Filter records in a subform using listboxes on the main form
I have a form that has 3 multi-select listboxes on it. Is there a way to filter the records in the sub-form on the same form when the user has selected records in the listboxes?
Microsoft Access
Last Comment
Gustav Brock
8/22/2022 - Mon
Gustav Brock
Yes. Build a filter from each listbox like this example:
Private Sub ListAccessories_Click() Dim Items As Variant Dim Item As Variant ' NB: Make this a Private variable of the code module. Dim Filter1 As String Items = Null For Each Item In Me!ListAccessories.ItemsSelected Items = (Items + "','") & Me!ListAccessories.ItemData(Item) Next Filter1 = Space(1) & Nz("IN ('" + Items + "')", "Is Not Null") ' Example: Debug.Print "Where SomeField" & Filter1End Sub
Of course, if the values are numbers, leave out the single-quotes. Now, combine the filter string from the three listboxes and adjust the recordsource of the subform:
"Select ... From ... Where Field1" & Filter1 & " And Field2" & Filter2 & " And Field3" & Filter3 & ""
When setting the recordsource of the subform, it will requery.
SteveL13
ASKER
I'm a little confused. There is one numeric multi-select listbox, 2 text multi-select listboxes, and 1 date type multiselect listbox. I don't know how to put that code together.
Open in new window
Of course, if the values are numbers, leave out the single-quotes.Now, combine the filter string from the three listboxes and adjust the recordsource of the subform:
Open in new window
When setting the recordsource of the subform, it will requery.