none
Combine dynamic listbox columns RRS feed

  • Question

  • Good Day~
    I have a form with two listboxes. Each list box is based on a query
    and changes values when selection is change. Only the first column is visible
    and there are 3 more columns I get values from to update a table. I need a vba
    code to get one listbox column values so I can put it in a sentence. If there is
    only one staff member I want to save one, if there is two I want to save want to
    put "and" between the first one and second one (John Doe and Jane Doe), or if
    there are 3 or more I want commas between and "and" at the end (John Doe, Jane
    Doe, and Sally Doe).
    Please help.
    Thank you in advance~mjc

    mjc

    Thursday, February 25, 2016 6:19 PM

Answers

  • Good Day All~

    Based on a database sample, I achieved my code:

    Private Sub SII_Click()
    On Error GoTo Err_Handler
    Dim ctlLB As Control
    Dim strAstCount As Integer
    Dim lngRow As Long
    Dim strFull As String
    Dim strFormal As String

     Set ctlLB = Me!LstUnwanted
     
            strAstCount = ctlLB.ItemsSelected.Count - 1
               
        If strAstCount = 0 Then
            strFull = ctlLB.ItemData(ctlLB.ItemsSelected(0))
            strFormal = ctlLB.ItemData(ctlLB.ItemsSelected(0))
        Else
            With Me.LstUnwanted
                For lngRow = 0 To .ListCount - 1
                    If .ListCount = 1 Then
                        strFull = .Column(0, lngRow)
                        strFormal = .Column(2, lngRow)
                    ElseIf .ListCount = 2 Then
                        If strFull = "" And strFormal = "" Then
                            strFull = strFull & .Column(0, lngRow)
                            strFormal = strFormal & .Column(2, lngRow)
                        Else
                            strFull = strFull & " and " & .Column(0, lngRow)
                            strFormal = strFormal & " and " & .Column(2, lngRow)
                        End If
                    Else
                        If strFull = "" And strFormal = "" Then
                            strFull = strFull & .Column(0, lngRow)
                            strFormal = strFormal & .Column(2, lngRow)
                        Else
                            If lngRow + 1 < .ListCount Then
                                strFull = strFull & ", " & .Column(0, lngRow)
                                strFormal = strFormal & ", " & .Column(2, lngRow)
                            Else
                                strFull = strFull & " and " & .Column(0, lngRow)
                                strFormal = strFormal & " and " & .Column(2, lngRow)
                            End If
                        End If
                    End If
                Next lngRow
            End With
        End If
       
     
     Me!Assit = strFull
     Me!AAEIOASFormal = strFormal

    End Sub

    Thanks so much for all of the assistance~mjc


    mjc

    • Marked as answer by MJFan23 Monday, February 29, 2016 1:44 PM
    Monday, February 29, 2016 1:44 PM

