Multiple combo boxes that filters records in a form

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!

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Sam Kuo" .(donotspam) wrote in message
...
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!

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


"Allen Browne" wrote:

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Sam Kuo" .(donotspam) wrote in message
...
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!

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

"Sam Kuo" .(donotspam) wrote in message
...
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


"Allen Browne" wrote:

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Sam Kuo" .(donotspam) wrote in message
...
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!

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)

"Ken Snell" wrote:

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.

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

"Sam Kuo" .(donotspam) wrote in message
...
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)

"Ken Snell" wrote:

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.