Multiselect for listbox

[scrappe7]

Hi

I am wondering how to allow multiselect with a query. I have a form with 6 listboxes. I set the listbox select properties to extended. However, when you select more than 1 value in a listbox the query will not work and will produce nothing. Since multiselect is enabled in access, how do you make it work with a query. I am ignorant with most VB, especially something like this. Details are extremly helpful for me as is some sample code. Thank you in advance.

Ed

 

[nath]

Hi Scrappe,

I presume you have a multiselect box in Access (not Excel). The way I do this kind of things may seem long winded -and if someone does it quicker I'd be happy to know- but it works.

In your form add a button. The idea is that, when you click on the button, you built a new query with all the item which have been selected. Then you replace your old query by the new one. In the click event of the button write a code like the following (to adapt to your own circumstances):

Dim VarItem As Variant
Dim str as String
Dim count as Integer

count = 0
str = "SELECT * from MYTABLE WHERE " 
For Each VarItem In MyList.ItemsSelected
        If count <> 0 Then
            str = str & &quot; OR &quot;
        End If
            str = str & &quot;myCriteria = '&quot;
            str = str & myList.ItemData(VarItem) & &quot;'&quot;    
Next VarItem

Dim dbs As Database
Dim qdf As QueryDef
    
Set dbs = CurrentDb
Set qdf = dbs.QueryDefs(&quot;myQuery&quot;)
    
qdf.SQL = str
[\code]

Hope this helps,

Nath

 

[scrappe7]

Would this check multiple listboxes all with possible multiselect properites, or is this just for 1 list box. what exactly would i change in your sample code to adapt to my program. (ie where do i put my query names and etc.) i take it MYTABLE is a reserved word, or do i out file names there?

Also, i noticed the OR statement, if i changed that to an "and" statement would it then place all the selected items into 1 long string that MUST ALL be found to yield data from the table, instead the OR statement which i presume would search for ANY of the selected items in a table.

Thanks for the help as you can see i'm pretty far off with this type of code, not my cup of tea.

Thank you.

 

[nath]

Hi Scrappe,

The code is for one listbox only.
All words starting by "My" correspond to your own circumstances and you have to change them according to your needs:
"MYTABLE" is your table name.
"MyList" is the name of your listbox(es).
"Mycriteria" is the field name of your table that you want to compare to the selected items in the listbox(es).
"MyQuery" is the name of your query.

You are right about "OR" and "AND". You can modify the str string according to your own query. Try to see what your query should look like and to build it based on my code above and the appropriate changes.

If it doesn't work, come back with more precision about what your query is supposed to do and what information each listbox contains.

Good luck,

Nath