Dave,
You first need to refine your logic a little. You should have a table for
Catgory and a table for whatever is in the second combobox which let's call
products. Your tables should look like:
TblCategory
CategoryID
Category
TblProduct
ProductID
CategoryID
Product
<Any additional fields needed>
As you can see for the second combobox, you don't need to limit the subform by
Category because when you limit the list by a certain ProductID, you
simultaneously specify the CategoryID. So-oooo, to be able to choose from any
category or from a specific category in the second combobox you need to create a
query based on TblProduct and include all three fields. In the CategoryID field,
enter this expression for the criteria:
Forms!NameOfYourForm!CbxCategory Or (Forms!NameOfYourForm!CbxCategory Is Null)
**CbxCategory is the name of the first combobox. Use this query as the rowsource
of the second combobox.
To limit the list in the subform, base your subform on a query that includes the
field ProductID. Put the following expression in the criteria of ProductID:
Forms!NameOfYourForm!CbxProduct Or (Forms!NameOfYourForm!CbxProduct Is Null)
**CbxProduct is the name of the second combobox.
To get the subform to change when you make a selection in cbxProduct, put this
expression in the AfterUpdate event of the combobox:
Me!NameOfYourSubformControl.Form.Requery
The first combobox is straight forward based on TblCategory. To get the second
combobox to change and then the subform to change when you make a selection in
the first combobox, put the following expression in the AfterUpdate event of the
first combobox:
Me!cbxProduct.Requery
Me!NameOfYourSubformControl.Form.Requery
