Filtering multiple subforms on a main form

[Soupy8728]

Good morning,

I have a main form [frmMain] with a listbox on it. I have 3 subforms [subfrmWORKSPEC], [subfrmCFR], [subfrmRCC]. I have been able to connect the listbox to all three subforms via Master/Child fields. When I select an item in the listbox, all three subforms react accordingly and filter to the selected ID in the listbox.

What I'd like to do is this:



field [WorkSpec] is a field on all three subforms. I'd like to be able to select the [WorkSpec] field on [subfrmWORKSPEC] and have the other two subforms get filtered based off the text field [WorkSpec]. I just can't seem to get this to work.

I added a textbox on the main form and called it txtWorkSpec and I set it's control source to =[subfrmWORKSPEC].[Form]![WorkSpec]. When I select [WorkSpec] on the frmWORKSPEC, the txtWorkSpec box changes to match that field so that part is working.

How do I get the other two subforms to filter on [WorkSpec] based off of the txtWorkSpec field? I can't seem to figure out the correct VBA combination to make this happen. Thanks for your help.

Soup

[jojowhite]

You set the the Parent field of the CFR and RCC subform to [txtWorkSpec].

[Soupy8728]

You set the the Parent field of the CFR and RCC subform to [txtWorkSpec].

Thanks for the reply but I'm not exactly sure how to do that. Can you please explain? thank you

[jojowhite]

on design view click on subform CFR (make sure a Yellow box surrounds the subform) on its Property Sheet:


do the same with subform RCC.

[Soupy8728]

The master/child fields are already linked to the listbox ID. When I select an item in the listbox, it filters the 3 subforms as it should. I need to filter the CFR and RCC subforms even further by linking them to the WORKSPEC subform [WorkSpec] field (or in this case the text box [txtWorkSpec].

[Soupy8728]

on design view click on subform CFR (make sure a Yellow box surrounds the subform) on its Property Sheet:


do the same with subform RCC.

The master/child fields are already linked to the listbox ID. When I select an item in the listbox, it filters the 3 subforms as it should. I need to filter the CFR and RCC subforms even further by linking them to the WORKSPEC subform [WorkSpec] field (or in this case the text box [txtWorkSpec].

[jojowhite]

have you tried it first?
subform WorkSpec will be the Parent
while the other 2 subs will be the Children.

[Welshgasman]

Filtering and linked are two different concepts.
If your linkage is already correct, which you say it is, then simply filter the other two subforms with VBA.
However if that is not the case then link with your mainform txt control.

You can even set the linkages in VBA, so you start with your now used linkage, then change it to link to txt, and change it back when you update the listbox?

[Soupy8728]

have you tried it first?
subform WorkSpec will be the Parent
while the other 2 subs will be the Children.

Ok, it worked. Initially I thought it wasn't because the first few line items in the WORKSPEC form didn't have any subform items associated. Thank you for your help! You guys are awesome!