Selecting more than one value from a list or combo box

[Barkley via AccessMonster.com]

Does anyone know how to allow a user to select more than one value from a
list or combo box?

Thanks

 

[Graham Mandeno]

You cannot select more than one value from a combo box, but you can for a
listbox.

Set the MultiSelect property to either "Simple" or "Extended".

Simple means that you click an item to select it and click it again to
unselect it.

Extended makes the selection behave like a Windows Explorer list -
ctrl-click to select multiple items, or click then shift-click to select a
block.

 

[Barkley via AccessMonster.com]

OK, so far so good...

now I want to set the multiple values as parameters for a query... any ideas?

Thanks

 

[Graham Mandeno]

You need to enumerate each selected item in the listbox and construct a
WHERE-clause of the form:
[Fieldname] IN (val1, val2, val3, ...)

This code should get you going:

Dim strWhere As String, varItem As Variant
strWhere = "[Fieldname] IN ("
With MyListbox
For Each varItem In .ItemsSelected
strWhere = strWhere & .ItemData(varItem) & ","
Next varItem
End With
' change the last "," to ")"
Mid(strWhere , Len(strWhere ), 1) = ")"

Now you can use strWhere in several different ways:
1. Pass it to OpenForm or OpenReport as a Where-condition
2. Append it to a SQL Select statement to open a recordset
3. Append it to a SQL Update (or Delete, or other) statement to execute
as an action query
4. ...

If [Fieldname] is a text (not numeric) field, then you need to include
quotes around each value:
strWhere = strWhere & """" & .ItemData(varItem) & ""","