asked on

How do i open a subform to a filtered record from another form

I am trying to open a subform to a specific filtered record (ID = ID from anotherform)
but i am not getting any filtered records even when i use the syntax below.
where "NotesAssignment" is the form having the subform i want filtered.

the two forms (mainform and NotesAssignment) have a common field (report_id and Ext_rept_urn )

DoCmd.OpenForm "NotesAssignment", acNormal, , "report_id='" & Ext_rept_urn & "'"

Paul Cook-Giles

Peter, is the subform an object on your main form? How are you executing the filter code?

The code below will toggle between displaying filtered data and unfiltered data on a subform (and incidentally change the caption on the button that applies/removes the filter). It uses a literal for filtering, but you can easily swap in your code using a variable. Let me know if this needs more explanation. :)

PCG

<><>
If Me.btnFilterAdv.Caption = "Show Adv" Then 'records are unfiltered
Me.RevenuePreparationSubFrm.Form.Filter = "[Revenue Type] like '*adv*'"
Me.RevenuePreparationSubFrm.Form.FilterOn = True
Me.RevenuePreparationSubFrm.Requery
Me.btnFilterAdv.Caption = "Remove Filter"
Else
'Me.RevenuePreparationSubFrm.Form.Filter = "[Revenue Type] like '*adv*'"
Me.RevenuePreparationSubFrm.Form.FilterOn = False
Me.RevenuePreparationSubFrm.Requery
Me.btnFilterAdv.Caption = "Show Adv"
End If
<><>

ASKER CERTIFIED SOLUTION

peterslove53

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

peterslove53

ASKER

Figured out another route. Just making a split form and hiding the display region solves it