All replies

  • Hi mjc,

    Not sure I follow... Are you saying you want to put the "and" between the columns or list items?

    Thursday, February 25, 2016 6:54 PM
  • The selected people in one listbox need to be saved in a form field but I have one column that has there full name and on that has a prefix and last name.  I need to save the full name column, all values and if there is more than 3 then all with commas and the last two with and separating them.  I need to do it for the formal name also.

    This is what I have so far:

    Dim ctlLB As Control
    Dim strAstCount As Integer
    Dim varSelected As Variant
    Dim intRow As Integer
    'Dim intCol0 As Variant
    'Dim intCol2 As Integer
    Dim strFull As String
    Dim strFormal As String
    Dim strEmail As String

     'Set ctlLB = Me!LstUnwanted 'qrySWIOAssist
           
            'strAstCount = ctlLB.ItemsSelected.Count - 1
               
        'If strAstCount = 0 Then
            'strFull = ctlLB.ItemData(ctlLB.ItemsSelected(0))
        'Else
            'For intRow = 0 To strAstCount
                'If intRow <> strAstCount Then
                    'For intCol0 = 0 To strAstCount
                        'strFull = strFull & ctlLB.ItemData(ctlLB.ItemsSelected(intRow, intCol0)) & ", "
                    'Next intCol0
               
                    'For intCol2 = 2 To strAstCount
                        'strFormal = strFormal & ctlLB.ItemData(ctlLB.ItemsSelected(intRow, intCol2)) & ", "
                    'Next intCol2
                'Else
                    'For intCol0 = 0 To strAstCount
                        'strFull = Left(strFull, Len(strFull) - 1)
                        'strFull = strFull & " and " & ctlLB.ItemData(ctlLB.ItemsSelected(intRow, intCol0))
                    'Next intCol0
               
                    'For intCol2 = 2 To strAstCount
                        'strFormal = Left(strFormal, Len(strFormal) - 1)
                        'strFormal = strFormal & " and " & ctlLB.ItemData(ctlLB.ItemsSelected(intRow, intCol2))
                    'Next intCol2
                'End If
            'Next intRow
        'End If

    Me!Assit = strFull
    Me!AAEIOASFormal = strFormal

    Thanks so much~mjc


    mjc

    Thursday, February 25, 2016 7:06 PM
  • Hi mjc. So, you're trying to put the "and" between items then. Here's an example of one possible way to do it for the bound column:

    Dim lngCount As Long Dim lngItem As Long Dim strItems As String lngCount = Me.lstTest.ItemsSelected.Count For lngItem = 0 To lngCount - 1 If lngItem = lngCount - 1 Then strItems = strItems & " and " & Me.lstTest.ItemData(lngItem) Else strItems = strItems & ", " & Me.lstTest.ItemData(lngItem) End If Next

    If Left(strItems, 5) = " and " Then strItems = Mid(strItems, 6) ElseIf Left(strItems, 2) = ", " Then strItems = Mid(strItems, 3) End If MsgBox strItems


    Hope that helps...
    • Edited by .theDBguy Thursday, February 25, 2016 7:51 PM
    Thursday, February 25, 2016 7:50 PM
  • Hi, MJFan23

    According to your description, you could refer to codes below:

    Dim choices As Integer
    choice = Me.LstCity.ItemsSelected.Count
    
    Dim result As String
    
    If choice = 1 Then
       result = LstCity.Column(1, LstCity.ItemsSelected(0))
    ElseIf choice = 2 Then
      For i = 0 To choice - 1
         If i <> 1 Then
            result = result & LstCity.Column(1, LstCity.ItemsSelected(i)) & " and "
         Else
            result = result & LstCity.Column(1, LstCity.ItemsSelected(i))
         End If
      Next
    Else
       For i = 0 To choice - 1
         If i <> (choice - 1) Then
            result = result & LstCity.Column(1, LstCity.ItemsSelected(i)) & ","
         Else
            result = Left(result, Len(result) - 1)
            result = result & " and " & LstCity.Column(1, LstCity.ItemsSelected(i))
         End If
      Next
    End If
    
    MsgBox result
    

    then multi select items:

    the result:

    For more information, click here to refer about ListBox.ItemsSelected Property (Access)

    Friday, February 26, 2016 6:03 AM
  • Good Day David~I have not looked at the reference yet, but I want to let you know YES. The listbox has the full/formal name column hidden AND the listbox is dynamic. So I am not sure how to reference the columns separately.

    Thank you so much David and everyone for your replies.  I will look over all of them today~mikki


    mjc

    Friday, February 26, 2016 12:50 PM
  • >>>I have not looked at the reference yet, but I want to let you know YES. The listbox has the full/formal name column hidden AND the listbox is dynamic. So I am not sure how to reference the columns separately.

    According to your description, please correct me if I have any misunderstandings on your question,

    Use 0 to refer to the first column, 1 to refer to the second column, and so on. Use 0 to refer to the first row, 1 to refer to the second row, and so on. For example, in a list box containing a column of customer FullName and a column of FormalName, you could refer to the FormalName in the second column and fifth row as:

    Me.ListBoxName.Column(1, 4)

    For more information, click here to refer about ListBox.Column Property (Access)

    Monday, February 29, 2016 8:04 AM
  • Good Day All~

    Based on a database sample, I achieved my code:

    Private Sub SII_Click()
    On Error GoTo Err_Handler
    Dim ctlLB As Control
    Dim strAstCount As Integer
    Dim lngRow As Long
    Dim strFull As String
    Dim strFormal As String

     Set ctlLB = Me!LstUnwanted
     
            strAstCount = ctlLB.ItemsSelected.Count - 1
               
        If strAstCount = 0 Then
            strFull = ctlLB.ItemData(ctlLB.ItemsSelected(0))
            strFormal = ctlLB.ItemData(ctlLB.ItemsSelected(0))
        Else
            With Me.LstUnwanted
                For lngRow = 0 To .ListCount - 1
                    If .ListCount = 1 Then
                        strFull = .Column(0, lngRow)
                        strFormal = .Column(2, lngRow)
                    ElseIf .ListCount = 2 Then
                        If strFull = "" And strFormal = "" Then
                            strFull = strFull & .Column(0, lngRow)
                            strFormal = strFormal & .Column(2, lngRow)
                        Else
                            strFull = strFull & " and " & .Column(0, lngRow)
                            strFormal = strFormal & " and " & .Column(2, lngRow)
                        End If
                    Else
                        If strFull = "" And strFormal = "" Then
                            strFull = strFull & .Column(0, lngRow)
                            strFormal = strFormal & .Column(2, lngRow)
                        Else
                            If lngRow + 1 < .ListCount Then
                                strFull = strFull & ", " & .Column(0, lngRow)
                                strFormal = strFormal & ", " & .Column(2, lngRow)
                            Else
                                strFull = strFull & " and " & .Column(0, lngRow)
                                strFormal = strFormal & " and " & .Column(2, lngRow)
                            End If
                        End If
                    End If
                Next lngRow
            End With
        End If
       
     
     Me!Assit = strFull
     Me!AAEIOASFormal = strFormal

    End Sub

    Thanks so much for all of the assistance~mjc


    mjc

    • Marked as answer by MJFan23 Monday, February 29, 2016 1:44 PM
    Monday, February 29, 2016 1:44 PM