|
Tips And Tricks
I seem to run into things from time to time that I want to share with the world,
usually things that I had to find out the hard way. Sometimes it will just be
something that I had to dig for, and want to make more readily accessible. This
page is intended to make life a little easier for other programmers. If it meets
your need, then that's what it's here for.
Call A Form In Another MDB Through A Reference Using MS Access
Ok, you've started moving code from places where it was cloned into
a central library, usually code which will be called from several
different places. You've set up a reference to the library, and
you have the ability to call functions in it. Next, you try moving
a form into the new library, but the Access doesn't seem to know
where the form is . . . How do we get Access to bring up a form
which resides in a central library?
We first tackled this in ADPs, where the problem was actually
simpler. Only recently did I try it from an MDB type of central
library. In ADPs, you set up a connection to a SQL Server style
database. In any ADP which has had the connection set, all tables
and queries in the SQL Server backend are available. So, we connect
both our satellite module and our central library to the same back
end database, and we don't have a problem with forms getting to
the data.
In a central library of the MDB type, it gets a bit more complicated,
in that you would either have to have links pointing to the back end
database (MDB) or else you would have to have some dynamic way of
pointing the form to the data. If you only have one project, with
one back end database, this might not seem like much of an issue.
However, I had several projects which had both front ends and back
ends for each one. In my MDB central library, I elected not to have
table links of any kind, and to have my forms connect to the data at
run time, rather than trying to manage links which would have to be
set up to point to either development data or production data. There
are probably several ways to do this, among them the use of disconnected
recordsets, and dynamic connections made "on the fly" at run time
using ADO. At the time of this writing, at least, I chose the latter
method.
We had used something like this in our ADPs to connect to a local
MDB on the user's local computer. To base a form in an ADP on a
recordset in an MDB, you can establish an ADO recordset at
run time using a connection string. This is done using code in the
form's OnOpen event. For example:
'use a local temp file in an MDB from an ADP to allow each user to have unique local data
Private Sub Form_Open(Cancel As Integer)
On Error GoTo Err_Form_Open
Dim ConnStr As String
Dim MDBConn As New ADODB.Connection
Dim rstmyform As New ADODB.Recordset
ConnStr = MDBConnStr(LocPAFile)
MDBConn.Open (ConnStr)
rstmyform.Open ("MyFormTempFile"), MDBConn, adOpenKeyset, adLockOptimistic
If Not rstmyform.EOF Then
Set Me.Recordset = rstmyform
Else
MsgBox "There is no data in the temp file"
End If
Exit_Form_Open:
Exit Sub
Err_Form_Open:
MsgBox "Error #" & Err.Number & ": " & Err.Description
Resume Exit_Form_Open
End Sub
Function MDBConnStr(pMDB) As String
On Error GoTo Err_MDBConnStr
Dim strConnection As String
'Build a connection string for an MS Access database.
strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" & pMDB & "'"
MDBConnStr = strConnection
Exit_MDBConnStr:
Exit Function
Err_MDBConnStr:
MsgBox "Error #" & Err.Number & ": " & Err.Description
Resume Exit_MDBConnStr
End Function
Once you know how to do this, it's not too terribly different to open
up a recordset in any MDB from a central library using ADO. You don't
need the elaborate Jet connection string. The code to open a table in
the MDB your code is running in at run time ends up looking something
like this:
Private Sub Form_Open(Cancel As Integer)
On Error GoTo Err_Form_Open
Dim con As Object
Dim rstUsr As New ADODB.Recordset
'run as normal -set up the data for the form
Set con = Application.CurrentProject.Connection
rstUsr.Open ("Users"), con, adOpenKeyset, adLockOptimistic
Set Me.Recordset = rstUsr
Exit_Form_Open:
Exit Sub
Err_Form_Open:
MsgBox "Error #" & Err.Number & ": " & Err.Description
Resume Exit_Form_Open
End Sub
That code establishes the recordset dynamically, at run time, for a
login form that checks to see if you are in the list of valid users.
With an ADO recordset pointing to the "Users" table, the login form,
which runs in the central library, is able to validate users
attempting to use the program.
Having called this form through a reference, then, it still has
access to the records in the Users table, even though there are no
table links or tables in the central library. And that brings us
back to the original question of how to call the form in the first
place.
Because the form is now off in another MDB, any buttons or toolbar
menu options which try to call the form the usual way won't work.
The form is not located in the current MDB. What we have to do is
run code which DOES reside in the same MDB where the form is
located. This can be accomplished by calling a function through
the reference. The function is in the same MDB (or ADP) where the
form is, and therefore has the form in its scope, so that it can
run it.
Originally, when my associate first found a way to do this, we had a
module with over a hundred functions with the form name "hard coded"
twice in each one. Each function would each bring up a particular
form. Some of them had the form name hard coded three times, with
an extra reference to allow a where clause to be passed in. Well,
that got old in a hurry, because an excess of cloned code can lead
to problems. Since this module consisted of so many smaller
functions, each of which opened a specific form, I came along and
wrote a function which allows us to pass in a parameter for the
form name. See the following:
Function GenericFormOpen(pFormName, Optional pWhereClause, Optional pViewMode)
On Error GoTo Err_GenericFormOpen
If IsMissing(pViewMode) Then
pViewMode = 0
End If
If IsMissing(pWhereClause) Then
pWhereClause = ""
End If
If Len(pWhereClause) = 0 Then
DoCmd.OpenForm pFormName, pViewMode
Else
DoCmd.OpenForm pFormName, pViewMode, , pWhereClause
End If
Exit_GenericFormOpen:
Exit Function
Err_GenericFormOpen:
If Err.Number = 438 Then Resume Next
If Err.Number = 2501 Then Resume Next
MsgBox "Error #" & Err.Number & ": " & Err.Description
Resume Exit_GenericFormOpen
End Function
This allows us to call up almost any form in the central library,
which is a separate MDB or ADP. We can even call up a form for
a specific record or set of records using the optional where clause.
A call to this function can be placed in the code for a click event
of a button, or can even be placed in a toolbar menu option using
the equal sign calling convention. For example the following toolbar
contains =GenericFormOpen("Department"). To bring this up this
dialogue, you right-click on a menubar or toolbar and choose
customize. For further details, see the following:
Right click again and choose properties:
These pictures illustrate how a form which is located in a central
library can be brought up by a function which is located in a central
library, from a toolbar in an application. Neither the form or the
function are found in the application, and the only way that the
application can get to these is through a reference which has been
established between the subsystem and the library (normally, prior
to run time).
If you found this information to be useful,
and would like to donate via PayPal to help offset the cost of
maintaining this web site, use the following button:
|
|
|
|
