Learning to Share

Peter Vogel

Sharing data between forms can be awkward in Access. In this article, Peter reviews all the techniques available for performing this task, including making use of Access 95 and 97's ability to add new properties to a form. By using forms as objects, you can even have two forms share a single recordset.

I don't like global variables -- I think they dramatically increase the costs of maintaining an application. After all, when you find a bad value in a global variable, that value could have been placed in the variable from any routine in the application. That's a lot of code to debug.

This narrow-minded attitude of mine can create difficulties when trying to share data between forms in a single application. There are a lot of solutions to this problem, but the only one I really like takes advantage of Access 95's ability to add new properties to a form object.

Imagine a situation where an application is displaying a customer record on a form called frmCustomer. On the form is a button that displays a form containing the customer's address (see Figure 1 for a simplified version of this problem). How do I pass the customer number from the main form to the address form so that the address form (frmAddress) can display the right shipping address?

Figure 1

The simplest way is to declare a public variable in a basic module like this:

Public gstrCustomerNumber As String

Now, in frmCustomer I can set it with the customer number before calling the address form:

Sub cmdShip_Click()
  gstrCustomerNumber = Me!txtCustomerNumber
  DoCmd.OpenForm "frmAddress"
End Sub 

Within frmAddress, I can use the global variable to filter the records displayed to ones that match the global variable. However, if the gstrCustomerNumber field is ever set to a bad value, the frmAddress form will display the wrong record (or no record at all). Since gstrCustomerNumber is available everywhere in my application, that bad value could come from anywhere in my application.

The second solution to the problem is to use a field on the frmCustomer form from within frmAddress. For instance, I could set the Filter property of the frmAddress form to limit the records to those records with a customer number that matches the one displayed on the frmCustomer form:

Me.Filter = "CustomerNumber = '" & _
 Form_frmCustomer!txtCustomerNumber & "'"
Me.FilterOn = True

While this eliminates the global variable, this method irretrievably ties frmAddress to frmCustomer. In fact, frmAddress is tied not just to frmCustomer but to the txtCustomerNumber text box on the form. Changing the name of the form or the text box will cause frmAddress to stop working. This tie to frmCustomer also prevents me from moving frmAddress to any other application.

The third solution is to use the OpenArgs property of the form to pass data to frmAddress. When calling the frmAddress field, I could pass the customer number to the form as the seventh argument in the OpenForm method:

  DoCmd.OpenForm "frmAddress",,,,,,Me!txtCustomerNumber

Within the frmAddress form, I can access that seventh parameter through the OpenArgs property of the frmAddress form. The code for that would look like this:

Me.Filter = "CustomerNumber = '" & _
 Me!OpenArgs & "'"
Me.FilterOn = True

With this method, I don't have a global variable, and the frmAddress form isn't tied to the frmCustomer form (or any field on it).

So what's the problem? I have to admit that the "OpenArgs property/seventh parameter of the OpenForm method" has always struck me as kind of a kludge. In addition, the OpenArgs property can only be set when the form is opened. Ideally, I'd want the user to be able to return to the frmCustomer form, select a new customer, and have the frmAddress form display the new data immediately.

In addition, the OpenArgs property will only let you pass a string value. While I can pass a customer number to the form, I can't pass an object variable. My frmCustomer form might well have retrieved all the data that the frmAddress form needs. If all I can pass to frmAddress is the customer number, then frmAddress will have to "re-retrieve" the ship-to address from the database. This is another access to the database, more network traffic, and just a bad thing to do.

There's also a more general problem: When two forms share data, you must reconcile the different copies of the data. Typically, this is handled by not duplicating any data on the forms. However, on the customer address form you'll probably want to display the customer name so that the user doesn't have to remember which customer the address is for. If you do that, then you'd probably lock the customer name fields on the address form to keep the user from changing them. After all, if the user changes the data on frmAddress and then returns to frmCustomer, frmCustomer might not reflect the changes made on frmAddress. From the user's point of view, this just seems narrow-minded. Why can't they change the customer name wherever they see it? I know that I really like the ability in Windows 95 to update the filename (or even delete the file) from any dialog box that lists files.

In this situation, what I really want to do is share a single recordset between frmCustomer and frmAddress. I also want to do this without tying the two forms together and without using a global variable. I want changes to automatically be reconciled between the two forms. Access 95 and 97's object-oriented tools let me do all of this.

