Restrict Combo Box Values based on another Combo Box

  • Thread starter Thread starter AimeeK via AccessMonster.com
  • Start date Start date
A

AimeeK via AccessMonster.com

Hello, Sorry if this has been answered before, but I'm trying to set up a
combo box in which I'd like the values to be based upon a choice from the
previous combo box. Here's an example:

In the first combo box, I have various master categories: Dry Groceries,
Oils and Shortenings, Center of the Plate, etc. In the second combo box, I
have categories that fall under the master categories. What I want to happen
is that when a user selects "Dry Groceries" as a master category, in the next
combo box, I only want the user to be able to choose from only categories
that fall under "Dry Groceries", instead of showing all the available
categories.

The first combo box is based off of a table called "tblMasterCategories", and
right now, the second combo box is based off of a query called
"qryCategories". Something's just not clicking here, can anyone help? I'm
thinking it has to do with filtering or something.

Thanks in advance.
 
AimeeK said:
See this article:
http://www.mvps.org/access/forms/frm0028.htm
Click to expand...
[quoted text clipped - 3 lines]
Ken, Thank you for your reply, but that article just confused the heck of me..
.I need the explanation for dummies on how to do this...thanks.
Click to expand...

An addendum to my previous message...this is the structure of the tables
(just a few examples):

tblMasterCategories (bound to Combo Box named cbxCombo1)
Master Category Code * Master Cat Description
005 Dry Groceries
010 Oils and Shortenings
015 Frozen Foods
020 Frozen Potatoes
*This is the primary key in this table


tblCategories (bound to Combo Box named cbxCombo2)
Master Category Code* Category Code ** Category Description
005 010
Desserts
005 015
Pudding, Gelatin
015 060
Frozen Prepared
015 065
Entrees
015 070
Other Frozen Foods
*This is the foreign key for this table
**This is the primary key for this table

In other words, if the user selects Master Category Code 005 from the
cbxCombo1 combo box, then in cbxCombo2, I only want Desserts and Pudding,
Gelatin to appear, since they are both tied to Master Category Code 005.

Hope this helps...thanks again!
 
Not sure what is the source of the confusion from that article, so I'll show
you an example based on these tables.

You'll want to set the Row Source property for cbxCombo1 to this SQL
statement:

SELECT [Master Category Code], [Master Cat Description]
FROM tblMasterCategories
ORDER BY [Master Cat Description];


You'll want to set the Row Source property for cbxCombo2 to this SQL
statement:

SELECT [Category Code], [Category Description]
FROM tblCategories
WHERE [Master Category Code] = [cbxCombo1]
ORDER BY [Category Description];


You'll want to put this code in the AfterUpdate event procedure for
cbxCombo1:

Private Sub cbxCombo1_AfterUpdate()
Me.cbxCombo2.Requery
End Sub

--

Ken Snell
<MS ACCESS MVP>



AimeeK via AccessMonster.com said:
AimeeK said:
See this article:
http://www.mvps.org/access/forms/frm0028.htm
Click to expand...
[quoted text clipped - 3 lines]
Thanks in advance.
Click to expand...

Ken, Thank you for your reply, but that article just confused the heck of
me..
.I need the explanation for dummies on how to do this...thanks.
Click to expand...

An addendum to my previous message...this is the structure of the tables
(just a few examples):

tblMasterCategories (bound to Combo Box named cbxCombo1)
Master Category Code * Master Cat Description
005 Dry Groceries
010 Oils and Shortenings
015 Frozen Foods
020 Frozen Potatoes
*This is the primary key in this table


tblCategories (bound to Combo Box named cbxCombo2)
Master Category Code* Category Code ** Category
Description
005 010
Desserts
005 015
Pudding, Gelatin
015 060
Frozen Prepared
015 065
Entrees
015 070
Other Frozen Foods
*This is the foreign key for this table
**This is the primary key for this table

In other words, if the user selects Master Category Code 005 from the
cbxCombo1 combo box, then in cbxCombo2, I only want Desserts and Pudding,
Gelatin to appear, since they are both tied to Master Category Code 005.

Hope this helps...thanks again!
Click to expand...
 
Back
Top