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:
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.- 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.
- In the cboCategorySelect box, select a different category. Note that the cboProductSelect box is reset and lists the appropriate products for the new category.
REFERENCES
For more information, search the Help Index for "synchronizing combo boxes," or ask the Office 97 Assistant
For more information, search the Help Index for "Requery Action," or ask the Office 97 Assistant.
Additional query words: combobox parameter listbox change
Keywords: kbfaq kbhowto KB98660