How to use Selected Items from ListBox as query criteria

Problem:
You have a multi-select ListBox on a form and you want to get all Fields from a table or query that match your Selection in the listbox.

Solution:

If you want to initiate the field search with e.g. a Command button, adapt the following code to your needs:


Sub YourSearchButton_Click()

Dim ctlList
Dim sSql as String
[blue]
Set ctlList = Me.YourListbox
'*******************************
sSQL="SELECT * FROM YourTable Where "

For Each Lmnt In ctlList.itemsSelected
sSQL=sSQL & "Surname= '" & ctlList.Itemdata(Lmnt) & "' OR "
Next

sSQL=Left(sSQL,Len(sSQL)-3) 'Remove last 'OR' with space
'********************************
DoCmd.RunSQL sSQL
[/blue]

Variation of this code is:
[blue]
'********************************
sSQL="SELECT * FROM YourTable WHERE Surname IN ('"

For Each Lmnt In ctlList.itemsSelected
sSQL=sSQL & ctlList.Itemdata(Lmnt) & "', '"
Next

sSQL=Left(sSQL,Len(sSQL)-3) & ")" 'Remove Last comma and single quote and add closing bracket
'*********************************
[/blue]
This code is of course valid for UPDATE, INSERT INTO or DELETE statements too, and only needs to be adapted respectively.

For those who need more complex statements:
[blue]Create a new query in query designer, switch to SQL view, copy/paste the statement into your code, make adaptions (esp. you might want to remove Forms![YourForm]! and set " & me!Whatever & "), where necessary.[/blue]

Special thanks to [blue]PeteJohnston[/blue] for the code variation. ;-)

[green]MakeItSo[/green]