Solved

ComboBox/ListBox with multi select Feature?

Posted on 2014-04-12
4
1,412 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …

808 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