Microsoft HomeProductsSearchSupportShopWrite Us Microsoft Home
Microsoft Office Developer Forum banner art
June 12, 1998     Back to Microsoft Access Tips  

Synchronize Forms by Using the wherecondition Argument of the OpenForm Method

Charlie Kindschi

If you’re setting up your custom Microsoft® Access application such that the user will be able to open one form from another, related form it is necessary to synchronize the data in both forms. For example, imagine that you add a new command button to the Customers form and program this button to open the Orders form. The Orders form must automatically display data for the customer currently selected on the Customers form. As the user moves from one record to the next on the Customers form, the data on the Orders form must change accordingly.

Use the code examples provided in this tip to synchronize the data displayed on the Customers and Orders forms, just as if the latter were a subform of the former.

Note: This technique doesn’t work both ways; one form must drive the other.

Create a command button to open one form from another

  1. In Access, open the Northwind sample database, Northwind.mdb. The default location for this database on your computer is C:\Program Files\Microsoft Office\Office\Samples.
  2. On the Forms tab in the Northwind database window, double-click Customers.
  3. Switch to Design view, and then add a command button to the Customers form.
  4. Set the command button’s Name property to cmdOrders, and set its Caption property to Orders.
  5. Adjust the size and positioning of the command button so that your form resembles the illustration at the end of this tip.

Add event procedures to synchronize the data on the two forms

  1. In Design view, click Code on the View menu.
  2. In the class module for the Customers form, insert the Click event procedure for cmdOrders (using the Object and Procedure boxes), and then paste in the following code:
 

 

Private Sub cmdOrders_Click()

    Dim strWhereCond

    strWhereCond = "CustomerID = Forms!Customers!CustomerID"

    DoCmd.OpenForm "Orders", wherecondition:=strWhereCond

End Sub 


 
  1. Insert the Current event procedure, and then paste in the following code:
 

 

Private Sub Form_Current()

    Dim strWhereCond

    strWhereCond = "CustomerID = Forms!Customers!CustomerID"

    If IsLoaded("Orders") Then

        DoCmd.OpenForm "Orders", wherecondition:=strWhereCond

    End If

End Sub 


 
  1. Switch to Form view, and then try out the new command button and event procedures. Open the Orders form from the Customers form, and then look at various customer records by using the navigation buttons at the bottom of the Customers form.

    Notice how the two forms stay in sync with one another.

The wherecondition argument of the OpenForm method (for the DoCmd object) can be any valid SQL Where clause without the word "Where". You use wherecondition in the Click and Current event procedures to synchronize the Customer ID field of the query "OrderQry" (which generates the data displayed on the Orders form) with the Customer ID field on the Customers form.

The Current event of the Customers form occurs whenever this form is refreshed or requeried. You use the IsLoaded function to detect the presence of the Orders form. If the form is found, the wherecondition argument is used as explained earlier.

Synchronized data on the Customers and Orders forms

Synchronized data on the Customers and Orders forms

 

 
 
  © 1998 Microsoft Corporation. All rights reserved. Legal Notices.
Last Updated: March 3, 1998

 
  Best experienced with
Microsoft Internet Explorer
Click here to start.