4 - 6 List boxes in one Search Form?

[Minnesota]

I have between 4 and 6 multi-select list boxes in a search form. I would like to have the user be able to select one or 2 options from each list box, then click serach and get the results they selected.

Here is the code I'm modeling mine around. I had it working with just one list box and 2 text boxes, but once I added the 2nd and 3rd, and so on, list boxes it gives an error.

Obviously I'm not very knowledgeable about VBA, so I would greatly appreciate if somebody could show me how to change the below code from a single list box to all multiple. My attempt failed.

Private Function BuildFilter() As Variant
    Dim varWhere As Variant
    Dim varColor As Variant
    Dim varItem As Variant
    Dim intIndex As Integer
    varWhere = Null  ' Main filter
    varColor = Null  ' Subfilter used for colors
 
    ' Check for LIKE First Name
    If Me.txtFirstName > "" Then
        varWhere = varWhere & "[FirstName] LIKE """ & Me.txtFirstName & "*"" AND "
    End If
 
    ' Check for LIKE Last Name
    If Me.txtLastName > "" Then
        varWhere = varWhere & "[LastName] LIKE """ & Me.txtLastName & "*"" AND "
    End If
 
    ' Check for min Age
    If Me.txtMinAge > "" Then
        varWhere = varWhere & "[Age] > " & Me.txtMinAge & " AND "
    End If
 
    ' Check for max Age
    If Me.txtMaxAge > "" Then
        varWhere = varWhere & "[Age] < " & Me.txtMaxAge & " AND "
    End If
 
    ' Check for CompanyID
    If Me.cmbCompany > 0 Then
        varWhere = varWhere & "[CompanyID] = " & Me.cmbCompany & " AND "
    End If
 
    ' Check for CountryID
    If Me.cmbCountry > 0 Then
        varWhere = varWhere & "[CountryID] = " & Me.cmbCountry & " AND "
    End If
 
    ' Check for Colors in multiselect list
    For Each varItem In Me.lstFavColor.ItemsSelected
        varColor = varColor & "[FavColor] = """ & _
                    Me.lstFavColor.ItemData(varItem) & """ OR "
 
    Next
 
    ' Test to see if we have subfilter for colors...
    If IsNull(varColor) Then
        ' do nothing
    Else
        ' strip off last "OR" in the filter
        If Right(varColor, 4) = " OR " Then
            varColor = Left(varColor, Len(varColor) - 4)
        End If
 
        ' Add some parentheses around the subfilter
        varWhere = varWhere & "( " & varColor & " )"
    End If
 
    ' Check if there is a filter to return...
    If IsNull(varWhere) Then
        varWhere = ""
    Else
        varWhere = "WHERE " & varWhere
 
        ' strip off last "AND" in the filter
        If Right(varWhere, 5) = " AND " Then
            varWhere = Left(varWhere, Len(varWhere) - 5)
        End If
    End If
 
    BuildFilter = varWhere
 
End Function

I have also attached the sample database I've been using to "write" my code. The samples everybody provides here are a life saver!

 

[Mr. B]

Minnesota,

The problem here is that for each of the multi-select list boxes on your form, you will have to use a for loop to test for the selected items. As you are moving through the list of selected items in a list box, you will have to build the string that will be part of the selection criteria.

Here is a link that might help you get started:
http://www.mvps.org/access/forms/frm0007.htm

I would suggest that you start with only one of your lists and make that one work. Then add another list, making your sql statement work. Then continue this process until you have successfully added all of the lists and made the sql statement work.

This is not going to be a simple fix. You will have to try until you get the correct string built in the correct syntax and make it work correctly in the sql statement.