How do you transfer the selected items from a Multi-Select List Box to the Criteria of a query?

OK, from the top....

As far as I know you cant type in any sort of formula for a criteria that will adapt depending on the selections/# of selections in a list box. You can however set the sql property of a querydef object to anything you want, including a string. So what we need to do is create a querydef, and a function that will build our sql, then set the sql property for our querydef to that string. so......

In a database called db3, I'm using Table1.field2 and setting the criteria to be one of the selected items in form1.list3 and calling the query 'query1':

In a module, place the following:

Public Function sqlstring()
Dim varitm As Variant
sqlstring = "SELECT Table1.field2 FROM Table1 WHERE (((Table1.field2) In ("
'beginning of our sql"

For Each varitm In [Forms]![form1]![List3].ItemsSelected
sqlstring = sqlstring & """" & [Forms]![form1]![List3].ItemData(varitm) & ""","
'adds each item to the sql
Next varitm

sqlstring = Mid$(sqlstring, 1, Len(sqlstring) - 1)
'gets rid of trailing comma
sqlstring = sqlstring & ")));"
'adds tail of sql statement

End Function

This looks at all selected items, and adds it to our sql. The resulting string can be referred to as sqlstring().


In the After update event of your listbox:

Dim dbs As Database
Dim q1 As QueryDef
DoCmd.DeleteObject acQuery, "Query1"
'kills old query
Set dbs = OpenDatabase("db3.mdb")
Set q1 = dbs.CreateQueryDef("query1", "SELECT
Table1.field2 FROM Table1")
'create query
q1.SQL = sqlstring() '
'set sql property of our query
This kills the old query, and creates a new one using our custom sql statement. Everytime the user selects an item, the query is in effect changed to reflect their selections.

Let me know if this helps, or if you need more info....

P.S. It would save a step or 2 if you could create a querydef object for an existing query....is this possible, anyone?

Eric