Access VBA How can I filter a recordset based on the selections in a multi select list box?

Question

I am trying to use the OpenForm function to filter based on the selections in a multi select list box. what is the correct syntax for this, or is there a better way to go about it? For the sake of example let's say:

List Box has options Ken, Mike, and Sandy.

Car has options Car1, Car2, and Car 3. All cars are owned by 1 or more people from that list box.

If someone from the list box is selected, I would like to open a form containing the cars owned by those people selected.

Thank you!

Answer 1

Ok So I figured out a way to do it:

1. Create a string to hold a query
2. Use a For loop to populate the string based on each item selected
3. Put that string as a filter in the OpenForm command.

Here is the specific code I used to to it. My example in the original post used Cars and People, but my actual context is different: Estimators and Division of Work are the filters. Let me know if you have any questions about it if you're someone who has the same question! Since it might be confusing without knowing more about what exactly I'm trying to accomplish.

Dim strQuery As String
Dim varItem As Variant
'query filtering for estimators and division list box selections
strQuery = ""
If Me.EstimatorList.ItemsSelected.Count + Me.DivisionList.ItemsSelected.Count > 0 Then
    For Each varItem In Me.EstimatorList.ItemsSelected
        strQuery = strQuery + "[EstimatorID]=" & varItem + 1 & " OR "
    Next varItem
    If Me.EstimatorList.ItemsSelected.Count > 0 And Me.DivisionList.ItemsSelected.Count > 0 Then
        strQuery = Left(strQuery, Len(strQuery) - 4)
        strQuery = strQuery + " AND "
    End If
    For Each varItem In Me.DivisionList.ItemsSelected
        strQuery = strQuery + "[DivisionID]=" & varItem + 1 & " OR "
    Next varItem
    strQuery = Left(strQuery, Len(strQuery) - 4)
End If

Answer 2

Using the JOIN function for cleaner and safer code

When you find yourself repeatedly building incremental SQL strings with delimiters like "," "AND" "OR" it is convenient to centralize the production of array data and then use the VBA Join(array, delimiter) function.

If the interesting keys are in an array, a user selection from a multiselect listbox to build a SQL WHERE fragment for the form filter property could look like this:

Private Sub lbYear_AfterUpdate()
    Dim strFilter As String

    Dim selction As Variant
    selction = ListboxSelectionArray(lbYear, lbYear.BoundColumn)

    If Not IsEmpty(selction) Then
        strFilter = "[Year] IN (" & Join(selction, ",") & ")"
    End If

    Me.Filter = strFilter
    If Not Me.FilterOn Then Me.FilterOn = True 
End Sub

A generic function to pick any column data from selected lisbok rows may look like this:

'Returns array of single column data of selected listbox rows
'Column index 1..n
'If no items selected array will be vbEmpty
Function ListboxSelectionArray(lisbox As ListBox, Optional columnindex As Integer = 1) As Variant
    With lisbox
        If .ItemsSelected.Count > 0 Then
            Dim str() As String: ReDim str(.ItemsSelected.Count - 1)
            Dim j As Integer
            For j = 0 To .ItemsSelected.Count - 1
                str(j) = CStr(.Column(columnindex - 1, .ItemsSelected(j)))
            Next
            ListboxSelectionArray = str
        Else
            ListboxSelectionArray = vbEmpty
        End If
    End With
End Function

A few array builders in the application library and coding can be made look more VB.NET