The first step in this solution is to add a new property to the frmAddress form. I'll call this property CustomerNumber, and, whenever it's changed, frmAddress will display the address for the new customer. If I can get this first step to work, I'll come back and change the property to accept a recordset.

There are two ways to add a new property to a form. The first is to add a Public variable in the Declarations section of the form. Adding this line to frmAddress:

Public CustomerNumb As String

would let me use these lines in frmCustomer:

DoCmd.OpenForm "frmAddress"
frmAddress.CustomerNumb = Me!txtCustomerNumber

Those two lines in frmCustomer will set the public CustomerNumb variable in the frmAddress form. With CustomerNumb set, I can now use it in frmAddress to set the form's Filter property:

Me.Filter = "CustomerNumber = '" & _
 CustomerNumb & "'"
Me.FilterOn = True

You'll notice that the code hasn't changed much from when the OpenArgs property was used. The problem, in fact, isn't with the code but with picking the event that this code will be put in.

The problem is determining when the line in frmCustomer that sets the CustomerNumb property will be executed. Once the OpenForm line executes, the frmAddress form is opened and the events for that form fire. The order of events is Open>Load>Activate>Current. Only after the Current event has fired does the line in frmCustomer that sets the CustomerNumb property finally execute (see Figure 2). Of course, there are no longer any events left to fire in frmAddress to put the code to set the filter.

Figure 2

The workaround for this situation is to set the Timer property of frmAddress to some relatively small value. A good choice is 500, which translates into 500 milliseconds, or one-half a second. This will cause a Form Timer event to fire one-half a second after the form displays (see Figure 3). The code to set the filter can then be put in the Form_Timer event to execute after the form has displayed. If you use this technique, don't forget to set the TimerInterval property to zero in the Form_Timer event, or the code will execute every one-half second after the form opens:

Private Sub Form_Timer()
  Me.FilterOn = False
  Me.Filter = "CustomerNumber = '" & _
    CustomerNumber & "'"
  Me.FilterOn = True
  Form.TimerInterval = 0
End Sub

Figure 3

This routine can be rerun from the frmCustomer form by setting frmAddress's TimerInterval property to some number, causing the Timer event to fire again. I could now have frmAddress redisplay every time the customer changed in frmCustomer (something I couldn't do with the OpenArgs property).

However, this method still uses a public variable and doesn't pass the recordset that I wanted to use. In addition, while the Filter property can be reset whenever the data in frmCustomer changes, I need two lines of code to make it happen:

frmAddress.CustomerNumb = Me!txtCustomerNumber
frmAddress.TimerInterval = 2

What I really wanted was to have the form reset as soon as the CustomerNumb property was changed.

This design goal can be met if a Property Let procedure is used (see the sidebar, "Buying Property"). The Property Let routine in frmAddress for CustomerNumb would look like this:

Public Property Let CustomerNumb _
  (NewCstNumber As String)
Me.FilterOn = False
Me.Filter = "CustomerNumber = '" & _
    NewCstNumber & "'"
  Me.FilterOn = True
End Property 

Now, in order to keep the two forms in sync, all that needs to be done is to have the CustomerNumb property of the frmAddress form be set in the Current property of frmCustomer (I also added some code to make sure that I didn't try to update the form when it wasn't open):

If bolFormOpen("frmAddress") Then
  frm_frmAddress.CustomerNumber = Me!txtCustomerNumber
End If

Function bolFormOpen(strFormName As String) As Boolean
Dim strName As String
On Error Resume Next
strName = Forms(strFormName).Name
If Err = 0 Then
    bolFormOpen = True
Else
    bolFormOpen = False
End If
End Function

As a side benefit, since I only wrote a Property Let procedure, my CustomerNumb property was a read-only property. This meant there was one less thing that could wrong in my code.

By this point, I had met most of my design goals: I'd eliminated any global variables, and I had automatic synchronization between the forms. However, I wasn't passing the frmCustomer recordset. As a result, frmAddress was still retrieving data that already had been retrieved by the frmCustomer. I also knew that filtering can be a very inefficient way to retrieve information. If the customer table contained a lot of records, the display of the frmAddress data could slow the system down considerably. And I still hadn't addressed reconciling changes between the two forms.

It was now time to upgrade my property to handle recordsets. Altering the Property Get procedure to accept a recordset was relatively easy: Just change the datatype of the parameter passed to the Property Get. I set the ControlSource property for all the controls and the RecordSource property for frmAddress itself to blank so that the form was no longer bound to any data source. I also changed the name of the property to CustomerRecordset to reflect its new role. My new property routine now accepted a recordset and moved the fields from the recordset to the controls on the form:

