Multiple combo boxes that filters records in a form

[Guest]

Could anyone please help me with the following?

How to:
1) create 2 combo boxes, each dispalying unique values from a field of table/query containng multiple occurences of each values?
2) then sychronize these 2 combo boxes and filter records in a form based on my selection in the combo boxes?

Example:
tblGrocery (containing fields of Category, Type, ProductName)
cboCategory
cboType (depend on the selection of cboCategory)
frmGrocery (based on tblGrocery)

After selecting Fruit in cboCategory, then Apple in cboType, records under Apple (red apple, green apple,...) are filtered in a form.

Your kind help is much appreciated from a very frustrated man!

 

[Allen Browne]

See:
Limit content of combo/list boxes
a:
http://www.mvps.org/access/forms/frm0028.htm

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Could anyone please help me with the following?

How to:
1) create 2 combo boxes, each dispalying unique values from a field of

table/query containng multiple occurences of each values?

2) then sychronize these 2 combo boxes and filter records in a form based

on my selection in the combo boxes?

Example:
tblGrocery (containing fields of Category, Type, ProductName)
cboCategory
cboType (depend on the selection of cboCategory)
frmGrocery (based on tblGrocery)

After selecting Fruit in cboCategory, then Apple in cboType, records under

Apple (red apple, green apple,...) are filtered in a form.

 

[Guest]

Thanks alot, Allen.
Do you know how I can do about the first problem ( i.e. How to create a combo box that displays unique values from a field of table/query containng multiple occurences of each values?)

for example
In tblGrocery,
contains 3 fields: Category-Type-Product
1st record: Fruit-Apple-GreenApple
2nd record: Fruit-Apple-RedApple
3rd record: Meat-Beef-Tbone
4th record: Vegi-Lettuce-FancyLettuce

Fruit appears twice in the drop-down list of CategoryComboBox, and the same for Apple in TypeComboBox. But it makes no sense to include any item more than once in a combo box list since it's only used to filter records (to filter Apple from the rest in this case) on a form.

Any comments welcome. Cheers

 

[Ken Snell]

Use a query for the combo box Row Source. That query should have SELECT
DISTINCT in it or else it should be a Totals query that is grouped on the
category field.

Same for the type combo box.

--

Ken Snell
<MS ACCESS MVP>

Thanks alot, Allen.
Do you know how I can do about the first problem ( i.e. How to create a

combo box that displays unique values from a field of table/query containng
multiple occurences of each values?)

for example
In tblGrocery,
contains 3 fields: Category-Type-Product
1st record: Fruit-Apple-GreenApple
2nd record: Fruit-Apple-RedApple
3rd record: Meat-Beef-Tbone
4th record: Vegi-Lettuce-FancyLettuce

Fruit appears twice in the drop-down list of CategoryComboBox, and the

same for Apple in TypeComboBox. But it makes no sense to include any item
more than once in a combo box list since it's only used to filter records
(to filter Apple from the rest in this case) on a form.

 

[Guest]

Many thanks to those who helped me out with my problems before.
I now have two dependent combo boxes working together. But neither of combo boxes filter the records in a form. Any comments appreciated. Cheers
(NB. both combo boxes and the form are based on different tables in Access2002)
-------------------------------------------------------------------------------
cboCategory
RowSourceType: Table/Query
RowSource: SELECT DISTINCTROW [CategoryID], [Category] FROM tblCategory ORDER BY [Category];
ColumnWidth: 0",1"
BoundColumn: 1
AfterUpdate: [Event Procedure]

Private Sub cboCategory_AfterUpdate()
Me.cboType.RowSource = "SELECT Type FROM" & _
" tblType WHERE CategoryID = " & Me.cboCategory & _
" ORDER BY Type"
Me.cboType = Me.cboType.ItemData(0)
End Sub

 

[Ken Snell]

Is the query that is the form's recordsource using the values from the two
combo boxes to filter its records? And do you do a requery of the form's
recordsource at some point after you choose the values in the combo boxes?

--

Ken Snell
<MS ACCESS MVP>

Many thanks to those who helped me out with my problems before.
I now have two dependent combo boxes working together. But neither of

combo boxes filter the records in a form. Any comments appreciated. Cheers

(NB. both combo boxes and the form are based on different tables in Access2002)

--------------------------------------------------------------------------

-----
cboCategory
RowSourceType: Table/Query
RowSource: SELECT DISTINCTROW [CategoryID], [Category] FROM tblCategory ORDER BY [Category];
ColumnWidth: 0",1"
BoundColumn: 1
AfterUpdate: [Event Procedure]

Private Sub cboCategory_AfterUpdate()
Me.cboType.RowSource = "SELECT Type FROM" & _
" tblType WHERE CategoryID = " & Me.cboCategory & _
" ORDER BY Type"
Me.cboType = Me.cboType.ItemData(0)
End Sub
-------------------------------------------------------------------------- -----
cboType
BoundColumn: 1
-------------------------------------------------------------------------- -----
frmShop
RecordSource: qryAllProducts (contains all fields in tblCategory and tblType)
Filter: (Blank)

Use a query for the combo box Row Source. That query should have SELECT
DISTINCT in it or else it should be a Totals query that is grouped on the
category field.

Same for the type combo box.