Avatar of SteveL13
SteveL13
Flag for United States of America asked on

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

Avatar of undefined
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" & Filter1

End Sub

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:

"Select ... From ... Where Field1" & Filter1 & " And Field2" & Filter2 & " And Field3" & Filter3 & ""

Open in new window

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.
ASKER CERTIFIED SOLUTION
Gustav Brock

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Your help has saved me hundreds of hours of internet surfing.
fblack61