ACC2: Using Code to Dynamically Synchronize Two Forms (119398)

SUMMARY

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

This article describes how to synchronize a form to the current record in a subform using Access Basic. The method described in this article searches a recordset returned by the form's RecordsetClone property and synchronizes the form's bookmarks with the Recordset.

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

MORE INFORMATION

The following example demonstrates how to synchronize the Products form to the current record in the Categories Subform form in the sample database NWIND.MDB.

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

Open the NWIND database.
Open the Categories Subform form in Design view.

Set the form's OnCurrent property to the following event procedure:

      '**********************************************************
      'Sub Form_Current()
      '**********************************************************

          'If the Product ID is blank, then exit the Sub.
          If IsNull(Me![Product Id]) 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(SYSCMD_GETOBJECTSTATE, A_FORM, 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 = "[Product Id]=" & Me![Product Id]

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

Save and then close the Categories Subform form.
Open the Categories form in Form view. When you open the Categories form, the subform's OnCurrent event procedure will be triggered, causing the Products form to be opened if it is not already open.

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 full form reference, using the syntax:

   Forms!Categories![Categories Subform].Form

REFERENCES

For an example of how to dynamically synchronize two forms in Microsoft Access for Windows 95 version 7.0, please see the following article in the Microsoft Knowledge Base:

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

Microsoft Access "User's Guide," version 2.0, Chapter 25, "Using the OnCurrent Property to Keep Two Open Forms Synchronized," pages 639-641

Microsoft Access "Building Applications," version 2.0, Chapter 4, "Using Forms to Collect, Display, and Filter Information," page 84, and Chapter 11, "Working with Sets of Records," pages 255-259

For more information about the SysCmd() function, search for "SysCmd," and then "SysCmd Function" using the Microsoft Access Help menu.

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