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