Microsoft KB Archive/97624

= ACC: How to Synchronize Two Combo Boxes on a Form =

Article ID: 97624

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 Q97624



Moderate: Requires basic macro, coding, and interoperability skills.



SUMMARY
This article shows you how to synchronize two combo boxes so that when you make a selection in the first combo box, the selection limits the choices in the second combo box.

NOTE: This article explains a technique demonstrated in the sample files, FrmSampl.exe (for Microsoft Access for Windows 95 version 7.0) and FrmSmp97.exe (for Microsoft Access 97). For information about how to obtain these sample files, please see the following articles in the Microsoft Knowledge Base:

150895 ACC95: Microsoft Access Sample Forms Available in Download Center

175066 ACC97: Microsoft Access 97 Sample Forms Available in Download Center



MORE INFORMATION
The following example uses the sample database Northwind.mdb (NWIND.MDB in earlier versions). The first combo box lists the available product categories, and the second combo box lists the available products for the category selected in the first combo box:

 Open the sample database Northwind.mdb (or NWIND.MDB in 1.x and 2.0).  Create the following new query based on the Products table, and then save the query as Category Combo Query:

     Query: Category Combo Query -     Table: Products Type: Select Query Field: ProductID (or Product ID in 1.x and 2.0) Sort: Ascending Show: Yes Field: ProductName (or Product Name in 1.x and 2.0) Table: Products Show: Yes Field: CategoryID (or Category ID in 1.x and 2.0) Show: No        Criteria: Forms![frmCategoriesProducts]![Categories]   Create a new form not based on any table or query with the following combo boxes, and save the form as frmCategoriesProducts.

     Combo Box 1 ---     Name:          Categories RowSourceType: Table/Query RowSource:    Categories ColumnCount:  2 ColumnWidths: 0;1 BoundColumn:  1 AfterUpdate: Refresh Products

NOTE: The Name property is called the ControlName property in     Microsoft Access 1.x.

Combo Box 2 ---     Name:          Products RowSourceType: Table/Query RowSource:    Category Combo Query ColumnCount:  2 ColumnWidth:  0;1 BoundColumn:  1

NOTE: The BoundColumn property of the first combo box should not be set to the field named in the Criteria row of the above query; otherwise, the second combo box displays only the first record.   Create the following new macro and save it as Refresh Products: <pre class="fixed_text">     Macro Name          Actions ---     Refresh Products    Requery

Action Arguments Control Name: Products </li> View the frmCategoriesProducts form in Form view. When you select a category in the first combo box, the second combo box is updated to list only the available products for the selected category.</li></ol>