Microsoft KB Archive/209576

= ACC2000: How to Create Synchronized Combo Boxes =

Article ID: 209576

Article Last Modified on 7/16/2004

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q209576



Novice: Requires knowledge of the user interface on single-user computers.

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



SUMMARY
This article shows you how to create a combo box that is filtered to list only those items that are related to an item that you selected in a previous combo box.



Steps to Reproduce the Behavior
In the following example, the first combo box lists the category names from the Categories table, and the second combo box lists the product names from the Products table. When you select a category name in the first combo box, the second combo box is filtered to list only the product names for that category.  Open the sample database Northwind.mdb. Create the following query based on the Categories table, and then save the query as qryCategoriesList.

 Create the following query based on the Products table, and then save the query as qryProductsList.

NOTE: The IIF function within the criteria tests to see if the first combo box is null. If the combo box is null, the query uses all the CategoryIDs within the Products table as criteria, then returns all Products in the second combo box. If the control is not null, the CategoryID that appears on the form becomes the criteria for the query, and then returns the related Products. Create a new form in Design view that is not based on any table nor any query. From the View menu click Properties. In the Property sheet, click the Event tab, and then click in the On Current check box.</li> Click Build next to the check box, click Code Builder, and then click OK.</li>  Enter the following code: Me!cboProductSelect.Requery </li>  Add two combo boxes as follows and then save the form as frmSelector: <pre class="fixed_text">  Combo Box 1: ---  Name: cboCategorySelect RowSourceType: Table/Query RowSource: qryCategoriesList ColumnCount: 2 ColumnWidths: 0";1" BoundColumn: 1 Combo Box 2: ---  Name: cboProductSelect RowSourceType: Table/Query RowSource: qryProductsList ColumnCount: 3 ColumnWidths: 0";1";0"  BoundColumn: 1                    </li> Right-click the first combo box that is named cboCategorySelect, and then click Properties.</li> In the Property sheet, click the Event tab, and then click in the After Update box.</li> Click the Build button next to the check box, click Code Builder, and then click OK.</li>  Enter the following code:  Me!cboProductSelect.Requery Me!cboProductSelect.SetFocus                    </li> Open the form in Form view, and then select a category from the first combo box. Note that the second combo box lists only the products that are related to the specific category.NOTE: Each time that you select a different category from the first combo box, the second combo box resets and then lists the appropriate products for the category that you choose.</li></ol>

<div class="references_section">