Microsoft KB Archive/98660

= ACC: How to Create Synchronized Combo Boxes =

Article ID: 98660

Article Last Modified on 1/18/2007

-

APPLIES TO


 * Microsoft Access 1.0 Standard Edition
 * Microsoft Access 1.1 Standard Edition
 * Microsoft Access 2.0 Standard Edition
 * Microsoft Access 95 Standard Edition
 * Microsoft Access 97 Standard Edition

-



This article was previously published under Q98660



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



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



MORE INFORMATION
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 (NWIND.MDB in version 2.0 and 1.x)  Create the following new query based on the Categories table, and then save it as qryCategoriesList:

     Query: qryCategoriesList ---     Field name: CategoryID (Type a space in Category ID in 2.0 and 1.x)         Show: Yes Field name: CategoryName (Type a space in Category Name in 2.0 and                               1.x)         Show: Yes Sort: Ascending   Create the following new query based on the Products table, and then save it as qryProductsList:

NOTE: In the following sample query, an underscore (_) is used at the end of a line as a line-continuation character. Remove the underscore from the end of the line when re-creating this query.

  In Microsoft Access 7.0 and 97:

Query: qryProductsList ---     Field name: ProductID Show: Yes Field name: ProductName Show: Yes Sort: Ascending Field name: CategoryID Show: Yes Sort: Ascending Criteria: IIF(IsNull([Forms]![frmSelector] _ ![cboCategorySelect]),[CategoryID],[Forms] _         ![frmSelector]![cboCategorySelect])

In Microsoft Access 1.x and 2.0:

Query: qryProductsList ---     Field name: Product ID         Show: Yes Field name: Product Name Show: Yes Sort: Ascending Field name: Category ID        Show: Yes Sort: Ascending Criteria: IIF(IsNull([Forms]![frmSelector] _ ![cboCategorySelect]),[Category ID],[Forms] _         ![frmSelector]![cboCategorySelect])

Note that using the IIf and IsNull functions in this query enables you to see a list of all the products if no category is selected.   Create the following new macro, and then save it as ResetProductSelect:

     Macro Name                 Action --     ResetProductSelect         Requery GoToControl

ResetProductSelect Actions -     Requery Control Name: cboProductSelect GoToControl: Control Name: cboProductSelect   Create the following new form with the combo boxes shown, and then save the form as frmSelector:

<pre class="fixed_text">     Form: frmSelector ---     Combo Box: Name: cboCategorySelect RowSourceType: Table/Query RowSource: qryCategoriesList ColumnCount: 2 ColumnWidths: 0 in;1 in        BoundColumn: 1 After Update: ResetProductSelect Combo Box: Name: cboProductSelect RowSourceType: Table/Query RowSource: qryProductsList ColumnCount: 3 ColumnWidths: 0 in;1 in; 0 in        BoundColumn: 1

NOTE: The Name property is the ControlName property in Microsoft Access version 1.x. </li> View the form in Form view. In the cboCategorySelect box, select a category and note that the cboProductSelect box lists only the products for that category.</li> In the cboCategorySelect box, select a different category. Note that the cboProductSelect box is reset and lists the appropriate products for the new category.</li></ol>

<div class="references_section">