By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,775 Members | 2,658 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,775 IT Pros & Developers. It's quick & easy.

Having trouble with filter on multi-select listbox

P: 31
Hi All,

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
  1. Private Sub cmdPOHCReport_Click()
  2.  
  3. On Error GoTo Err_Handler
  4.     'Purpose:  Open the report filtered to the items selected in the list box.
  5.     'Author:   Allen J Browne, 2004.   http://allenbrowne.com
  6.     Dim varItem As Variant      'Selected items
  7.     Dim strWhere As String      'String to use as WhereCondition
  8.     Dim strDescrip As String    'Description of WhereCondition
  9.     Dim lngLen As Long          'Length of string
  10.     Dim strDelim As String      'Delimiter for this field type.
  11.     Dim strDoc As String        'Name of report to open.
  12.  
  13.     strDelim = """"            'Delimiter appropriate to field type. See note 1."
  14.     strDoc = "qryPOHC_Customer"
  15.  
  16.     'Loop through the ItemsSelected in the list box.
  17.     With Me.lstVehID
  18.         For Each varItem In .ItemsSelected
  19.             If Not IsNull(varItem) Then
  20.                 'Build up the filter from the bound column (hidden).
  21.                 strWhere = strWhere & strDelim & .ItemData(varItem) & strDelim & ","
  22.                 'Build up the description from the text in the visible column. See note 2.
  23.                 strDescrip = strDescrip & """" & .Column(0, varItem) & """, "
  24.             End If
  25.         Next
  26.     End With
  27.  
  28.     'Remove trailing comma. Add field name, IN operator, and brackets.
  29.     lngLen = Len(strWhere) - 1
  30.     If lngLen > 0 Then
  31.         strWhere = "[Vehicle] IN (" & Left$(strWhere, lngLen) & ")"
  32.         lngLen = Len(strDescrip) - 2
  33.         If lngLen > 0 Then
  34.             strDescrip = "Categories: " & Left$(strDescrip, lngLen)
  35.         End If
  36.     End If
  37.  
  38.     'Report will not filter if open, so close it. For Access 97, see note 3.
  39.     If CurrentProject.AllReports(strDoc).IsLoaded Then
  40.         DoCmd.Close acReport, strDoc
  41.     End If
  42.  
  43.     'Omit the last argument for Access 2000 and earlier. See note 4.
  44.     DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere, OpenArgs:=strDescrip
  45.  
  46. Exit_Handler:
  47.     Exit Sub
  48.  
  49. Err_Handler:
  50.     If Err.Number <> 2501 Then  'Ignore "Report cancelled" error.
  51.         MsgBox "Error " & Err.Number & " - " & Err.Description, , "PrevLim_Click"
  52.     End If
  53.     Resume Exit_Handler
Attached Files
File Type: zip MultiSelect.zip (955.6 KB, 67 views)
Jun 15 '12 #1
Share this Question
Share on Google+
1 Reply


TheSmileyCoder
Expert Mod 100+
P: 2,321
Whats the query or table that the report is based on? That information is missing from your question, and sorry, I wont start downloading attachments from unknown sources. You will have to provide the information in your question.
Jun 18 '12 #2

Post your reply

Sign in to post your reply or Sign up for a free account.