J
Joker
OK, I used the follwoing procedure to create the combo box that is filtered
by another combo box. The problem I have now is that the value it sends
back to the main table are the RegionID and DivisionID when what I want is
the RegionName and DivisionName. What should I fix?
James
Steps to Reproduce the Behavior
In the following example, the first combo box lists the Region names from
the Region table, and the second combo box lists the Division names from the
Division table. When you select a Region name in the first combo box, the
second combo box is filtered to list only the Division names for that
Region.
1. Open the sample database Masterlist.mdb.
2. Create the following query based on the Region table, and then save
the query as qryRegionList.
Field: RegionID RegionName
Table: Region Region
Sort: Ascending
Show: <checked> <checked>
Criteria:
Or:
3. Create the following query based on the Division table, and then
save the query as qryDivisionList.
Field: DivisionID DivisionName RegionID
Table: Division Division Division
Sort: Ascending
Ascending
Show: <checked> <checked> <checked>
Criteria:
IIF(IsNull([Forms]![frmSSelector]![cboRegionSSelect]),[RegionID],[Forms]![frmSSelector]![cboRegionSSelect])
Or:
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 RegionIDs
within the Division table as criteria, then returns all Division in the
second combo box. If the control is not null, the RegionID that appears on
the form becomes the criteria for the query, and then returns the related
Division.
4. Create a new form in Design view that is not based on any table nor
any query.
5. From the View menu click Properties.
6. In the Property sheet, click the Event tab, and then click in the On
Current check box.
7. Click Build next to the check box, click Code Builder, and then
click OK.
8. Enter the following code:
Me!cboDivisionSelect.Requery
9. Add two combo boxes as follows and then save the form as
frmSSelector:
Combo Box 1:
Name: cboRegionSSelect
RowSourceType: Table/Query
RowSource: qryRegionList
ColumnCount: 2
ColumnWidths: 0";1"
BoundColumn: 1
Combo Box 2:
Name: cboDivisionSelect
RowSourceType: Table/Query
RowSource: qryDivisionList
ColumnCount: 3
ColumnWidths: 0";1";0"
BoundColumn: 1
10. Right-click the first combo box that is named cboRegionSSelect, and
then click Properties.
11. In the Property sheet, click the Event tab, and then click in the After
Update box.
12. Click the Build button next to the check box, click Code Builder, and
then click OK.
13. Enter the following code:
Me!cboDivisionSelect.Requery
Me!cboDivisionSelect.SetFocus
14. Open the form in Form view, and then sSelect a Region from the first
combo box. Note that the second combo box lists only the Division that are
related to the specific Region.NOTE: Each time that you sSelect a different
Region from the first combo box, the second combo box resets and then lists
the appropriate Division for the Region that you choose.
by another combo box. The problem I have now is that the value it sends
back to the main table are the RegionID and DivisionID when what I want is
the RegionName and DivisionName. What should I fix?
James
Steps to Reproduce the Behavior
In the following example, the first combo box lists the Region names from
the Region table, and the second combo box lists the Division names from the
Division table. When you select a Region name in the first combo box, the
second combo box is filtered to list only the Division names for that
Region.
1. Open the sample database Masterlist.mdb.
2. Create the following query based on the Region table, and then save
the query as qryRegionList.
Field: RegionID RegionName
Table: Region Region
Sort: Ascending
Show: <checked> <checked>
Criteria:
Or:
3. Create the following query based on the Division table, and then
save the query as qryDivisionList.
Field: DivisionID DivisionName RegionID
Table: Division Division Division
Sort: Ascending
Ascending
Show: <checked> <checked> <checked>
Criteria:
IIF(IsNull([Forms]![frmSSelector]![cboRegionSSelect]),[RegionID],[Forms]![frmSSelector]![cboRegionSSelect])
Or:
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 RegionIDs
within the Division table as criteria, then returns all Division in the
second combo box. If the control is not null, the RegionID that appears on
the form becomes the criteria for the query, and then returns the related
Division.
4. Create a new form in Design view that is not based on any table nor
any query.
5. From the View menu click Properties.
6. In the Property sheet, click the Event tab, and then click in the On
Current check box.
7. Click Build next to the check box, click Code Builder, and then
click OK.
8. Enter the following code:
Me!cboDivisionSelect.Requery
9. Add two combo boxes as follows and then save the form as
frmSSelector:
Combo Box 1:
Name: cboRegionSSelect
RowSourceType: Table/Query
RowSource: qryRegionList
ColumnCount: 2
ColumnWidths: 0";1"
BoundColumn: 1
Combo Box 2:
Name: cboDivisionSelect
RowSourceType: Table/Query
RowSource: qryDivisionList
ColumnCount: 3
ColumnWidths: 0";1";0"
BoundColumn: 1
10. Right-click the first combo box that is named cboRegionSSelect, and
then click Properties.
11. In the Property sheet, click the Event tab, and then click in the After
Update box.
12. Click the Build button next to the check box, click Code Builder, and
then click OK.
13. Enter the following code:
Me!cboDivisionSelect.Requery
Me!cboDivisionSelect.SetFocus
14. Open the form in Form view, and then sSelect a Region from the first
combo box. Note that the second combo box lists only the Division that are
related to the specific Region.NOTE: Each time that you sSelect a different
Region from the first combo box, the second combo box resets and then lists
the appropriate Division for the Region that you choose.