ACC2000: Using Code to Dynamically Synchronize Two Forms

• Microsoft Access 2000

SUMMARY

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. Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact a Microsoft Certified Solution Provider or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Solution Providers, please see the following page on the World Wide Web:

http://www.microsoft.com/mcsp/

http://www.microsoft.com/support/supportnet/overview/overview.asp

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 and perform these steps on a copy of the database.

NOTE: The sample code in this article uses Microsoft Data Access Objects. For this code to run properly, you need to reference the Microsoft DAO 3.6 Object Library.

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 OnCurrent property of the form to the following event procedure:




Private 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 frm As Form, rs As DAO.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 frm = Forms(formname)
   Set rs = frm.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
      frm.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 OnCurrent property event procedure of the subform 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 the SysCmd() function, click Microsoft Access Help on the Help menu, type SysCmd Function in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

For additional information about the BuildCriteria function, please see the following article in the Microsoft Knowledge Base:

Q197586 BuildCriteria Method Gives Error w/ Certain Expressions

Q208970 Run-time error '2448' with BuildCriteria Method

Additional query words:

Keywords : kbusage kbdta AccCon FmsHowto
Version : WINDOWS:2000
Platform : WINDOWS
Issue type : kbinfo