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

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

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

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:

ASKER CERTIFIED SOLUTION

Gustav Brock

membership

This solution is only available to members.

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

Start Free Trial

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