This content is outdated and is no longer being maintained. It is provided as a courtesy for individuals who are still using these technologies. This page may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist.
Applies to: 2007 Microsoft Office System, Microsoft Office Access 2007
Joel Krist, Akona Systems
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:
How to add an event procedure for the combo box AfterUpdate event.
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:
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
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
Load the form that contains the two combo boxes in design view. In the Navigation Pane, right-click the form's name and select the Design View menu item.
Display the properties for the Categories combo box. Right-click the Categories combo box on the form and select the Properties menu item.
On the Categories combo box Property Sheet, select the Event tab to display the combo box events.
Locate and click the After Update event and select the small down arrow that is displayed.
Click the [Event Procedure] item and then select the ellipses button next to the small down arrow. This causes Visual Basic to load and display the body of the cboCategories_AfterUpdate event procedure.
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.
' 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)
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:
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.
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.