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

 

[Rolls]

Appears that you want to limit a list [CategoryID] to potentially multiple
selections, then use these in a SELECT statement, separating multiple
criteria by OR. You could also join a query result for the selected
criteria to tblProduct and obtain the result.

Suggest that the user interface be similar to the way certain wizards work,
where a list of available choices appears on the left and as each selection
is made it drops from the left list and is added to a list to the right.
The process can be reversed. When the list is correct another button runs
the query with the result you expect being displayed by a form.

(Will have to find the code to do this if you need it.)