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

 

[jhargram]

Hi I have some doubt regarding listbox. Actually i have two table
1. employee(columns are emp_id, emp_name)
2. task (columns are task_id and emp_name)
In task I have 10 or more records, and in employee table we have 2 employee like:x and y
I have a form is form1 and 2 listbox. one for employee(multiselect) and task(multiselect).

Now if i select booth employee from employee listbox and click submit then it should update in task table and all the records from task devided into booth employee like: if it is 10 record then first five emp_name should x and remaining should y. Can you please update me on this. Thanks in advance.