Apply a filter via button to match records

Question

I have a form with two subforms (3 tables, total).

Tables: PI (patient information)
Referrals
Reports

The form displays PI at the top and then Referrals and Reports as subforms (see screenshot below)

In the current view, the form is displaying all referrals for patient #1 (1 referral available) and ALL reports regardless of whether it's tied to a referral or not. I'd like to create a button so that when clicked, it applies a filter to show ONLY reports that match the currently displayed ReferralID.

Answer 1

Please see this page on how to refer to controls and properties on sub and parent forms. As for the button for applying the filter, this should be a Toggle Button, clicked for applying the filter, or up, for removing it.

In your case, add to the Referrals sub Form a Toggle Button named tbtnFilter, and in the OnCurrent event of form, modify the Reports filter:

Me.Parent!Reports.Form.Filter = "ReferralsID = " & CStr(Me.ReferralsID)
Me.Parent!Reports.Form.FilterOn = Me.tbtnFilter

In the tbtnFilter OnClick Event apply and remove the filter, and modify the ToggleButton.Caption Property to be "Apply Filter" when up (FALSE), and "Remove Filter" When down (TRUE):

Me.Parent!Reports.Form.FilterOn = Me.tbtnFilter
Me.tbtnFilter.Caption = IIf(Me.tbtnFilter, "Remove", "Apply") & " Filter"

Answer 2

SubForm controls on PI Form have LinkChildFields property and LinkMasterFields property.

Edit: You can have Reports form as a subform in Referrals sub form. Iv'e done it a few times, it runs nicely. It just may limit you a-bit in form design.

MSDN Article on SubForm.LinkChildFields Property:

You can use the LinkChildFields property (along with the LinkMasterFields property) together to specify how Microsoft Access links records in a form... to records in a subform... If these properties are set, Microsoft Access automatically updates the
related record in the subform when you change to a new record in a main form.