Use results from a multi select list box in a query

[Nigel]

I have a multiselect lsit bix and would like to run an update query based on
the values selected in the list box

the query name is mergeclasses and the list box is called oldclass.

Ho would i program it to update a filed based on the values selected in the
listbox or can I use the listbox in the criteria of a query

appreciated

 

[Graham Mandeno]

Hi Nigel

In a SQL WHERE clause, you can use the IN operator:

WHERE [fieldname] IN (value1, value2, value3, ...)

So what you need to do is make a comma-separated list of all the items that
are selected in your listbox and you're 90% of the way there.

The following code will do this for you:

Dim strList As String, varItem As Variant
With YourListboxName
strList = "("
For Each varItem In .ItemsSelected
strList = strList & .ItemData(varItem) & ","
Next varItem
' replace final comma with a close parenthesis
Mid(strList , Len(strList), 1) = ")"
End With

Note that this assumes your field is numeric. If it is text, then each
value in the list needs to be enclosed in quotes - for example:
strList = strList & "'" & .ItemData(varItem) & "'" & ","

Now you just use the list you have created in your update query string:

strSQL = "UPDATE YourTable SET ... WHERE [fieldname] IN " & strList
CurrentDb.Execute strSQL, dbFailOnError