Tip: The IN Crowd |
  
|
Tip: The IN Crowd |
|
You've given your users a multi-select box and they've selected the items they want. This code turns their selection into a SQL Where clause so you can retrieve the items they've asked for. The IN keyword works nicely here because it matches all the items in a list (such as IN("item1", "item2", . . .)). The code creates the necessary input by walking the ItemsSelected collection of the list box, tacking every element in the property onto a string, and putting quotes around it:
Function BuildIn(lstItems As Control, _
strFieldName As String) As String
Dim varItem As Variant
Dim strOut As String
Const conQuote = """"
If lstItems.ItemsSelected.Count = 0 Then
BuildIn = ""
Else
For Each varItem In lstItems.ItemsSelected
strOut = strOut & "," & conQuote & _
lstItems.ItemData(varItem) & conQuote
Next varItem
' Get rid of leading comma.
BuildIn = " WHERE " & strFieldName & _
" IN (" & Mid$(strOut, 2) & ")"
End If
End Function
Then, calling this function and passing in a reference to the list box, it should return a string containing something like "WHERE CustomerID IN ("Item1","Item2","Item3")". You could tag this onto your SQL like this (assuming that strSQL already contains something like "SELECT * FROM tblCustomers"):
strSQL = strSQL & _
BuildIn(Me!lstCustomerIDS, "CustomerID")
Now strSQL will contain "SELECT * FROM tblCustomers WHERE CustomerID IN ("Item1","Item2","item3")".
If your items in the list box contain quotes (that is, the double-quote character), this will fail, and you'll need to find some other way to delimit the strings from the list box.
See More On Listboxes
