Solved

ComboBox/ListBox with multi select Feature?

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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

912 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

20 Experts available now in Live!

Get 1:1 Help Now