none
Multi-select Listbox Selections Combined in Sentence RRS feed

  • Question

  • Good Day~

    I have a listbox and I know how to combine the selections in an unbound textbox with commas however, I want to put and between the next to last and last records selected so I can use them in a sentence in my reports.

    Mr. John Doe, Mr. James Next and Mr. Jerry Last

    Please help.

    Thank you in advance~mjc


    mjc

    Tuesday, December 8, 2015 7:17 PM

Answers

  • >>>however, I want to put and between the next to last and last records selected so I can use them in a sentence in my reports.

    Mr. John Doe, Mr. James Next and Mr. Jerry Last<<<

    According to your description, you could refer to below code and modify them based on your requirement:

    '  Mr. John Doe, Mr. James Next and Mr. Jerry Last
      Dim nameChoice As String
      Dim number As Integer
      number = LstName.ItemsSelected.count - 1
      If number = 0 Then
        nameChoice = LstName.ItemData(LstName.ItemsSelected(0))
      Else
        For i = 0 To number
           If i <> number Then
              nameChoice = nameChoice & LstName.ItemData(LstName.ItemsSelected(i)) & ","
           Else
              nameChoice = Left(nameChoice, Len(nameChoice) - 1)
              nameChoice = nameChoice & " Next and " & LstName.ItemData(LstName.ItemsSelected(i)) & " Last"
           End If
        Next i
      End If
      Debug.Print nameChoice

    • Marked as answer by David_JunFeng Wednesday, December 23, 2015 2:39 PM
    Wednesday, December 9, 2015 5:15 AM
  • The following is code for the method I suggested.  With a list boxed named lstNames, a button named cmdMakeList and a text box named txtNameList it will populate the text box with the selected names on the click of the button

    Private Sub cmdMakeList_Click()

        Dim n As Integer
        Dim blnNotAnd As Boolean
        
        Me.txtNameList = Null
        
        With Me.lstNames
            For n = .ListCount - 1 To 0 Step -1
                If .Selected(n) Then
                    Me.txtNameList = IIf(blnNotAnd, ", ", " and ") & .ItemData(n) & Me.txtNameList
                    blnNotAnd = True
                End If
            Next n
                
            ' remove leading substring
            Me.txtNameList = Mid(Me.txtNameList, IIf(.ItemsSelected.Count > 1, 3, 6))
       End With

    End Sub

    The RowSource of the list box is an SQL statement which concatenates the Title, FirstName and LastName columns in to a single string expression.

    Ken Sheridan, Stafford, England

    • Marked as answer by David_JunFeng Wednesday, December 23, 2015 2:39 PM
    Thursday, December 10, 2015 6:55 PM

All replies

  • Hi. If you're using a For Each loop to go through the selected items, you might try using a For Next loop instead, so you can trap the last item. Just a thought...
    Tuesday, December 8, 2015 7:39 PM
  • Rather than looping through the control's ItemsSelected collection from first to last, do so in reverse order and build the sting expression from right to left.  That way you can easily concatenate the 'and' into the expression at the first iteration of the loop by setting the value of a Boolean variable to True after adding the first item to the expression.  The 'and' would only be inserted and the comma omitted where the value of the variable is False.

    The other way would be loop from first to last in the usual way and make use of the Count property of the ItemsSelected collection to determine when the last item has been reached, and add the 'and' to the expression and omit the comma at that iteration of the loop.

    Ken Sheridan, Stafford, England

    Tuesday, December 8, 2015 11:44 PM
  • >>>however, I want to put and between the next to last and last records selected so I can use them in a sentence in my reports.

    Mr. John Doe, Mr. James Next and Mr. Jerry Last<<<

    According to your description, you could refer to below code and modify them based on your requirement:

    '  Mr. John Doe, Mr. James Next and Mr. Jerry Last
      Dim nameChoice As String
      Dim number As Integer
      number = LstName.ItemsSelected.count - 1
      If number = 0 Then
        nameChoice = LstName.ItemData(LstName.ItemsSelected(0))
      Else
        For i = 0 To number
           If i <> number Then
              nameChoice = nameChoice & LstName.ItemData(LstName.ItemsSelected(i)) & ","
           Else
              nameChoice = Left(nameChoice, Len(nameChoice) - 1)
              nameChoice = nameChoice & " Next and " & LstName.ItemData(LstName.ItemsSelected(i)) & " Last"
           End If
        Next i
      End If
      Debug.Print nameChoice

    • Marked as answer by David_JunFeng Wednesday, December 23, 2015 2:39 PM
    Wednesday, December 9, 2015 5:15 AM
  • Good Day Mr. Sheridan~

    Thank you ever so much for your time and consideration.  I used the code you suggested and received Error 91 - Object variable or With block variable not set.  Any suggestions?

    Thank you so much for getting me closer to my solution~mjc


    mjc

    Wednesday, December 9, 2015 11:49 AM
  • I assume that you are referring to David's code as I only posted a description of the methodology, not any code.

    At first glance the one omission I notice is that the i variable is not declared:

        Dim i As Integer

    Ken Sheridan, Stafford, England

    Wednesday, December 9, 2015 9:20 PM
  • >>>I used the code you suggested and received Error 91 - Object variable or With block variable not set.  Any suggestions?

    Could you provide more information about which line code causes you receiving "Error 91 - Object variable or With block variable not set.", for example sample code, screenshot etc., that will help us reproduce and resolve your issue.

    Thanks for your understanding.

    Thursday, December 10, 2015 8:44 AM
  • The code works just fine for me - I tested it out of curiosity.

    IF you have Option Explicit declared at the top of your code (as you should!) you'll need the explicit declaration of "i", as Ken Sheridan pointed out.

    The code posted assumes that your list box is called 'lstName'.  If it is named differently, you'll have to replace lstName in the in the code with whatever your actual listbox name is.  (This problem will give you an 'object' error)

    If neither of those help, my guess is that the error is in code that was not posted here.


    Miriam Bizup Access MVP


    • Edited by mbizup MVP Thursday, December 10, 2015 11:43 AM typo correction
    Thursday, December 10, 2015 11:42 AM
  • The following is code for the method I suggested.  With a list boxed named lstNames, a button named cmdMakeList and a text box named txtNameList it will populate the text box with the selected names on the click of the button

    Private Sub cmdMakeList_Click()

        Dim n As Integer
        Dim blnNotAnd As Boolean
        
        Me.txtNameList = Null
        
        With Me.lstNames
            For n = .ListCount - 1 To 0 Step -1
                If .Selected(n) Then
                    Me.txtNameList = IIf(blnNotAnd, ", ", " and ") & .ItemData(n) & Me.txtNameList
                    blnNotAnd = True
                End If
            Next n
                
            ' remove leading substring
            Me.txtNameList = Mid(Me.txtNameList, IIf(.ItemsSelected.Count > 1, 3, 6))
       End With

    End Sub

    The RowSource of the list box is an SQL statement which concatenates the Title, FirstName and LastName columns in to a single string expression.

    Ken Sheridan, Stafford, England

    • Marked as answer by David_JunFeng Wednesday, December 23, 2015 2:39 PM
    Thursday, December 10, 2015 6:55 PM