Making A List Box With Multiple Options


This code allows you to use a multi-select list box   list box with multiple options) instead of a combo box on a search form front end to a query or table.  It  loops through the selected values and builds a string called “criteria” with the selected values all in it as ” OR ” options.   The example result would look like “(“OPEN” or “CLOSED” or “IN PROGRESS”)

Then it merges that string with the existing strwhere for the other search criteria picked and adds the ” and ” to it per normal.

That way people can search for more than one option from a field at a time in conjunction with other fields.

An example of the code behind ONE listbox field (status list box instead of status dropdown)


If Not IsNull(Me![cbostatus].ItemsSelected) Then                   

 ‘ Build criteria string from selected items in list box.
  criteria = “(”

  For Each i In Me![cbostatus].ItemsSelected
If criteria <> “(” Then
criteria = criteria & ” OR ”
End If
criteria = criteria & “([Project Status]= “”” & Me![cbostatus].ItemData(i) & “””)”
Next i

   If criteria = “(” Then
criteria = “”
    strWhere = strWhere
strWhere = criteria & “) AND ”
End If
End If

Related Posts

Access 2016 Relationships

Short blog today, this is a great read on relationships in Access 2016: Access 2016 Relationships Thanks to Microsoft for this one!

Microsoft Access Union Query from Hell. Skill Level: Demi-God

The worst query I ever had to write which powers a master report for the database user.  Here it is.  I don't think I can make it smaller, there were too many weird rules, aggregates, and translations in it.  And we needed to pull different columns based on criteria. ...