Results 1 to 15 of 15
  1. #1
    stildawn is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Oct 2013
    Posts
    185

    Using multiple listboxes (multiselect) to filter a query

    Hi All

    Still working away on this project and had a change come through.

    I have the attached currently, the form has three Comboboxes which gets passed through to 3 queries which are then used to populate the large listbox on the form.

    I need to change the comboboxes to multiselect little select boxes, so the users can select multiple items from all three and have that passed to the queries.

    I have tried a bunch of things myself but nothing has worked as yet, any ideas on how to make this work?

    The queries (Form_Listbox_Query + its 2 variants) can show a little funky in design view, but look fine in SQL view.

    Thanks
    Attached Files Attached Files

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    The only way a combobox can be multi-select is when it is bound to a multi-value field. I advise NOT to use multi-value field.

    A listbox can be multi-select. Review http://allenbrowne.com/ser-50.html
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    moke123's Avatar
    moke123 is online now Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    Sorry but couldn't make heads or tails of your query.

    I added 3 multi-select listboxes with 3 textboxes below them. I included a public function I use for Multi select Listboxes.
    You'll see that when you select an item in the list, it is added to the textbox below it(delimited for text as that appears to be what your using)
    You can then use an "In" clause to construct your query. --- ie. "Select * from sometable where somefield in (" & getLBX(YourListName, , , """") & ")"

    HTH
    Attached Files Attached Files

  4. #4
    stildawn is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Oct 2013
    Posts
    185
    Quote Originally Posted by moke123 View Post
    Sorry but couldn't make heads or tails of your query.

    I added 3 multi-select listboxes with 3 textboxes below them. I included a public function I use for Multi select Listboxes.
    You'll see that when you select an item in the list, it is added to the textbox below it(delimited for text as that appears to be what your using)
    You can then use an "In" clause to construct your query. --- ie. "Select * from sometable where somefield in (" & getLBX(YourListName, , , """") & ")"

    HTH
    Your code looks promising, my issue is passing the result into the query.

    The query SQL is this:

    Code:
    SELECT DISTINCT Master_Data2.ID, Master_Data2.[POL Name], Master_Data2.[POD Name], Master_Data2.Carrier, Master_Data2.[Contract Type], Master_Data2.Contract, Master_Data2.[20GP All In], Master_Data2.[40GP All In], Master_Data2.[40HC All In], Master_Data2.[Valid From], Master_Data2.[Valid To], Master_Data2.TransitFROM FRT_Table INNER JOIN Master_Data2 ON FRT_Table.ID = Master_Data2.ID
    WHERE (((Master_Data2.[POL Name])=Forms![Test Form]!POLCombo Or Forms![Test Form]!POLCombo Is Null) And ((Master_Data2.[POD Name])=Forms![Test Form]!PODCombo Or Forms![Test Form]!PODCombo Is Null) And ((Master_Data2.Carrier)=Forms![Test Form]!CarrierCombo Or Forms![Test Form]!CarrierCombo Is Null) And ((Master_Data2.[Valid To])>=Date()))
    ORDER BY Master_Data2.[Valid From], Master_Data2.[Valid To];
    If I use your listboxes, but instead of putting the result into another textbox, if I put your listbox getLBX into the original Comboboxes instead (POLCombo for example) that way the user has the option to use the combo box for one, or use the listbox for multiple.

    So these parts of the query SQL currently work with the single result in the comboboxes:

    Code:
    WHERE (((Master_Data2.[POL Name])=Forms![Test Form]!POLCombo Or Forms![Test Form]!POLCombo Is Null)
    And ((Master_Data2.[POD Name])=Forms![Test Form]!PODCombo Or Forms![Test Form]!PODCombo Is Null)
    And ((Master_Data2.Carrier)=Forms![Test Form]!CarrierCombo Or Forms![Test Form]!CarrierCombo Is Null)
    But I assume to make it work with the listbox results I need to change these, how do I do that on the fly?

    Whatever is passed into these places in the query, it needs to accept one result (like it currently does from comboboxes), multiple results (from your listboxes results) and also work if left blank (the is null part above).

    Thanks

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    You want to use the ItemsSelected property to loop over the ItemsSelected collection when using a listbox. Look here, with the notion that you would not Debug.Print as shown - you'd add (concatenate) each item and a comma to a string that you build as criteria. At the end of the loop, you'd add any sorting clause to that, then add the whole result to the beginning of your sql statement and then do something with it (i.e. open a recordset, make it the form recordsource or whatever else). There are tons of examples on how to build a query with listbox items.

    PS - you usually have to drop the trailing comma.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    moke123's Avatar
    moke123 is online now Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    First a couple things.
    Use option explicit in your declarations in every module. I added it in the attached but didn't correct any of your code that wasn't dimensioned.
    Eliminate any spaces in your object and fieldnames.

    That said,
    I'm not really sure what data you want returned as your query didnt quite click with me.

    In the attached I dynamically create the query in the forms module and set it to the rowsource of your listbox8.
    Its simplified because I'm not sure what you want returned but it should show you how to build the SQL. Look in the forms module.

    See if it makes sense and post back.
    Attached Files Attached Files

  7. #7
    stildawn is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Oct 2013
    Posts
    185
    Quote Originally Posted by moke123 View Post
    First a couple things.
    Use option explicit in your declarations in every module. I added it in the attached but didn't correct any of your code that wasn't dimensioned.
    Eliminate any spaces in your object and fieldnames.

    That said,
    I'm not really sure what data you want returned as your query didnt quite click with me.

    In the attached I dynamically create the query in the forms module and set it to the rowsource of your listbox8.
    Its simplified because I'm not sure what you want returned but it should show you how to build the SQL. Look in the forms module.

    See if it makes sense and post back.
    So from what I can see you dont use the original query at all? You build a new one each time from FRT_Table?

    Is there no way to pass the getLBX(Me.lstPOLNAME, , , """") directly into the current queries? Its because there are 3 different queries already set that do different things in the listbox based on what the user puts in various places.

    I'll try make your code work to show the correct data (pulling from Master_Data2 instead of FRT_Table), I think I'll need to do the code three times to replicate the three different queries.

  8. #8
    stildawn is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Oct 2013
    Posts
    185
    Well it didnt work as planned, doesnt work at all

    Code:
    Private Sub SetListRS()Dim strRS As String
    Dim strWhere As String
    Dim rs As String
    Dim strOrder As String
    
    
    strOrder = " ORDER BY Master_Data2.[Valid From], Master_Data2.[Valid To]"
    strRS = "SELECT DISTINCT Master_Data2.ID, Master_Data2.[POL Name], Master_Data2.[POD Name], Master_Data2.Carrier, Master_Data2.[Contract Type], Master_Data2.Contract, Master_Data2.[20GP All In], Master_Data2.[40GP All In], Master_Data2.[40HC All In], Master_Data2.[Valid From], Master_Data2.[Valid To], Master_Data2.Transit FROM FRT_Table INNER JOIN Master_Data2 ON FRT_Table.ID = Master_Data2.ID"
    strWhere = ""
    
    
    If Me.lstPOLNAME.ItemsSelected.Count <> 0 Then
    strWhere = strWhere & " And [POL Name] in(" & getLBX(Me.lstPOLNAME, , , """") & ")"
    End If
    
    
    If Me.lstPODName.ItemsSelected.Count <> 0 Then
    strWhere = strWhere & " And [POD Name] in(" & getLBX(Me.lstPODName, , , """") & ")"
    End If
    
    
    If Me.lstCarrier.ItemsSelected.Count <> 0 Then
    strWhere = strWhere & " And [Carrier] in(" & getLBX(Me.lstCarrier, , , """") & ")"
    End If
    
    
    'remove the leading " And "
    If Len(strWhere) <> 0 Then
    strWhere = Right(strWhere, Len(strWhere) - 4)
    End If
    
    
    MsgBox strRS & " Where " & strWhere & strOrder
    Me.List8.RowSource = strRS & " Where " & strWhere & strOrder
    Changed in bold, just added the correct SELECT and ORDER stuff

  9. #9
    stildawn is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Oct 2013
    Posts
    185
    Got it working:

    Code:
    Private Sub SetListRS()Dim strRS As String
    Dim strWhere As String
    Dim rs As String
    Dim strOrder As String
    
    
    strOrder = " ORDER BY Master_Data2.[Valid From], Master_Data2.[Valid To]"
    strRS = "SELECT DISTINCT Master_Data2.ID, Master_Data2.[POL Name], Master_Data2.[POD Name], Master_Data2.Carrier, Master_Data2.[Contract Type], Master_Data2.Contract, Master_Data2.[20GP All In], Master_Data2.[40GP All In], Master_Data2.[40HC All In], Master_Data2.[Valid From], Master_Data2.[Valid To], Master_Data2.Transit FROM FRT_Table INNER JOIN Master_Data2 ON FRT_Table.ID = Master_Data2.ID"
    strWhere = ""
    
    
    If Me.lstPOLNAME.ItemsSelected.Count <> 0 Then
    strWhere = strWhere & " And Master_Data2.[POL Name] in(" & getLBX(Me.lstPOLNAME, , , """") & ")"
    End If
    
    
    If Me.lstPODName.ItemsSelected.Count <> 0 Then
    strWhere = strWhere & " And Master_Data2.[POD Name] in(" & getLBX(Me.lstPODName, , , """") & ")"
    End If
    
    
    If Me.lstCarrier.ItemsSelected.Count <> 0 Then
    strWhere = strWhere & " And Master_Data2.Carrier in(" & getLBX(Me.lstCarrier, , , """") & ")"
    End If
    
    
    'remove the leading " And "
    If Len(strWhere) <> 0 Then
    strWhere = Right(strWhere, Len(strWhere) - 4)
    End If
    
    
    MsgBox strRS & " Where " & strWhere & strOrder
    Me.List8.RowSource = strRS & " Where " & strWhere & strOrder

  10. #10
    stildawn is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Oct 2013
    Posts
    185
    Currently its as below, which incorporates the 2 other query variations:

    Code:
    Private Sub SetListRS()Dim strRS As String
    Dim strWhere As String
    Dim rs As String
    Dim strOrder As String
    
    
    strOrder = " ORDER BY Master_Data2.[Valid From], Master_Data2.[Valid To]"
    strRS = "SELECT DISTINCT Master_Data2.ID, Master_Data2.[POL Name], Master_Data2.[POD Name], Master_Data2.Carrier, Master_Data2.[Contract Type], Master_Data2.Contract, Master_Data2.[20GP All In], Master_Data2.[40GP All In], Master_Data2.[40HC All In], Master_Data2.[Valid From], Master_Data2.[Valid To], Master_Data2.Transit FROM FRT_Table INNER JOIN Master_Data2 ON FRT_Table.ID = Master_Data2.ID"
    strWhere = ""
    
    
    If Me.lstPOLNAME.ItemsSelected.Count <> 0 Then
        strWhere = strWhere & " And Master_Data2.[POL Name] in(" & getLBX(Me.lstPOLNAME, , , """") & ")"
    End If
    
    
    If Me.lstPODName.ItemsSelected.Count <> 0 Then
        strWhere = strWhere & " And Master_Data2.[POD Name] in(" & getLBX(Me.lstPODName, , , """") & ")"
    End If
    
    
    If Me.lstCarrier.ItemsSelected.Count <> 0 Then
        strWhere = strWhere & " And Master_Data2.Carrier in(" & getLBX(Me.lstCarrier, , , """") & ")"
    End If
    
    
    'remove the leading " And "
    If Len(strWhere) <> 0 Then
        strWhere = Right(strWhere, Len(strWhere) - 4)
    Else
        If ExpiredCheck.Value = True Then
            List8.RowSource = "Form_ListBox_OldDates_Query"
            Exit Sub
        End If
        
        If viewDate.Value <> "" Then
            List8.RowSource = "Form_ListBox_SingleDate_Query"
            Exit Sub
        End If
        
        List8.RowSource = "Form_ListBox_Query"
        Exit Sub
    End If
    
    
    If ExpiredCheck.Value = True Then
        Me.List8.RowSource = strRS & " Where " & strWhere & strOrder
        Exit Sub
    Else
    End If
    
    
    If viewDate.Value <> "" Then
        strWhere = strWhere & " And (Master_Data2.[Valid From])<=Forms![Test Form]!viewDate And (Master_Data2.[Valid To])>=Forms![Test Form]!viewDate"
        Me.List8.RowSource = strRS & " Where " & strWhere & strOrder
        Exit Sub
    Else
    End If
    
    
    strWhere = strWhere & " And ((Master_Data2.[Valid To])>=Date())"
    
    
    'MsgBox strRS & " Where " & strWhere & strOrder
    Me.List8.RowSource = strRS & " Where " & strWhere & strOrder
    Now I just have to get the old Comboboxes to work at the same time, but I think I have an idea on that anyway.

    If I just use the combobox.values as a place to put in the getLBX(Me.lstCarrier, , , """") parts, then in the above code replace it with something like this:

    Code:
    If Me.lstPOLNAME.ItemsSelected.Count <> 0 Then
        strWhere = strWhere & " And Master_Data2.[POL Name] in(" & [Forms]![Test Form]![POLCombo] & ")"
    End If
    Should that work?

  11. #11
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Could try and let us know.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  12. #12
    stildawn is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Oct 2013
    Posts
    185
    Hi All

    Think I have it working, full code as below if anyone wants to take a look:

    Code:
    Private Sub SetListRS()Dim strRS As String
    Dim strWhere As String
    Dim rs As String
    Dim strOrder As String
    
    
    strOrder = " ORDER BY Master_Data2.[Valid From], Master_Data2.[Valid To]"
    strRS = "SELECT DISTINCT Master_Data2.ID, Master_Data2.[POL Name], Master_Data2.[POD Name], Master_Data2.Carrier, Master_Data2.[Contract Type], Master_Data2.Contract, Master_Data2.[20GP All In], Master_Data2.[40GP All In], Master_Data2.[40HC All In], Master_Data2.[Valid From], Master_Data2.[Valid To], Master_Data2.Transit FROM FRT_Table INNER JOIN Master_Data2 ON FRT_Table.ID = Master_Data2.ID"
    strWhere = ""
    
    
    If POLCombo.Value <> "" Then
        strWhere = strWhere & " And Master_Data2.[POL Name] in(" & POLCombo.Value & ")"
    End If
    
    
    If PODCombo.Value <> "" Then
        strWhere = strWhere & " And Master_Data2.[POD Name] in(" & PODCombo.Value & ")"
    End If
    
    
    If CarrierCombo.Value <> "" Then
        strWhere = strWhere & " And Master_Data2.Carrier in(" & CarrierCombo.Value & ")"
    End If
    
    
    'remove the leading " And "
    If Len(strWhere) <> 0 Then
        strWhere = Right(strWhere, Len(strWhere) - 4)
    Else
        If ExpiredCheck.Value = True Then
            List8.RowSource = "Form_ListBox_OldDates_Query"
            Exit Sub
        End If
        
        If viewDate.Value <> "" Then
            List8.RowSource = "Form_ListBox_SingleDate_Query"
            Exit Sub
        End If
        
        List8.RowSource = "Form_ListBox_Query"
        Exit Sub
    End If
    
    
    If ExpiredCheck.Value = True Then
        Me.List8.RowSource = strRS & " Where " & strWhere & strOrder
        Exit Sub
    Else
    End If
    
    
    If viewDate.Value <> "" Then
        strWhere = strWhere & " And (Master_Data2.[Valid From])<=Forms![Test Form]!viewDate And (Master_Data2.[Valid To])>=Forms![Test Form]!viewDate"
        Me.List8.RowSource = strRS & " Where " & strWhere & strOrder
        Exit Sub
    Else
    End If
    
    
    strWhere = strWhere & " And ((Master_Data2.[Valid To])>=Date())"
    
    
    'MsgBox strRS & " Where " & strWhere & strOrder
    Me.List8.RowSource = strRS & " Where " & strWhere & strOrder
    
    
    
    
     
    End Sub
    Private Sub POLCombo_AfterUpdate()
    'getLBX(Me.lstPOLNAME, , , """")
    
    
    If IsNull(POLCombo.Value) = True Then
    Else
        POLCombo.Value = """" & POLCombo.Value & """"
        Call ClearLBX(Me.lstPOLNAME, , , """")
    End If
    
    
    SetListRS
    
    
    End Sub
    
    
    Private Sub PODCombo_AfterUpdate()
    'getLBX(Me.lstPODName, , , """")
    
    
    If IsNull(PODCombo.Value) = True Then
    Else
        PODCombo.Value = """" & PODCombo.Value & """"
        Call ClearLBX(Me.lstPODName, , , """")
    End If
    
    
    SetListRS
    
    
    End Sub
    
    
    Private Sub CarrierCombo_AfterUpdate()
    'getLBX(Me.lstCarrier, , , """")
    
    
    If IsNull(CarrierCombo.Value) = True Then
    Else
        CarrierCombo.Value = """" & CarrierCombo.Value & """"
        Call ClearLBX(Me.lstCarrier, , , """")
    End If
    
    
    SetListRS
    
    
    End Sub
    Private Sub lstPOLNAME_AfterUpdate()
    
    
    POLCombo.Value = getLBX(Me.lstPOLNAME, , , """")
    
    
    SetListRS
    
    
    End Sub
    Private Sub lstPODName_AfterUpdate()
    
    
    PODCombo.Value = getLBX(Me.lstPODName, , , """")
    
    
    SetListRS
    
    
    End Sub
    Private Sub lstCarrier_AfterUpdate()
    
    
    CarrierCombo.Value = getLBX(Me.lstCarrier, , , """")
    
    
    SetListRS
    
    
    End Sub
    So far it seems to do what I want it to do, so touch wood another hurdle overcome, thanks heaps for your help.

  13. #13
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    In case you're interested in 2 shortcuts:
    1) consider using table aliases such as in
    Code:
    strRS = "SELECT DISTINCT ID, [POL Name], [POD Name], Carrier, [Contract Type], Contract, [20GP All In], [40GP All In], [40HC All In], [Valid From], [Valid To], Transit FROM FRT_Table INNER JOIN Master_Data2 AS MD2 ON FRT_Table.ID = MD2.ID"
    2) several controls (certainly textboxes and combos) have .Value as the default property, so you don't need If Me.ComboName.Value =
    If Me.ComboName = is good enough.
    Last edited by Micron; 09-02-2020 at 06:45 PM. Reason: code correction
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  14. #14
    stildawn is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Oct 2013
    Posts
    185
    Quote Originally Posted by Micron View Post
    In case you're interested in 2 shortcuts:
    1) consider using table aliases such as in
    Code:
    strRS = "SELECT DISTINCT ID, [POL Name], [POD Name], Carrier, [Contract Type], Contract, [20GP All In], [40GP All In], [40HC All In], [Valid From], [Valid To], Transit FROM FRT_Table INNER JOIN Master_Data2 AS MD2 ON FRT_Table.ID = MD2.ID"
    2) several controls (certainly textboxes and combos) have .Value as the default property, so you don't need If Me.ComboName.Value =
    If Me.ComboName = is good enough.
    Thanks for the tips, I'm very rusty in access so didnt know what are defaulted to values etc lol.

  15. #15
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Well, that was dumb of me. In editing (find/replace) youR sql I forgot to use the alias! I meant more like
    SELECT DISTINCT MD2.ID, MD2.[POL Name], MD2.[POD Name], ... INNER JOIN Master_Data2 AS MD2...

    but often you can get away without repeating the table name anyway, as in
    SELECT DISTINCT ID, [POL Name], [POD Name], ... FROM Master_Data2 ...
    but my familiarity with that is primarily where only one table is involved. I should have just posted the default hint and left it at that. I'm confusing both of us.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 07-02-2018, 10:38 PM
  2. Replies: 4
    Last Post: 03-27-2018, 12:30 PM
  3. Replies: 35
    Last Post: 03-19-2018, 12:06 AM
  4. Multiselect Multiple Listboxes
    By Deepak.Doddagoudar in forum Forms
    Replies: 29
    Last Post: 03-15-2018, 03:26 PM
  5. Replies: 5
    Last Post: 02-05-2013, 01:18 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums