Export (0) Print
Expand All
22 out of 68 rated this helpful - Rate this topic

Synchronizing Combo Boxes on Forms in Access 2007

Office 2007
Office Visual How To

Applies to: 2007 Microsoft Office System, Microsoft Office Access 2007

Joel Krist, Akona Systems

April 2007

Overview

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.

Code It

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

    Synchronizing Combo Boxes categories table

    Figure 2. Products table

    Synchronizing Combo Boxes 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

  1. 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.

  2. Display the properties for the Categories combo box. Right-click the Categories combo box on the form and select the Properties menu item.

  3. On the Categories combo box Property Sheet, select the Event tab to display the combo box events.

  4. Locate and click the After Update event and select the small down arrow that is displayed.

  5. 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.

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
Read It

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.

See It

Synchronizing Combo Boxes on Forms video

Watch the Video

Video Length: 00:04:46

File Size: 3.37 MB

File Type: WMV

Explore It
Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.