ACC: How to Create Synchronized Combo Boxes
ID: Q98660
|
The information in this article applies to:
-
Microsoft Access versions 1.0, 1.1, 2.0, 7.0, 97
SUMMARY
Novice: Requires knowledge of the user interface on single-user computers.
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 : FmsCmbo
Version : 1.0 1.1 2.0 7.0 97
Platform : WINDOWS
Issue type : kbhowto
|