Solved

ComboBox/ListBox with multi select Feature?

Posted on 2014-04-12
4
1,377 Views
Last Modified: 2014-04-12
Hello All,

i have been spending hours but not able to make this work. How do i or what attributes do i change on a control of list-box or combobox so that users are able to multi select  - multiple values from that control and store it in a global Var?

thank you
0
Comment
Question by:Rayne
  • 2
  • 2
4 Comments
 

Author Comment

by:Rayne
ID: 39996808
can someone explain
0
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 500 total points
ID: 39996817
You cannot make a combo box multi-select.

To make a listbox multi-select, set the Multi-Select property of the listbox to Simple or Extended.

Once you have done this, the list will no longer have a Value, so you cannot refer to the selected item with code that simply says:

Me.lst_ListName.Value

Instead, you will have to loop through the ItemsSelected collection to identify which items were selected.  
Dim varItem as Variant
Dim strItems as string

For each varItem in me.lst_ListName.ItemsSelected
    strItems = strItems & ", " & me.lst_ListName.column(0, varItem)
Next

Open in new window

However, I'm not sure how you would use that set of values in a global variable.  I use the following function to allow me to use a multi-select listbox as a filter parameter for various reports.  I might call this like:

strFilter = "[SomeField] " & fnMultiList(me.lst_ListName)

This would return the values in the bound column, separate by commas.  If the number of Items selected is zero, the function returns " IS NOT NULL".  If the bound column, or the column provided in the argument list is not numeric, it wraps the values in quotes or in the character(s) provided in the Delimeter argument.
Public Function fnMultiList(lst As ListBox, Optional SelectAll As Boolean = False, _
                            Optional UseColumn As Integer = -1, _
                            Optional Delimiter As Variant = Null) As Variant

    Dim varItem As Variant
    Dim lngItem As Long
    Dim strDelimiter As String
    
    fnMultiList = Null
    
    'Determine how to delimit the list items
    If UseColumn = -1 Then UseColumn = lst.BoundColumn - 1
    If IsNull(Delimiter) = False Then
        strDelimiter = Delimiter
    ElseIf IsNumeric(lst.Column(UseColumn, Abs(lst.ColumnHeads))) Then
        strDelimiter = ""
    Else
        strDelimiter = Chr$(34)
    End If
    
    'loop through the selected items in the list
    If lst.MultiSelect = 0 And SelectAll = False Then
        fnMultiList = lst.Value
    Else
        For lngItem = 0 To lst.ListCount
            If lst.Selected(lngItem) = True Or SelectAll Then
                fnMultiList = (fnMultiList + ",") _
                            & strDelimiter & lst.Column(UseColumn, lngItem) _
                            & strDelimiter
            End If
        Next lngItem
    End If
    
    'Strip trailing "," if there is one
    If Right(fnMultiList, 1) = "," Then fnMultiList = Left(fnMultiList, Len(fnMultiList) - 1)
    
    'Depending on the number of items selected, determine how the "list values" are returned
    If Len(fnMultiList & "") = 0 Then
        fnMultiList = " IS NOT NULL"
    Else
        Select Case Len(fnMultiList) - Len(Replace(fnMultiList, ",", ""))
            Case 0
                fnMultiList = " = " & fnMultiList
            Case Else
                fnMultiList = " IN (" & fnMultiList & ")"
        End Select
    End If
    
End Function

Open in new window

Hope this gives you a starting point for whatever it is you are trying to do.
0
 

Author Comment

by:Rayne
ID: 39996838
thank you Dale, thank you
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39996842
Glad to help.  Post back if you need some more help with that.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
In case Office 2010 has not been deployed in your environment, this article may be quite useful. In our office, we wanted a way to deploy Microsoft Office Professional Plus 2010 through an automated batch file via logon script. This article is docum…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now