Making WHERE statement from multiselect ListBox

[John S. Ford, MD]

I have a ListBox with its "Multiselect" property turned on. I want to use
the choices to create a WHERE statement for a separate form that uses the
same underlying datasource as the ListBox.

How can I code this using Access 2000?

John

 

[Dirk Goldgar]

I have a ListBox with its "Multiselect" property turned on. I want to use
the choices to create a WHERE statement for a separate form that uses the
same underlying datasource as the ListBox.

How can I code this using Access 2000?

Usually you'll do this by looping through the list box's ItemsSelected
collection, cross-referencing that against the ItemData collection, and
building a comma-delimited list to be used with an In() predicate. Like
this:

'----- start of example code -----

Dim strCriteria As String
Dim strList As String
Dim varItem As Variant

With Me.lstMyListbox

Select Case .ItemsSelected.Count

Case 0
' Nothing selected, hence no criteria
strCriteria = ""

Case 1
' Only one item select, hence a simple "Where field ="
clause
strCriteria = "YourFieldName=" &
..ItemData(.ItemsSelected(0))

Case Else
' Build "Where file In()" clause
For Each varItem In .ItemsSelected
strList = strList & ", " & .ItemData(varItem)
Next varItem

strCriteria = "YourFieldName In(" & Mid(strList, 3) & ")"

End Select

End With

' Open form using the criteria string.
DoCmd.OpenForm "YourFormName", WhereCondition:=strCriteria

'----- end of example code -----

Note: the above code assumes that the field to be filtered on is numeric.
If it's text, then quotes have to be built into the criteria string
surrounding each selected field value.

Also note that this is untested air code.

 

[John S. Ford, MD]

Thanks Dirk. I'm going to study your code but it sounds like exactly what
I'm looking for.

John

 

[Ken Snell MVP]

This sample database contains working example of the code for multiselect
listboxes (as well as other controls, too):

Building SQL string based on values entered into controls
http://www.accessmvp.com/KDSnell/SampleDBs.htm#FilterForm

 

[John S. Ford, MD]

Thanks Ken. I'll check that out. I've actually been experimenting with
using multiselect ListBoxes as described in your book. You cite an example
that creates a string used to fill a TextBox based on the selection of a
ListBox. Now I'm trying to understand how to go from there to filtering the
underlying datasource of a form.

John

 

[Ken Snell MVP]

To filter the underlying data source of a form, easiest way is to build a
WHERE clause, then set the form's RecordSource property to the original
unfiltered SQL statement plus the WHERE clause, in order to filter the data.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/