Subform control

  • Thread starter Thread starter Dave the wave
  • Start date Start date
D

Dave the wave

I have a form that has 2 combo box controls and a subform. The first combo
box selects a "Category" (for example) which should limit both the item list
for combo box 2 and the records shown in the subform. Making a selection
with combo box 2 should further limit the records shown in the subform.

Ideally, all records should be visible when the form is opened for the first
time.

I would appreciate an outline of how to attack this problem.
 
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
 
Back
Top