Using

[jsbarroso]

I am using a simple form to set filter options to a query. One of my fields is a [FONT=&quot]Multi Selection list box [/FONT][FONT=&quot]which users can select 1 or more options. The [/FONT][FONT=&quot]selected items in the multiple selection list box is stored in a comma-delimited string programmatically called “Selections” which, is included as part of the query filter as “In ([Forms]![View Reports]![Selections])”. The following code works great if users only pick 1 selection on the list box. Nothing works when multiple values are selected. :banghead:[/FONT]

Any help would be greatly appreciated.

Thank you,

Option Compare Database
Option Explicit

Private Sub Form_Current()
    Dim oItem As Variant
    Dim bFound As Boolean
    Dim sTemp As String
    Dim sValue As String
    Dim sChar As String
    Dim iCount As Integer
    Dim iListItemsCount As Integer
    
    sTemp = Nz(Me!Selections.Value, " ")
    iListItemsCount = 0
    bFound = False
    iCount = 0

    Call clearListBox
        
    For iCount = 1 To Len(sTemp) + 1
    sChar = Mid(sTemp, iCount, 1)
        If StrComp(sChar, " , ") = 0 Or iCount = Len(sTemp) + 1 Then
            bFound = False
            Do
                If StrComp(Trim(Me!AccruList.ItemData(iListItemsCount)), Trim(sValue)) = 0 Then
                    Me!AccruList.Selected(iListItemsCount) = True
                    bFound = True
                End If
                iListItemsCount = iListItemsCount + 1
            Loop Until bFound = True Or iListItemsCount = Me!AccruList.ListCount
            sValue = ""
        Else
            sValue = sValue & sChar
        End If
    Next iCount
End Sub
    
Private Sub clearListBox()
    Dim iCount As Integer
        
    For iCount = 0 To Me!AccruList.ListCount
        Me!AccruList.Selected(iCount) = False
    Next iCount
End Sub

Private Sub multiselect_Click()
    Dim oItem As Variant
    Dim sTemp As String
    Dim iCount As Integer
    
    iCount = 0
            
    If Me!AccruList.ItemsSelected.Count <> 0 Then
        For Each oItem In Me!AccruList.ItemsSelected
            If iCount = 0 Then
                sTemp = sTemp & Me!AccruList.ItemData(oItem)
                iCount = iCount + 1
            Else
                sTemp = sTemp & " , " & Me!AccruList.ItemData(oItem)
                iCount = iCount + 1
            End If
        Next oItem
    Else
        MsgBox "Nothing was selected from the list", vbInformation
        Exit Sub  'Nothing was selected
    End If
    
    Me!Selections.Value = sTemp
End Sub
    
Private Sub clrList_Click()
    Call clearListBox
    Me!mySelections.Value = Null
End Sub

 

[MarkK]

What I would be much more prone to do is get consumers to "pull" in the data they need, when they need it. Your code seems to "push" values out instead, so it appears that data gets assigned, and processes get run, regardless of whether consuming code requests this data. Like, you don't need to handle every click on the list, and rebuild your "selections."

Rather, consider a clearly named function like this to return a "|" delimited list of bound column values from a multi-select listbox . . .

Function GetSelected() As String
    Dim var
    Dim tmp As String
    For Each var In Me.List0.ItemsSelected
        tmp = tmp & "|" & Me.List0.ItemData(var)
    Next
    If tmp <> "" Then GetSelected = Mid(tmp, 2)
End Function

So don't run anything on Current, and don't run anything on Click. Wait until a consumer needs that data, and get that consumer to call the above function, and only calculate that result on demand.

Hope that helps,