Microsoft KB Archive/210060

= ACC2000: Using Code to Dynamically Synchronize Two Forms =

Article ID: 210060

Article Last Modified on 10/11/2006

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q210060



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

This article applies only to a Microsoft Access database (.mdb).



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.

NOTE: This article explains a technique demonstrated in the sample file, FrmSmp00.mdb. For information about how to obtain this sample file, please see the following article in the Microsoft Knowledge Base:

2333245 ACC2000: Microsoft Access Sample Forms Database Available in Download Center

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers 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 requirements.



MORE INFORMATION
CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow 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 must reference the Microsoft DAO 3.6 Object Library. To do so, click References on the Tools menu in the Visual Basic Editor, and make sure that the Microsoft DAO 3.6 Object Library check box is selected.

The following example demonstrates how to synchronize the Products form to the current record in the Product List subform on the Categories form.  Open the sample database Northwind.mdb. Open the Product List form in Design view.  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  Save and then close the Product List form. 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.

You can adapt this method to occur when a command button is clicked by moving the code specified in the OnCurrent property event procedure of the subform 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

<div class="references_section">