Synchronizing Combo Boxes on Forms in Access 2007

When you build custom Microsoft Office Access 2007 applications, there are often situations where it is useful to synchronize two combination boxes (combo boxes) on an Access 2007 form so that when you select an item in the first combo box, that selection limits the choices in the second combo box. For example, it might be necessary to limit the products that are displayed in a combo box based on the category that is selected in another combo box. The Access 2007 combo box control generates the AfterUpdate event when an item in the combo box is selected. This article illustrates how to use the combo box AfterUpdate event to synchronize two combo boxes on an Access 2007 form.

This section walks through two steps to illustrate how to synchronize two combo boxes on an Access 2007 form:

1. How to add an event procedure for the combo box AfterUpdate event.

2. How to add code to the AfterUpdate event procedure that updates the contents of a combo box based on the item selected in another combo box.

To illustrate the steps required to synchronize two combo boxes, the code provided in this article works with a sample database that has the following characteristics:

1. An Access 2007 database exists which contains two tables, tblCategories and tblProducts. The Category field in tblProducts is a lookup column that is linked to the Category field in the tblCategories table.

   Figure 1. Categories table

   

   Figure 2. Products table

   

2. The database contains a form which contains two combo boxes: a Categories combo box named cboCategories and a Products combo box named cboProducts. The cboCategories combo box has its Row Source Type property set to Table/Query and its Row Source property set to the following SQL statement:

   SELECT [tblCategories].ID, [tblCategories].Category FROM tblCategories ORDER BY [Category];

Add an Event Procedure for the Combo Box AfterUpdate Event

To add an AfterUpdate event procedure for the Categories combo box

Add Code to the AfterUpdate Event Procedure

Modify the cboCategories_AfterUpdate event procedure created previously so that it sets the RowSource property of the Products combo box based on the category that is selected in the Categories combo box. This causes the Products combo box to display only those products whose category matches the category that was selected in the Categories combo box.

Private Sub cboCategories_AfterUpdate()
    ' Update the row source of the cboProducts combo box
    ' when the user makes a selection in the cboCategories
    ' combo box.
    Me.cboProducts.RowSource = "SELECT ProductName FROM" & _
                               " tblProducts WHERE Category = " & _
                               Me.cboCategories & _
                               " ORDER BY ProductName"
                            
    Me.cboProducts = Me.cboProducts.ItemData(0)
End Sub

When you build custom Access 2007 solutions, it is often useful to synchronize two combo boxes on an Access 2007 form. This ensures that when an item is selected in the first combo box, that selection limits the choices in the second combo box. This article explores how to synchronize two combo boxes on an Access 2007 form. The key steps include:

1. How to add an event procedure for the combo box AfterUpdate event. This step causes Visual Basic to load and display the body of the AfterUpdate event procedure.

2. After the body of the AfterUpdate event procedure is created, the next step is to add the code that executes as part of this event procedure. This code, as indicated above, uses a SELECT statement to scope the second combo box list based on the first combo box selection.

Watch the Video

Video Length: 00:04:46

File Size: 3.37 MB

File Type: WMV

• Access Developer Portal

• Welcome to the Microsoft Office Access 2007 Developer Reference

• ComboBox.AfterUpdate Event documentation on MSDN

• Office Developer Center