Unbound Combo Box to filter form

[Firefighter22]

I know this question has been asked before, but I can't seem to find one that fits my situation. I have a form, let's say "frm1". frm1 has 3 subforms, frm2, frm3, frm4. I want to have an unbound combo box that filters all three subforms to show a selected InventoryID. There may be more than 1 instance of InventoryID in all three forms.


thanks in advance

[June7]

Does frm1 have a RecordSource with the InventoryID field? If so, use Master/Child link properties to synchronize the subforms with the parent form. Filter the main form to the desired InventoryID and the subforms will follow. Are the subforms in Datasheet view?

[Firefighter22]

Actually, frm1 is basically just a holding place for the 3 subforms and doesn't currently have a record source. One subform is for Purchases, one for Sales, and the last for Available Inventory.
Yes, all 3 subforms are in Datasheet view, the main form is a Single Form.

[June7]

The simplest solution might be to implement the scenario I described. Set main form RecordSource to the table having InventoryID as primary key. Set Master/Child links in each of the subform container controls. Then use macro or VBA code to set the main form filter based on the selection in combobox. Code in the AfterUpdate event of the combobox. VBA simply:
Me.FilterOn = False
Me.Filter = "InventoryID=" & Me.comboboxname
Me.FilterOn = True

Or set the form's Filter property to: InventoryID=Forms!frm1!comboboxname
then remove the Me.Filter line from the VBA code.

There is probably a macro version of this but I only use VBA.

[Firefighter22]

Thanks for your help, works great