CODEWITHSUNDEEP
sqlms-accessfilter
BeardedSith
BeardedSith

Reputation: 129

Multiple Combo boxes filtering Listbox (Revisited?)

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!

Upvotes: 0

Views: 135

Answers (1)

AHeyne
AHeyne

Reputation: 3455

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

I guess that's the reason for your problem.

Upvotes: 1

Related Questions