Filter listbox based on combo selection

[inkbird1]

Hi, I want to have a combo box which the user can filter down the available options in a multiselect list box.
I have tried to use the below code but doesn't work

Private Sub cbo_filterProvider_Click()

    Dim strRS As String
 
    ' Filter the list box appropriately based on the combo box selection(s)
    If Me.cbo_filterProvider.ListIndex >= 0 Then strRS = strRS & " AND ProviderID = " & Me.cbo_filterProvider
 
    'ADD FILTER TO QUERY
    strRS = "SELECT qry_studentsExtended.StudentID, qry_studentsExtended.StudentName FROM qry_studentsextended " & strRS
 
    'ADD ORDER BY CLAUSE
    strRS = strRS & " ORDER BY qry_studentsExtended.Last Name;"
 
    Me.lst_Students.RowSource = strRS
    Me.lst_Students.Requery

 

[welshgasman]

Debug.Print strRS at each modification.

 

[CraigYellick]

Far easier to alter the listbox's .RowSource to reference the combobox value. Access will handle the events for you. Something like:

  select * from qry_studentsextended where ProviderID = forms!theFormName!cbo_filterProvider

If you need to handle the "I don't want a filter condition" you can add an "ALL" choice to the combobox values and check for that. Assuming ALL's value is -1:

  select * from qry_studentsextended where ProviderID = forms!theFormName!cbo_filterProvider or forms!theFormName!cbo_filterProvider = -1

No code necessary.

-- Craig