ACC: Using Code to Dynamically Synchronize Two Forms (95/97)

MORE INFORMATION

CAUTION: Following the steps in this example will modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file, or perform these steps on a copy of the database.

The following example demonstrates how to synchronize the Products form to the current record in the Product List subform on the Categories form.

1. Open the sample database Northwind.mdb.
2. Open the Product List form in Design view.
3. Set the form's OnCurrent property to the following event procedure:

         '**********************************************************
         'Sub Form_Current()
         '**********************************************************
   
            'If the ProductName is blank, then exit the Sub.
            If IsNull(Me![ProductName]) Then
               Exit Sub
            End If
   
            'Dimension variables.
            Dim formname As String, SyncCriteria As String
            Dim f As Form, rs As Recordset
   
            'Set the formname to "Products," the form that will be
            'synchronized.
            formname = "Products"
   
            'Check to see if the Products form is open. If it
            'is not open, open it.
            If Not SysCmd(acSysCmdGetObjectState, acForm, formname) Then
                DoCmd.OpenForm formname
            End If
   
            'Define the form object and Recordset object for
            'the Products form.
            Set f = Forms(formname)
            Set rs = f.RecordsetClone
   
            'Define the criteria used for the synchronization.
            SyncCriteria = BuildCriteria("ProductName", dbText, _
               Me!ProductName)
   
            'Synchronize the corresponding record in the Products form to
            'the current record in the subform.
            rs.FindFirst SyncCriteria
   
            'If a record exists in the Products form, find the
            'matching record.
            If rs.nomatch Then
               MsgBox "No match exists!", 64, formname
            Else
               f.bookmark = rs.bookmark
            End If
   
         '**********************************************************
         'End Sub
         '**********************************************************

4. Save and then close the Product List form.
5. Open the Categories form in Form view. When you open the Categories form, the subform's OnCurrent event procedure is triggered. This causes the Products form to open, if it is not already open, and synchronizes that form to the current record on the Categories subform.

You can adapt this method to occur when a command button is clicked by moving the code specified in the subform's OnCurrent property event procedure to a command button on the Categories form. If you do move the code to a command button, make sure to change the references to "Me" in the code to a full form reference, using the following syntax:

   Forms!Categories![Product List].Form