How To Build A List Of Products From Multiple Categories

[Mark]

My Category and Product tables look like:
TblCategory
CategoryID
Category

TblProduct
ProductID
CategoryID
Product

I have a continuous form with one field named ProductID. I want to populate this
form with a list of products from several different categories. In the header of
the form I have a combobox named CategoryID for selecting the Category. The
CategoryID combobox uses TblCategory for the rowsource. In the detail section of
the form I have a combobox named ProductID with the intent to select one or more
products after selecting the category. The ProductID combobox has a query for
the rowsource with the fields ProductID, Product, CategoryID. The criteria for
the CategoryID field is [CategoryID]. In the AfterUpdate event of CategoryID I
requery ProductId. This works fine for the first category and the products
selected in that category. However, when I select the second category, the
products selected from the first category disappear because the query for
ProductID does not include them in the second category.

What is the method for building a list of products from multiple categories?

Thanks!

Mark

[Adrian Jansen]

You could have several combo boxes, all linked to the same Category
rowsource, and then build a query string in code, using 'OR' to link the
different selections.

Alternately use a multi-select list box for the categories, and again use
code to build a query from the selections.

Either way will involve a bit of coding.

--
Regards,

Adrian Jansen
J & K MicroSystems
Microcomputer solutions for industrial control
"Mark" <mm*****@earthlink.net> wrote in message
news:9w*******************@newsread3.news.atl.eart hlink.net...

My Category and Product tables look like:
TblCategory
CategoryID
Category

TblProduct
ProductID
CategoryID
Product

I have a continuous form with one field named ProductID. I want to populate this form with a list of products from several different categories. In the header of the form I have a combobox named CategoryID for selecting the Category. The CategoryID combobox uses TblCategory for the rowsource. In the detail section of the form I have a combobox named ProductID with the intent to select one or more products after selecting the category. The ProductID combobox has a query for the rowsource with the fields ProductID, Product, CategoryID. The criteria for the CategoryID field is [CategoryID]. In the AfterUpdate event of CategoryID I requery ProductId. This works fine for the first category and the products
selected in that category. However, when I select the second category, the
products selected from the first category disappear because the query for
ProductID does not include them in the second category.

What is the method for building a list of products from multiple categories?
Thanks!

Mark