Multiple Combo boxes filtering Listbox (Revisited?)

Question

I'm attempting to filter a listbox based on several combo boxes. Seems pretty easy, right? In fact, I found pretty much the exact answer to my problem, however I can't seem to get it to work properly. (see: Multiple Combo Boxes to filter a listbox)

Using the code (modified for my purposes obviously) from the solution above doesn't seem to want to filter out anything specifically. Instead, it isn't finding any records in the query that match the filtering at all.

I have five Combo Boxes which grab unique values from a query (qryCustomerWants) and populate each of the five combo boxes based on the appropriate column in the query. When I click one of the combo boxes, the list box updates and is supposed to filter down the results based on the search criteria selected in the combo boxes.

Private Sub RequerylstCustomers()
   Dim SQL As String
   SQL = "SELECT qryCustomerWants.ID, qryCustomerWants.Type, qryCustomerWants.Make, qryCustomerWants.Model, qryCustomerWants.YearWanted, qryCustomerWants.Condition " _
    & "FROM qryCustomerWants " _
    & "WHERE 1=1 "
    If cboType.Value & "" <> "" Then
        SQL = SQL & " AND qryCustomerWants.Type = '" & cboType.Value & "'"
    End If
    If cboMake.Value & "" <> "" Then
        SQL = SQL & " AND qryCustomerWants.Make = '" & cboMake.Value & "'"
    End If
    If cboModel.Value & "" <> "" Then
        SQL = SQL & " AND qryCustomerWants.Model = '" & cboModel.Value & "'"
    End If
    If cboYear.Value & "" <> "" Then
        SQL = SQL & " AND qryCustomerWants.Year = '" & cboYear.Value & "'"
    End If
    If cboCondition.Value & "" <> "" Then
        SQL = SQL & " AND qryCustomerWants.Condition = '" & cboCondition.Value & "'"
    End If
    SQL = SQL & " ORDER BY qryContactWants.Last"
    Me.lstCustomers.RowSource = SQL
    Me.lstCustomers.Requery
End Sub

I call the function using:

Private Sub cboType_AfterUpdate()
    RequerylstCustomers
End Sub

Currently, each time I select an item from a combo box (any of them) it wipes the entire listbox clear. I know there are records that match the search parameters, so it should be filtering these down to a smaller list each combo box I select an entry from.

Where I am messing this up? thanks!

What types are the fields in the table? Looks like all of them are text fields? What's the original Row Source of the List Box? Try to use Debug.Print SQL at the end of RequerylstCustomers and use the printed result to run a query to see what's wrong. One more: In the select of the query there is YearWanted but you filter for Year... — Unhandled Exception
Here's the SQL output from Debugging: SELECT qryCustomerWants.ID, qryCustomerWants.Type, qryCustomerWants.Make, qryCustomerWants.Model, qryCustomerWants.YearWanted, qryCustomerWants.Condition FROM qryCustomerWants WHERE 1=1 AND qryCustomerWants.Type = 'Boat' ORDER BY qryContactWants.Last — BeardedSith
Thanks for pointing out the typo, I hadn't gotten to that part yet hah. Thanks anyway! — BeardedSith
When you create a new query with that SQL, does it run well? — Unhandled Exception
OMG. I am such a fail. The actual name of the Query is qryContactwants, not qryCustomerWants. But now I have another problem, and it's the line Me.lstCustomers.RowSource = SQL - I don't want the line to equal the SQL resutls, I want it to equal the customers name, just as it did originally. When I take that line out of the sub, the listbox doesn't update and lists all names instead of filtering them down. — BeardedSith

Unhandled Exception Unhandled Exception · Accepted Answer · 2019-04-09T15:48:26

I see right now, that your Order By uses qryContactWants and not qryCustomerWants.

I guess that's the reason for your problem.

Multiple Combo boxes filtering Listbox (Revisited?)

1 Answers