Multi Select List Box only filters on one selection at a time?

[d9pierce1]

Database3.zip

Hi all,
I attached a sample db for reference.



I am trying to filter my list boxes with selections in them but it will only populate with one selection.
Is there a way if I select more then one then the next gets the value of all I selected from the other?

Basically What I select in Lst1 I want it to filter Lst2 & Lst3 and what I select in Lst2 to filter Lst3
Depending on how many i select in each listbox? If that makes any sense.

Thanks
Dave

[Welshgasman]

You likely need to use the IN() statement if more than one value.

[kd2017]

Check this out: http://allenbrowne.com/ser-50.html
Basically you just want to make sure multi select is enabled for the listbox then when you run your code you will loop through all the items in the listbox and build a string with values to filter by

[d9pierce1]

kd2017
I use that for one of my reports all ready but not really sure how that would work with cascading list boxes?

[d9pierce1]

Hi all,
welshgasman,
I am totally unfamiliar with the IN() Statement, will need to ready up on that one.
I use the allen brown example but the key work is "how to use a mutli select" A being the key word!
I am trying to cascade three multiselect listboxes
Thanks

[moke123]

As pointed out, you'll need to use an "In" clause.

such as (in red)

Dim sCategorySub As String


    sCategorySub = "SELECT [tblCategorySub].[SubCategoryID]," & _
                   " [tblCategorySub].[CategoryID]," & _
                   " [tblCategorySub].[SubCategoryName] " & _
                     "FROM tblCategorySub " & _
                     "WHERE [CategoryID] In(" & getLBX(Me.Lst1) & ")"
    Me.Lst2.RowSource = sCategorySub

I use a public function with Multi-select listboxes which returns a delimited string of your selections.

Public Function getLBX(lbx As ListBox, Optional intColumn As Variant = 0, Optional Seperator As String = ",", _
                       Optional delim As Variant = Null) As String


'Iterates thru the multiselect listbox and constructs an array of the selected items
'Arguments:
'Lbx is Listbox Object ie.Me.MyListbox
'intColumn is the column # to be returned
'Seperator is the character seperating items in array returned
'Delim is optional delimiter to be return in array ie. #1/1/2001#,#12/25/2015#


    Dim strlist As String
    Dim varSelected As Variant


    'On Error GoTo getLBX_Error


    If lbx.ItemsSelected.Count = 0 Then
        'MsgBox "Nothing selected"
    Else


        For Each varSelected In lbx.ItemsSelected


            If Nz(lbx.Column(intColumn, (varSelected)), "") <> "" Then


                strlist = strlist & delim & lbx.Column(intColumn, (varSelected)) & delim & Seperator


            Else


                strlist = strlist


            End If


        Next varSelected


        If Nz(strlist, "") <> "" Then


            strlist = Left$(strlist, Len(strlist) - 1)  'remove trailing comma


        End If
    End If


    getLBX = strlist


    On Error GoTo 0
    Exit Function


getLBX_Error:


    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure getLBX of Module modLBX"


End Function

[d9pierce1]

Thanks Moke123,
I put the code in my forms as suggested and put the function in a module. I am getting an error
"A Module Is Not a Valid Type"
Not sure what I need to do?
Thanks
Dave

[d9pierce1]

Hi Moke123
Thank you,
Didn't have the correct name in Module. Sorry for confusion!
Works like a charm!
Thank you very much!
Dave

[moke123]

Good luck with your project.