Filtered Combo Box in Subform based on Combo Box in Main Form

[Dip8324]

Hello,


I am a newbie to Access and need help with filtered combo boxes from the gurus out there.


I am trying to filter a combo box in a subform based on the value in a combo box in the main form that this subform is linked to. I then need another combox box in the subform filtered on the selection made in the previous combo box. The first combo box in the main form is a list of Customers. After a customer in the main form (titled frmPurchaseOrder in the attached database) is selected then I want the first combo box in the subform (titled subfrmPurchaseOrderDetail) to display a list of the Orders for that customer. Once the Order is selected in this combo box then I want the next combo box (also in the subform) to display a filtered list of items for that Order.


The Orders combo and OrderDetails combos are both based on queries (named “qryOrderSearchForPO” and “qryOrderDetailIDSearchForPO” in the attached database).


Any help you can provide to fix this would be much appreciated as I cannot get this to work.



Thank you!
Dip8324

[ajetrumpet]

I doubt you'll get anyone to look at the actual file, but there key things you have to know when you do something like this:

1. a subform has a control name and it's actual form name associated with it.
2. when you work with comboboxes, the subform's control name always has to be referenced. the subform's form name/object name is 99% of the time not useful
3. When you reference a combo in a subform from a main, this syntax should be used:
   
   • me.subform.form.controls("combo").value
   
   
4. and when you need a reference the other way, from sub back to main, you need this:
   
   • me.parent.controls("combo").value

now, depending on the version of access you're using, syntax may have changed or not work, for reasons always unexplained of course, so if they don't, the syntaxes need to be varied slightly.

[Dip8324]

Adam,

Thank you for the quick and useful advice.

After some head scratching i realized that it was a minor error that was not making the combos work.
In the VBA code editor in my main form i was not referencing the combo boxes in the subform with the correct syntax (was excluding the main form's name)
The correct syntax is:

Forms!frmPurchaseOrder!subfrmPurchaseOrderDetail!O rderSearchCbo.Requery

In the subform i put in:
OrderDetailCbo.Requery

In the query for the second combo box in the subform, in the criteria field i put in (before i did not have the main form reference):
[forms]![frmPurchaseOrder]![subfrmPurchaseOrderDetail]![OrderID]

It works great now.

Attached is the fixed fixed database just in case another newbie needs to reference it. Just adding my 2 cents.

Thanks everyone!

Dip8324

[RuralGuy]

You can follow the link in my sig for directions or just use the Thread tool to mark this thread as Solved.

[Dip8324]

Thank you Ruralguy.

[RuralGuy]

We're here to assist whenever possible.