CODEWITHSUNDEEP
ms-accessfiltering
Chris Ray
Chris Ray

Reputation: 51

Apply a filter via button to match records

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)

enter image description here

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.

Upvotes: 0

Views: 939

Answers (2)

marlan
marlan

Reputation: 1485

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"

Upvotes: 1

marlan
marlan

Reputation: 1485

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.

Upvotes: 0

Related Questions