Avatar of colevalleygirl
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

PROC_EXIT:
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
colevalleygirl

8/22/2022 - Mon
colevalleygirl

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

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

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

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

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

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
mbizup

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

ASKER
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

works.

Thank you.