Multi Selection on form

[mpaulbattle]

Is there a way to make my combo box multi selection if my row source is a query based on the table?

tblCredentials
CredentialID-Autonumber
Credentials-Text (Combo Box, Value List, Allow Multiple Values)

Form
My combo box is set as
Control Source-CredentialID
Row Source-

SELECT tblProvidersSpecialty.ProviderSpecialtyID_PK, tblProvidersSpecialty.ProviderSpecialty
FROM tblProvidersSpecialty;

Row Source Type-Table/Query

 

[isladogs]

Yes ... if you use the dreaded multivalue fields ... but that's a very bad idea in my view.

Instead use a multiselect listbox which is designed for the purpose.

 

[Insane_ai]

To expand on the response from Ridders:


I use these snippets to make life easier when working with Listboxes and retrieving values for use in a query:

Use the first one for anything that doesn't require being in quotes, use the second one to return a series of strings. I use it particularly when the return value is two or more words with a space in between.

Public Function GetListboxItems(ctrl As ListBox)
Dim i As Long
Dim sResult As String
    sResult = ""
    For i = 0 To ctrl.ListCount - 1
        If ctrl.Selected(i) Then
            sResult = sResult & "," & ctrl.ItemData(i)
        End If
    Next i
    If Left(sResult, 1) = "," Then sResult = Right(sResult, Len(sResult) - 1)
    GetListboxItems = sResult
End Function




Public Function GetListboxItemsStrings(ctrl As ListBox)
Dim i As Long
Dim sResult As String
    sResult = ""
    For i = 0 To ctrl.ListCount - 1
        If ctrl.Selected(i) Then
            sResult = sResult & ",'" & ctrl.ItemData(i) & "'"
        End If
    Next i
    If Left(sResult, 1) = "," Then sResult = Right(sResult, Len(sResult) - 1)
    GetListboxItemsStrings = sResult
End Function

Usage Samples:
sLstResult = GetListboxItems(Me.cbEmployee) >>>> Returns Integer


sLstResult = Nz(GetListboxItemsStrings(Me.lstMP), "ALL") >>>>Returns string values in single quotes to deal with spaces in the values. Eg: 'Marketing Department', 'Warehouse Returns', etc.


If you have the real estate on your form to use a listbox, I would suggest that method when you intend the user to choose more than one value at any time.

 

[moke123]

Like insane I also use a public function to return items from a multi select list box. Mine has several arguments that allow you to return any of the columns in the listbox, change the seperator character, and delimit the results as needed.

Public Function getLBX(Lbx As ListBox, Optional intColumn As Variant = 0, Optional Seperator As String = ",", _
                       Optional Delim As Variant = Null) As String

'Iterates thru the multiselect listbox and constructs a string of the selected items
'Arguments:
'Lbx is Listbox Object ie.Me.MyListbox
'intColumn is the column # to be returned
'Seperator is the character seperating items in string returned
'Delim is optional delimiter to be return in string ie. #1/1/2001#,#12/25/2015#

    Dim strList As String
    Dim varSelected As Variant

   On Error GoTo getLBX_Error

    If Lbx.ItemsSelected.Count = 0 Then
        'MsgBox "Nothing selected"
    Else

        For Each varSelected In Lbx.ItemsSelected

            strList = strList & Delim & Lbx.Column(intColumn, (varSelected)) & Delim & Seperator

        Next varSelected

        strList = Left$(strList, Len(strList) - 1)  'remove trailing comma

    End If

    getLBX = strList

   On Error GoTo 0
   Exit Function

getLBX_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure getLBX of Module modLBX"

End Function

Here's a sample I did for someone else recently on another forum. It has a few helpful procedures for dealing with Multi select list boxes.

 

[Insane_ai]

Like insane I also use a public function to return items from a multi select list box. Mine has several arguments that allow you to return any of the columns in the listbox, change the seperator character, and delimit the results as needed.

Public Function getLBX(Lbx As ListBox, Optional intColumn As Variant = 0, Optional Seperator As String = ",", _
                       Optional Delim As Variant = Null) As String

'Iterates thru the multiselect listbox and constructs a string of the selected items
'Arguments:
'Lbx is Listbox Object ie.Me.MyListbox
'intColumn is the column # to be returned
'Seperator is the character seperating items in string returned
'Delim is optional delimiter to be return in string ie. #1/1/2001#,#12/25/2015#

    Dim strList As String
    Dim varSelected As Variant

   On Error GoTo getLBX_Error

    If Lbx.ItemsSelected.Count = 0 Then
        'MsgBox "Nothing selected"
    Else

        For Each varSelected In Lbx.ItemsSelected

            strList = strList & Delim & Lbx.Column(intColumn, (varSelected)) & Delim & Seperator

        Next varSelected

        strList = Left$(strList, Len(strList) - 1)  'remove trailing comma

    End If

    getLBX = strList

   On Error GoTo 0
   Exit Function

getLBX_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure getLBX of Module modLBX"

End Function

Here's a sample I did for someone else recently on another forum. It has a few helpful procedures for dealing with Multi select list boxes.

++Thanks, I'm taking this.