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

ID: Q149940

The information in this article applies to:

• Microsoft Access versions 7.0, 97

SUMMARY

Advanced: Requires expert coding, interoperability, and multiuser skills.

This article shows you how to use Visual Basic for Applications to synchronize a form to the current record on the subform of another form. The method described in this article synchronizes the form's bookmark with the bookmark of the form's recordset after searching the recordset for the current key value from the other form's subform.

This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to your version of the "Building Applications with Microsoft Access" manual.

NOTE: This article explains a technique demonstrated in the sample files, FrmSampl.exe (for Microsoft Access for Windows 95 version 7.0) and FrmSmp97.exe (for Microsoft Access 97). For information about how to obtain these sample files, please see the following articles in the Microsoft Knowledge Base:

   ARTICLE-ID: Q150895
   TITLE     : ACC95: Microsoft Access Sample Forms Available on MSL

   ARTICLE-ID: Q175066
   TITLE     : ACC97: Microsoft Access 97 Sample Forms Available on MSL

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
      '**********************************************************

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.

   Forms!Categories![Product List].Form

REFERENCES

For more information about synchronizing forms with Microsoft Access 2.0, please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q119398
   TITLE     : ACC2: Using Code to Dynamically Synchronize Two Forms 

For more information about the Me property, search for "Me Property" using the Microsoft Access 97 Help Index.

Additional query words:

Keywords          : kbusage FmsHowto 
Version           : 7.0 97
Platform          : WINDOWS
Hardware          : x86
Issue type        : kbhowto

Last Reviewed: November 21, 1998