473,325 Members | 2,671 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,325 software developers and data experts.

Having trouble with filter on multi-select listbox

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, 101 views)
Jun 15 '12 #1
1 1841
TheSmileyCoder
2,322 Expert Mod 2GB
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

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

Similar topics

1
by: Anand | last post by:
Hi i am having trouble adding a recordset into the access database, the code seems to be working fine it passs and parses through all variables just fine without showing any errors and also when i...
2
by: ed | last post by:
i'm having trouble with a form. I want to be able to type in the address of the form with the data for the form items in the URL (ie: http://somesite.com/formpage.html?field1=data1&field2=data2)....
1
by: Lauren Wilson | last post by:
I'm having trouble with the Access VBA help on my installation of A2K with Dev tools. Every time I try to retrieve help for items listed in the Object Browser (and SOME other items as well),...
2
by: Jozef | last post by:
Hello, I am trying to put together a module and open a workspace on a database that has a simple password (using Access XP). This is the lin that I'm having trouble with; Set wrk =...
0
by: Jozef | last post by:
Hello, I'm having trouble with the download links on my web server. The error I'm getting is; CGI Timeout The specified CGI application exceeded the allowed time for processing. The server...
1
by: Jozef | last post by:
Hello. I'm having trouble creating a blank solution (and ASP.net web application) from my laptop. I own the server (in fact it's sitting right next to me) and have added the URL to the trusted...
2
by: Jake Barnes | last post by:
I've read over the documentation for these effects: http://wiki.script.aculo.us/scriptaculous/show/CombinationEffectsDemo I want to include them on my page. I tried attaching using onload, but...
3
by: Michael | last post by:
Hi all, I'm having trouble PInvoking a TCHAR within a struct. I'll paste the specific struct's API definition below. I've tried so many numerous variations. The main Win32 error I get is...
5
by: tkondal | last post by:
Hi all. I just started looking at Python's ctypes lib and I am having trouble using it for a function. For starters, here's my Python code: from ctypes import*; myStringDLL=...
2
by: Stu | last post by:
Hi guys, I've been having trouble getting the clock function to work portably, please could I get some thoughts? <Possibly OT comments> It works fine on my laptop (under WinXP) and on my...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.