Filter on many subforms

[tamer64]

I have a Mainform and a subform I recently finished. I had a combo box on the main form that filtered on the Sub Form. It worked great, but know I have added 5 more subforms to the main form. Is it possible to have one combo box filter on all subforms?
The mainform is called "Stats_Form_Main" which is unbound. I have six subforms (Subform_A, Subform_B etc.)which are all bound to six different tables. Each of the subforms contain a field called "Supervisors" I would like to have the combo box filter on this field. At which point all subforms would pull up only the information associated with the supervisor. The code I previously had worked great with one main form and one subform. Can I modify the code below to work with filtering six subforms?

Subform_A is bound to Stats_Employee_Table_PT1
Subform_B is bound to Stats_Employee_Table_PT2
Subform_C is bound to Stats_Employee_Table_PT3
Subform_D is bound to Stats_Employee_Table_PT4
Subform_E is bound to Stats_Employee_Table_PT5
Subform_F is bound to Stats_Employee_Table_PT6
[blue]
Dim strFilter As String
strFilter = "SELECT Stats_Employee_Table_PT1.* "
strFilter = strFilter & "FROM Stats_Employee_Table_PT1"
strFilter = strFilter & "WHERE (((Stats_Employee_Table_PT1.SupervisorID)='" & Me.CboSelectSup & "'" & "));"

Forms![Stats_Form_Main]![Subform_A].Form.RecordSource = strFilter
Forms![Stats_Form_Main].Form.Requery
[/blue]

 

[MajP]

You do not need any code to do this. Just link every subform to the combobox in the parent and child links of the subform controls. The code is unnecessary,and not as robust.

your properties should look like

linkMasterFields:[cboSelectSup]
linkChildFields: [SupervisorID]

If you link the subforms to the combo it has the added benefit of when you add a new record to the subforms it gets the parent ID as a foriegn key.

 

[MajP]

BTW, you likely have a very poor table design.There should be no reason that you have 5 tables, you should have one table and each subform is based off a query of that table. By looking at the table names I guess those tables are nearly identical, with a single field value that differentiates them. Need to consider normalizing your database.

 

[tamer64]

Thank You MajP! It did not cross my mind to link the master and child fields. I am sure this will work and be much easier than code in it.