We help IT Professionals succeed at work.

Trying to filter a sub-report

SteveL13
SteveL13 asked
on
102 Views
Last Modified: 2017-03-14
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

Jeffrey CoachmanMIS Liason
CERTIFIED EXPERT
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 & "'"
CERTIFIED EXPERT
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
CERTIFIED EXPERT
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
Jeffrey CoachmanMIS Liason
CERTIFIED EXPERT
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.
CERTIFIED EXPERT
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
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION

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