Avatar of SteveL13
SteveL13
Flag for United States of America asked on

Trying to filter a sub-report

I'm using this code to filter the results in a sub-report.  But it gets "stuck" on the line, "Me.Filter = "[Agent] = '" & strAgent & "'""

Also, I'm not sure if it belongs in the onopen or onload event.

Here is my code:

Private Sub Report_Open(Cancel As Integer)

    If DLookup("[SecurityLevel]", "LOCALtblCurrentUser") > 1 Then
        Dim strAgent As String
        strAgent = DLookup("CurrentUser", "LOCALtblCurrentUser")
        Me.Filter = "[Agent] = '" & strAgent & "'"
        FilterOn = True
    Else
        DoCmd.OpenReport "rptReferrals", acViewPreview, "", "", acNormal
    End If

End Sub

Open in new window

Microsoft Access

Avatar of undefined
Last Comment
SteveL13

8/22/2022 - Mon
Jeffrey Coachman

What do you mean by: " it gets "stuck" on" ?
I am not quite sure of your code there, ...perhaps another Expert can help there..

But try it like this, ..as a quick test
"[Agent] =" & "'" & strAgent & "'"
Gustav Brock

You need to set your filter ON:

Me.Filter = "[Agent] = '" & strAgent & "'"
Me.FilterOn = True

Open in new window

/gustav
Gustav Brock

But your code doesn't make much sense ... you are opening a new report in the OnLoad event of a subform ... not very likely what you wish.

/gustav
Your help has saved me hundreds of hours of internet surfing.
fblack61
Jeffrey Coachman

I am sure Gustav can get you sorted...
SteveL13

ASKER
I am opening a report via a command button on a form.  The code behind the command button is:

        Dim Agent As String
        Dim WhereCondition As String
        Agent = Nz(DLookup("[CurrentUser]", "LOCALtblCurrentUser"))
        WhereCondition = "[Agent] = '" & Agent & "'"

        DoCmd.OpenReport "rptReferrals", acViewPreview, , WhereCondition, acWindowNormal

Open in new window


But then I am trying to open the subreport using the same filter with this revised onclick code and it is not working:

        Dim Agent As String
        Dim WhereCondition As String
        Agent = Nz(DLookup("[CurrentUser]", "LOCALtblCurrentUser"))
        WhereCondition = "[Agent] = '" & Agent & "'"
        
        Reports!rptReferrals.Report!subrptReferralCounts.Filter = "[Agent] = '" & Agent & "'"
        Me.FilterOn = True

        DoCmd.OpenReport "rptReferrals", acViewPreview, , WhereCondition, acWindowNormal

Open in new window


I do not know how to filter the sub-report using the same criteria.
Gustav Brock

The first is correct.
The second you could solve extremely easy by setting the Master/Child link fields of the subreport control of the main report to [Agent]

/gustav
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
SteveL13

ASKER
I tried that.  But it doesn't work because I have a many to one relationship.  Here is a "picture" of the report-subreport I'm trying to create:

Example
ASKER CERTIFIED SOLUTION
Gustav Brock

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
SteveL13

ASKER
Worked:

        Dim Agent As String
        Dim WhereCondition As String
        Agent = Nz(DLookup("[CurrentUser]", "LOCALtblCurrentUser"))
        WhereCondition = "[Agent] = '" & Agent & "'"
       
        DoCmd.OpenReport "rptReferrals", acViewPreview, , WhereCondition, acWindowNormal
        Reports!rptReferrals.Report!subrptReferralCounts.Report.Filter = "[Agent] = '" & Agent & "'"
        Reports!rptReferrals.Report!subrptReferralCounts.Report.FilterOn = True