Why my multiselect listbox doesn't pass the value to my Select query? 3

[jane30]

Hi, I have a subform containing 3 controls: StartDate, EndDate (both are bound text box), and HospCode (unbound multiselect listbox). All three of them are parameter values for my Select query. The first two controls work perfectly. But the last control sort of give me hard time. It doesn't pass the value to my query at all 'cause I always get nothing. The expression is like this: [Forms]![SwbSubMenu]![FrmSelPer].[Form]![List65]

What I try to do is to allow users to choose more than one hospital. I used to use a text box defaulted to "AA,BB,CC,DD,EE,FF" The query returned me nothing. But it worked if only one hospCode, e.g. "AA". Any suggestion how to make this work? Use multiselect listbox or textbox?

Thanks a bunch in advance

 

[JerryDennison]

You cannot pass a multiselect listbox value directly to a query. You must build the SQL Where clause and pass it to the query. This is usually done with a hidden textbox on your form and some code in the AfterUpdate Event procedure of the listbox. Basically, you must iterate through the choices made in the listbox and pass these to the hidden control which is used by the query. Sounds convoluted but it actually works well and isn't too hard to implement.

Sub Listbox_AfterUpdate()

Dim varItem As Variant
Dim txtTemp As String

For Each intItem In Me.ListboxName
txtTemp = txtTemp & Me.LisboxName.ItemsSelected(varItem) & " Or "
Next

txtTemp = Left(txtTemp, Len(txtTemp) - 4)

Me.HiddenControl = txtTemp

End Sub

 

[jane30]

Thanks, Jerry!

Do I need to define intItem as variant first? I tried to define it then test the procedure. it returned an error message:Ambiguous name detected:Left. Then I deleted TxtTemp=Left(TxtTemp,Len(TxtTemp)-4) for testing purpose. then I got another error message: Object doesn't support this property or method.(Error 438). The line "For Each IntItem...." is highlighted.

What should I do, Jerry? thank you.

 

[JerryDennison]

You need to check your references. Open a module. Goto Tools... References... You'll need to make sure Visual Basic for Applications is checked. Also, make sure no references are marked as 'missing'.

 

[jane30]

thanks, Jerry!

 

[JerryDennison]

You're welcome.

 

[Jackie]

I am having the same problem. Your code is very helpful. How do you pass the hidden control value to the query?

I include the following in my "criteria" grid where txtAppSelected is a hidden text box:

([forms]![key word search]![txtAppSelected]). However the query returns zero records

Thank you.
Jackie

 

[JerryDennison]

Un hide the hidden control and make sure what is being passed to it. Then check your query using the actual text value rather than the hidden control to see if the problem is with the recordset or the control.

 

[Jackie]

The textbox control is un-hidden.

How do tell the query to use the the actual text value rather than the hidden control?

I suspect it is a syntax problem. I have been testing a variety of combinations of &'s and ", but have not hit upon the correct syntax.

Thank you.

 

[JerryDennison]

No, do not use the '&' or '"'. The value in the textbox should look something like the following:

1234 Or 5678 Or 9012

As for passing the value of the hidden control. You've already set that up correctly(it would appear). It's a matter of insuring the syntax of what is passed is acceptable.

 

[Jackie]

The values displayed in the text box are in the following format: abc or def or ghi

The query executes correctly when only 1 value is selected and displayed in the text box.

What should the criteria row in the query look like?

 

[JerryDennison]

A couple of things. I copied and pasted the code incorrectly. Use the following to build the Where clause:

Private Sub ListBoxName_AfterUpdate()

Dim varItem As Variant
Dim txtTemp As String

For Each varItem In Me.ListBoxName.ItemsSelected
txtTemp = txtTemp & Me.ListBoxName.ItemData(varItem) & " Or "
Next

txtTemp = Left(txtTemp, Len(txtTemp) - 4)

Me.HiddenControlName = txtTemp

End Sub

Also, this isn't designed to be used as the criteria source for a query but rather as the Where clause when calling a report.

DoCmd.OpenReport "ReportName", , Forms!FormName!HiddenControl

Depending on the data type you may need to concatenate single or double quotes around each criteria.

 

[Jackie]

So what is the easiest way to base a query on a multiple select list box?

I have no trouble basing a query on a single select list box.

Jackie

 

[JerryDennison]

This is true. Adding the additional criteria on the fly is sometimes tricky. It may be better to simply build the entire SQL and use that to create a query/recordset.

 

[nashman]

I had the same issue as well, the only way it would work correctly was when I had only a single selected item. The problem is that when your query refers to the control in the form and sees x or y or z it will transfer this into the query criteria as "x or y or z" with the quotes. The query sees this as only one criteria and will look for it exactly as typed. I felt like pulling my hair out. ventually i decided to create checkboxes instead since I only had 7 possible selections. I then created 7 hidden textboxes and referred each checkbox to a textbox when selected. It was then only a matter of right clicking in the criteria area in the query and using the "Build" feature as follows...

Select the form on the left hand side, select the first hidden control in the middle, double click on the <value> indicator in the right column, then click on the OR button, then selecting the second hidden control...etc. It works like a charm. This is really only useful when you have few possible selections. If you have a lengthy list then you would have to use VBA and I am sorry, I am out of my field on this one. If anyone knows how to translate the &quot;Build&quot; statements into VBA, I would be interested.

nashman

 

[idd]

Has anyone managed to find a way of using the selected items from a multi-select list box as the criteria for a query. I'm stuck with the same problem. I have done everything so far that Jerry has mentioned, my hidden textbox updates after the items are selected in the listbox, but how do I get my query to use the value of the textbox as the criteria.

My scenario is that the user will use the multiselect listbox, select all courses they want then a form is opened which is based on a query displaying details of the selected courses. I want the results to be filtered either

at the query stage by making the listbox selection as the criteria, which having read all the posts in this thread, sounds like it can't be done,

or at the stage when the form is opened. I have tried adding the hiddencontrols value as the where clause when opening a form but it doesn't work.

is this the way to go about it or is there a better alternative solution.

 

[JerryDennison]

My suggestion will not work as the criteria for a query but will work to build the SQL of a query directly or to pass as the WHERE claus when calling a report. To use as the where claus when calling a report you set the field to the criteria:

DoCmd.OpenReport &quot;ReportName&quot;, , &quot;[FieldName] = &quot; & Forms!FormName!HiddenControl
&quot;Advice is a dangerous gift, even from the wise to the wise, for all course may run ill.&quot; J.R.R. Tolkien

 

[idd]

Jerry,

Won't the last suggestion work when opening a form and using the hidden controls value as the where condition in the open statement:

FormName1 is the form I want to open and Formname2 is the form with the listbox.

DoCmd.OpenForm &quot;FormName1&quot;,,, [CourseNo] = &quot; & Forms!FormName2!HiddenControl&quot;

 

[idd]

Jerry,

I forgot to add, if what I wrote above doesnt work what can I do to solve the problem.

Thanx in Advance

Idd

 

[WildHare]

I wrote a FAQ ( faq701-1927 ) on a technique to table-drive queries that use a multi-select list box. It's in the Access queries and JET SQL forum here. You might find some information there - I also placed a sample on my site.

It's something to consider. How many of you believe in telekinesis? Raise my hand...
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm