I am having trouble filtering a report with a multiselect list box. The report is generated, but it is not filtered based on the selections of the list box:
lstVehID
Multi Select Simple
Row Source Type Table/Query
Row Source SELECT qryVehicle.Vehicle, qryVehicle.[RBL Status] FROM qryVehicle ORDER BY qryVehicle.Vehicle;
Users enter their information into form:
frmCustomers
A command button (cmdPOHCReport) opens the report:
rptPOHC
which is run off query:
qryPOHC_Customer
I am using Allen Browne's VBA code to filter the query with the selections from the list box.
In an Access 2003 version of this database I was able to set strDoc = "Name of Report", however now
If I set
strDoc = "rptPOHC"
I get a message box asking me for [Vehicle]
If I set
strDoc = "qryPOHC_Customer"
I get "Error 2467 - The expression you entered refers to an object that is closed or does not exist"
I have attached a copy of the DB. Any help would be much appreciated
Expand|Select|Wrap|Line Numbers
- Private Sub cmdPOHCReport_Click()
- On Error GoTo Err_Handler
- 'Purpose: Open the report filtered to the items selected in the list box.
- 'Author: Allen J Browne, 2004. http://allenbrowne.com
- Dim varItem As Variant 'Selected items
- Dim strWhere As String 'String to use as WhereCondition
- Dim strDescrip As String 'Description of WhereCondition
- Dim lngLen As Long 'Length of string
- Dim strDelim As String 'Delimiter for this field type.
- Dim strDoc As String 'Name of report to open.
- strDelim = """" 'Delimiter appropriate to field type. See note 1."
- strDoc = "qryPOHC_Customer"
- 'Loop through the ItemsSelected in the list box.
- With Me.lstVehID
- For Each varItem In .ItemsSelected
- If Not IsNull(varItem) Then
- 'Build up the filter from the bound column (hidden).
- strWhere = strWhere & strDelim & .ItemData(varItem) & strDelim & ","
- 'Build up the description from the text in the visible column. See note 2.
- strDescrip = strDescrip & """" & .Column(0, varItem) & """, "
- End If
- Next
- End With
- 'Remove trailing comma. Add field name, IN operator, and brackets.
- lngLen = Len(strWhere) - 1
- If lngLen > 0 Then
- strWhere = "[Vehicle] IN (" & Left$(strWhere, lngLen) & ")"
- lngLen = Len(strDescrip) - 2
- If lngLen > 0 Then
- strDescrip = "Categories: " & Left$(strDescrip, lngLen)
- End If
- End If
- 'Report will not filter if open, so close it. For Access 97, see note 3.
- If CurrentProject.AllReports(strDoc).IsLoaded Then
- DoCmd.Close acReport, strDoc
- End If
- 'Omit the last argument for Access 2000 and earlier. See note 4.
- DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere, OpenArgs:=strDescrip
- Exit_Handler:
- Exit Sub
- Err_Handler:
- If Err.Number <> 2501 Then 'Ignore "Report cancelled" error.
- MsgBox "Error " & Err.Number & " - " & Err.Description, , "PrevLim_Click"
- End If
- Resume Exit_Handler