Go Back   Access World Forums > Microsoft Access Discussion > Forms

Thread Tools Rate Thread Display Modes
Old 04-01-2003, 02:00 PM   #1
Registered User
Join Date: Feb 2003
Location: Louisiana
Posts: 51
Thanks: 0
Thanked 0 Times in 0 Posts
Question Multi Select ListBox criteria in query

I'm wanting to be able to select multiple items in a listbox and then filter a report by what is selected. I have the report's rowsource as a query, the criteria of the query points to a text box on my form. I have code that puts all the selected items in a string with each selected item separated by OR and then puts this string in that textbox. If I only select one item it will filter the report correctly, however with more than 1 selected it show a blank report. So it's not getting the criteria in the right format or something like that.
If I have 4 items selected this is an example of the string it would send to the textbox. '3R232' OR '3R235' OR '3R2PD.01' OR '3RA016' . The query has this in the criteria [Forms]![Filter Report Form]![AFENumber1] which points to the textbox on the form.
Here is the code for the on click of a command button:
Dim ctlSource As Control
Dim strItems As String
Dim intCurrentRow As Integer
Set ctlSource = Me!lstAFENumber

For intCurrentRow = 0 To ctlSource.ListCount - 1
If ctlSource.Selected(intCurrentRow) Then
strItems = strItems & Chr(39) & ctlSource.Column(0, intCurrentRow) & Chr(39) & " OR "
End If
Next intCurrentRow
strItems = Left(strItems, Len(strItems) - 4) 'Remove the last " Or "
Me.AFENumber1 = strItems

I then open the report with an afterupdate event of a combo box which the user can select the name of the report to open. It doesn't work quite right. Is there anyway to do this with the current set up I have(e.g. with the underlying query of the report)?


oihjk is offline   Reply With Quote
Old 04-01-2003, 03:58 PM   #2
Registered User
Join Date: Aug 2002
Location: Raleigh, NC USA
Posts: 871
Thanks: 0
Thanked 1 Time in 1 Post
What you might have to do is each time change the underlying query through code.

I had a similar problem that Pat Hartman helped me out with. When you've got more than one item selected, using [Forms]![Filter Report Form]![AFENumber1] will actually pull in a string expression. So the result would end up looking like:

"'3R232' OR '3R235' OR '3R2PD.01' OR '3RA016'"

And of course that doesn't work.

Create the procedure to take that criteria and generate the SQL statement for the query and then open the report.
Rob.Mills is offline   Reply With Quote

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

All times are GMT -8. The time now is 12:32 PM.

Microsoft Access Help
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post

Sponsored Links

Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World