Link to home
Start Free TrialLog in
Avatar of colevalleygirl
colevalleygirlFlag 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?
Avatar of colevalleygirl
Flag of United Kingdom of Great Britain and Northern Ireland image


Forgot to say: Access 2010 in case it's relevant.
Avatar of 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'
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.
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.
Avatar of mbizup
Flag of Kazakhstan image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Also before trying that, try your original post with no modifications other than starting with FilterOnLoad set to Yes.
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.