troubleshooting Question

Filtering a report on Open

Avatar of colevalleygirl
colevalleygirlFlag for United Kingdom of Great Britain and Northern Ireland asked on
Microsoft Access
8 Comments1 Solution586 ViewsLast Modified:
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

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

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?
Join our community to see this answer!
Unlock 1 Answer and 8 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 8 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros