Trying to filter a sub-report

SteveL13
SteveL13 used Ask the Experts™
on
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

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
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 & "'"
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
You need to set your filter ON:

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

Open in new window

/gustav
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
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
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
I am sure Gustav can get you sorted...

Author

Commented:
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.
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
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

Author

Commented:
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
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
Then use [AgentName] and not the ID.

Or perhaps this would do:

        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

Open in new window

/gustav

Author

Commented:
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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial