Avatar of colevalleygirl
Flag for United Kingdom of Great Britain and Northern Ireland asked on

Filtering a report on Open

I have a report that has to be filtered based on the contents of an array (which is populated in sub GetCheckedItems from values selected by the user before the report is opened).

The report is opened via right-click context menu using

DoCmd.OpenReport gstrActiveReport, acViewReport

Open in new window

where gstrActiveReport is a global string identifying the report.

The report's Open sub looks like:

      Private Sub Report_Open(Cancel As Integer)

         Dim lngID As Long
         Dim lngIndex As Long
         Dim strWhere As String      'String to use when building up criteria
         Dim strCriteria As String
         Dim lngLen As Long          'Length of string

230      strWhere = ""
240      strCriteria = ""

250      If GetCheckedItems(avarCheckedItems) = False Then
260          DisplayMessage "You have not selected any repositories"
270          Cancel = True
280          Exit Sub
290      End If

         'Loop through the CheckedItems in the list box.
310      For lngIndex = LBound(avarCheckedItems) To UBound(avarCheckedItems)
320          lngID = avarCheckedItems(lngIndex, 0)
360          strWhere = strWhere & CStr(lngID) & ","
380      Next lngIndex

420      If strWhere <> "" Then
               'Remove trailing commas. Add field name, IN operator, and brackets.
460          lngLen = Len(strWhere) - 1
470          strWhere = "[RepositoryID] IN (" & Left$(strWhere, lngLen) & ")"
480          strCriteria = strCriteria & strWhere
490      End If

500      Me.Filter = strCriteria
510      Me.FilterOn = True

530      Exit Sub

     End Sub

Open in new window

If a single item is checked, strCriteria is set to (for example) "[RepositoryID] IN (17)" and Me.Filter is being set to strCriteria but Me.FilterOn is not being set True, and when the report opens it isn't filtered.

The report has AllowFilters set to True and FilterOnLoad set to False.

What am I doing wrong?
Microsoft Access

Avatar of undefined
Last Comment

8/22/2022 - Mon

Forgot to say: Access 2010 in case it's relevant.

Try moving the code that determines the criteria to the command button which opens the report (before the OpenReport), and change your OpenReport statement to:

DoCmd.OpenReport gstrActiveReport, acViewReport,,strCriteria

and set the Filter On Load property to 'Yes'

The report isn't opened by a command button -- it's opened by a right-click context menu common to all reports that involves a procedure that runs docmd.openreport

If I make the change you suggest, I'll need either to include a case statement in the context menu procedure to check which report is invoking it and then construct the appropriate criteria, or have a different right-click menu for each report. It seems better (to me) to have the code specific to a given report within the report module itself.

If it's the only way to do it, then of course I will, but is there something fundamentally wrong with the code I've written?

Will do a quick and dirty fix to try your suggestion anyway.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck

You could test it out by commenting out the open event code in your report, and hard-coding your criteria -  right from the debug window:

Docmd.OpenReport "YourReportName",acviewpreview, WhereCondition := "RepositoryID IN (1,2,3,4,5)"

Open in new window

(or something similar)

The quick and dirty fix does work. I'd still prefer to get something triggered by the open event for the report if possible.

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

Also before trying that, try your original post with no modifications other than starting with FilterOnLoad set to Yes.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.

Simply setting FilterOnLoad to Yes doesn't work.

The recordsource is a saved query qryrptTaskbyRepository and already has a Where clause.

Me.RecordSource = "Select * from qryrptTaskbyrepository where " & strCriteria 

Open in new window


Thank you.