Before trying to filter on a field in the subform, review how filters are normally used within a form.
Take a Products form with a Product Category field.
To get started we will create a new form called Bedroom.
If you don't know how to create a new form please refer to the articles mentioned earlier.
For example, some products might have several suppliers.
You need a subform for the various suppliers of the product in the main form.
Next we need to insert subforms for each of our new Headings. Then click Insert on the top toolbar and select Subform. This time we will select the radio button that says Add an existing form. Please note that the name of the subform will not update until you click on another line. Now we've got the same subheadings and narratives on all of our 3 Headings Bedroom 1, Bedroom 2, and Bedroom 3 thanks to the subform.
Now we need to add the subform beneath Heading: Bedroom 3. Then click insert on the top toolbar and Select Subform from the drop down menu. In the future if we had new narratives or subheadings then the changes will take place for all 3 Headings since they are sharing the same subform.
This means that if you use both the Filter and the change of Record Source together, your code must save the filter state before changing the Record Source and restore it. Record Source = s SQL End If If b Was Filter On And Not Me. Filter On = True End If Exit_cbo Show Sup_After Update: Exit Sub Err_cbo Show Sup_After Update: Msg Box Err.
Assume you have provided both the combos described above (cbo Show Cat and cbo Show Sup) on your main form.
A user can now filter only products of a certain category and from a particular supplier. Record Source = "tbl Product" End If Else s SQL = "SELECT DISTINCTROW tbl Product.* FROM tbl Product " & _ "INNER JOIN tbl Product Supplier ON " & _ "tbl Product.