Multiple Combo Boxes to filter a listbox

Question

I have a form with 2 combo boxes and 1 listbox. The listbox is populated when I search using a button and a text box. Each combo box independently filters the listbox, but I can not figure out how to get them to layer. i.e. if I have chosen selections in both combo boxes, I would like both of the filters to apply to the listbox.

I would like to do one of the following: a) Get the filters to layer dynamically based or b) Have the filters apply when the search button is clicked

My current layout is:

Combo Boxes: cboJob, cboCompany ListBox: lstResume Textbox for searching: txtKeywords Search Button: btnSearch data is drawn from qryResume All data is text

Current Code:

Private Sub btnSearch_Click()
Dim SQL As String
SQL = "SELECT qryResume.ID, qryResume.Company, qryResume.Job, qryResume.LastUpdated " _
    & "FROM qryResume " _
    & "Where Company LIKE '*" & Me.txtKeywords & "*' " _
    & " OR Job LIKE '*" & Me.txtKeywords & "*' " _
    & "ORDER BY qryResume.Company "
    Me.lstResume.RowSource = SQL
    Me.lstResume.Requery

End Sub

Private Sub cboCompany_AfterUpdate()
Dim SQL As String
SQL = "SELECT qryResume.ID, qryResume.Company, qryResume.Job, qryResume.LastUpdated " _
    & "FROM qryResume " _
    & "WHERE qryResume.Company = '" & cboCompany.Text & "'" _
    & "ORDER BY qryResume.Company"
    Me.lstResume.RowSource = SQL
    Me.lstResume.Requery

End Sub

Private Sub cboJob_AfterUpdate()
Dim SQL As String
SQL = "SELECT qryResume.ID, qryResume.Company, qryResume.Job, qryResume.LastUpdated " _
    & "FROM qryResume " _
    & "WHERE qryResume.Job = '" & cboJob.Text & "'" _
    & "ORDER BY qryResume.Company"
    Me.lstResume.RowSource = SQL
    Me.lstResume.Requery

End Sub

Erik A Erik A · Accepted Answer · 2018-03-07T16:37:47

You can refactor this code pretty easily to the following, if you could use the .Value property or .Column collection instead of .Text:

Private Sub RequerylstResume()
   Dim SQL As String
   SQL = "SELECT qryResume.ID, qryResume.Company, qryResume.Job, qryResume.LastUpdated " _
    & "FROM qryResume " _
    & "WHERE 1=1 "
    If cboJob.Value & "" <> "" Then
        SQL = SQL &  " AND qryResume.Job = '" & cboJob.Value & "'"
    End If
    If cboCompany.Value & "" <> "" Then
        SQL = SQL & " AND qryResume.Company = '" & cboCompany.Value & "'"
    End If
    If Me.TextKeyWords.Value & "" <> "" Then 
        SQL = SQL & " AND (Company LIKE '*" & Me.txtKeywords & "*' " _
        & " OR Job LIKE '*" & Me.txtKeywords.Value & "*') "
    End If
    SQL = SQL & " ORDER BY qryResume.Company" 
    Me.lstResume.RowSource = SQL
    Me.lstResume.Requery
End Sub

Then, whenever you want to execute a search, just call RequerylstResume.

You call a sub like this:

Private Sub cboJob_AfterUpdate()
    RequerylstResume
End Sub

And put the sub you want to call in the same module, outside of any other sub

Multiple Combo Boxes to filter a listbox

1 Answers