vba - MS Access 2007 - Cycling through values in a list box to grab id's for a SQL statement

Question

Lets say I have two tables, one for transactions, and another table who's primary key is the foreign key in the first table, and this relationship simply associates locations to transactions.

I have a form with a list box that shows all the potential locations, so that the user can open some dashboard forms that only pertain to a given location. So I know how to pass the data from the selection to the dashboard, however I would now like the user to have the capability to select multiple locations from the first list.

so if I use a SQL statement the WHERE clause is like

 .... WHERE LocationID = " & me.lstLocations.value & ";"

but how would I equate this type of method to selecting multiple choices? I am sure there is some type of loop that escapes me.

Thanks Justin

Answer 1

You can use

WHERE LocationID IN (" & listofvalues & ");"

The list can be obtained like so:

For Each itm In Me.ListBox.ItemsSelected
  listofvalues = listofvalues & "," & Me.ListBox.Column(0, itm)
Next

listofvalues = Mid(listofvalues,2)

This is for a numeric list, a list of strings needs quotes.