Query Criteria from form controls

[DavidCat]

I have a query where one of the fields criteria is set to the value a user selets from a listbox on a form, so the criteria is Forms![FormName]![ControlName].Value, which is fine when only one value will be selected. Now I have to change the form so the listbox can have multiple values selected, but what is the criteria I use for the query, as the above no longer works.

 

[JerryDennison]

You will have to build the criteria selection through code and assign it to another control(it can be hidden) then use this control as the criteria. This code will allow for building the WHERE clause of the DoCmd.OpenReport as well as assign it to a control on the form that can be used by a query. Basically, what it does is iterate through the choice made in the listbox and build a criteria using OR as the concatenator.

...
Dim sWhere As String ' Creates the WhereCondition for the Report
Dim RemoveOR As Integer ' Counts LEN of sWhere String for Removal of last OR statement
Const QUOTE = """"
sWhere = "[CriteriaField] = " & QUOTE
Me.txtBuildSQL = Null
' Print the list of selected items to the text
' box txtSelected.
Dim varItem As Variant
Dim strList As String
With lstCompanies
' varItem returns a row number.
For Each varItem In .ItemsSelected
sWhere = sWhere & .Column(1, varItem) & QUOTE & " OR [CriteriaField] = " & QUOTE
strList = strList & .Column(1, varItem) & vbCrLf
Next varItem
RemoveOR = Len(sWhere)
sWhere = Left(sWhere, (RemoveOR - 19))
Me.txtSelected = strList
Me.txtBuildSQL = sWhere
End With
...