views:

43

answers:

1

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

+3  A: 

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.

Remou
@Remou....hey will the first example only represent those selected like the second one does? thanks as always Remou!
Justin
@Remou....also if I use the second example, I am hiding the ID value from the list box with the column width set to zero....this really won't effect that though, right?
Justin
The width of the column is not important. There is only one example here, an SQL statement and how to get the IN list for that statement.
Remou
ohhhh...i see. thanks!
Justin