Dim recHoldCust as Recordset
Public Property Let CustomerRecordset _
   (CustRecordset As Recordset)
  Me.txtCustomerCity = _
    CustRecordset("CustomerCity")
  'code to move other fields to form controls
  Set recHoldCust = CustRecordset
End Property   

In the last line of the procedure I saved the recordset into a local variable called recHoldCust, just in case some other routine in frmAddress might need to work with the recordset.

The frmCustomer form had to have its Current event updated to set the CustomerRecordset property of frmAddress to an appropriate recordset. The recordset to be passed would be the same recordset that the frmCustomer form was using, which I could retrieve through the frmCustomer's RecordsetClone property. Once I retrieved the recordset clone, I needed to have the clone's current record set to the same record that was current in the form. This is done by setting the clone's Bookmark property to the Bookmark property for the form. The code for the Current event then looked like this:

Private rec As Recordset
Private Sub Form_Current()
 Dim rec As Recordset

 Set rec = Me.RecordsetClone
 rec.Bookmark = Me.Bookmark
 frmAddress.CustomerRecordset = rec

With the addition of these three lines in the Current event of frmCustomer, and one line for each field in the CustomerRecordset Property procedure in frmAddress, I had eliminated the need for a global variable, guaranteed that the two forms would always be in sync, and shared a single recordset between two different forms. Only one problem remained: synchronizing updates.

It's entirely possible that the user could make changes in the frmAddress form. If that happened, I needed to write those changes back to the database. The code for that is relatively simple and takes advantage of the module-level variable that held the recordset passed to frmAddress:

recHoldCust.Edit
recHoldCust("CustomerCity") = Me!txtCustomerCity
'code for the other controls on the form
recHoldCust.Update

As with the problem of where to filter the form, the question was now where to put this update code. Since the form was no longer data bound, I couldn't use any of the data-driven events like BeforeInsert or BeforeUpdate. In the end, I put the code in the form's Deactivate event, which is fired when the user switches to another form.

Synchronizing changes between forms in this application isn't a problem anymore. Since both forms are sharing the same recordset, I can display the customer name on both frmCustomer and frmAddress -- and let the user update the name in both locations. Should the user then change the customer name on frmAddress, he or she will also see the change displayed in frmCustomer upon returning to it.

There's a cost to this solution, though: the code in the Property Get and the Deactivate event procedures that move the data from the controls to the fields and back again. One of the benefits of using Access is its automatic data binding, and my solution negates that, requiring me to write more code. There's also the danger that a new control will be added to frmAddress, and the appropriate lines of code won't be added to both the Property Get and Deactivate events. And, of course, frmAddress can't be a continuous form. Still, where frmAddress isn't a continuous form, I think that the benefits outweigh the costs.

The object-oriented features of Access 95 and 97 can solve a lot of long-standing problems. This is just one example of the kinds of opportunities that are available if you take the time to explore those features.

Like declaring a public variable, a Property Let procedure defines a property for the form -- but it doesn't require a public variable to do so. The main advantage of using a Property Let procedure is that the procedure's code is run each time the property's value is changed.

To create a read-only property called CustomerNumb, you'd create a routine that looks a lot like a subroutine that accepts one parameter:

Public Property Let CustomerNumb _
  (NewCstNumber As String)
'any code to process the data
End Property 

If you want your users to be able to read your property, you must create a Property Get procedure. If the Property Let procedure looks like a subroutine, a Property Get procedure looks like a function. To create a property called CustomerNumb that can be read, you'd create this routine:

Public Property Get CustomerNumb As String
CustomerNumb = value
End Property

Like a function, the Property Get must have a line that sets the name of the routine (which is also the name of the property) to the value that's to be returned to the user. If the property to be returned is an object, then you must create a Property Set rather than a Property Get.

If you want a property that can be both read and written, then you must write both the Let and Get (or Set) procedures. The watchout here is to make sure that you're consistent in typing your property. The data type of the parameter passed to the Let must be the same as the datatype of the Get routine.

Download sample code for this article here.

Peter Vogel is a principal in PH&V Information Systems (a technology management company). He is also the editor of Smart Access, a Microsoft Certified Solution Developer, and an instructor/author for Learning Tree International. Peter presents at conferences on Microsoft tools and is currently writing a book on Access for Sybex. peter.vogel@phvis.com.