We help IT Professionals succeed at work.

Filter records in a subform using listboxes on the main form

SteveL13
SteveL13 asked
on
High Priority
38 Views
Last Modified: 2020-03-26
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?
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
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
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.


Author

Commented:
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.
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
At the top of the module have:

Private Filter1 As String
Private Filter2 As String
Private Filter3 As String
Private Filter4 As String 
Next, remove Dim Filter1 As String from the function above.

Adjust the first three listboxes' code (the single code line in the function above) to:

Filter1 = Space(1) & Nz("IN (" + Items + ")", "Is Not Null")
Filter2 = Space(1) & Nz("IN ('" + Items + "')", "Is Not Null")
Filter3 = Space(1) & Nz("IN ('" + Items + "')", "Is Not Null")
The last with the dates takes a little more:

Private Sub ListAccessories_Click()

    Dim Items   As Variant
    Dim Item    As Variant
   
    Items = Null
    For Each Item In Me!ListAccessories.ItemsSelected
        Items = (Items + "#,#") & Format(DateValue(Me!ListAccessories.ItemData(Item)), "yyyy\/mm\/dd")
    Next
    Filter4 = Space(1) & Nz("IN (#" + Items + "#)", "Is Not Null")
   
    ' Example:
    Debug.Print "Where SomeField" & Filter4

End Sub
Then have, say, a button to click to set the filter:

Dim Filter As String
Dim RecordSource As String

Filter = " Where Field1" & Filter1 & " And Field2" & Filter2 & " And Field3" & Filter3 & " And Field4" & Filter4 & ""
RecordSource = "Select * From YourChildTable " & Filter

Me!NameOfYourSubformControl.Form.RecordSource = RecordSource