Concatenate all items in a listbox

Rakesh935 · Feb 18, 2013

Hello,

Is there any way to concatenate all the items listed in listbox and obtain the value in a purticular row of the same table.

note:
The listbox items can be varriable.

Thank you....

Beetle · Feb 18, 2013

Yes, this can be done with code, but I certainly would not recommend storing the results in a table. Just display the results when needed in a query, form or report.

Assuming you have a list box on a form, then you would use some event (like the Click event of a command button) to run a few lines of code. If you want all of the items in the list box, regardless of whether or not they were selected, then the procedure might look something like this (keep in mind these are just a quick and dirty examples. I normally would not be so lazy with my object naming);

Code:

Private Sub Command4_Click()

    Dim i As Integer, s As String
    
    With Me.List0
        For i = 0 To .ListCount - 1
            s = s & .ItemData(i) & ", "
        Next
    End With
           
    s = Left(s, Len(s) - 2)
    Me.Text2 = s

End Sub

Results look like;

If you have a multi-select list box, and you want only the items that were selected, then it might look like;

Code:

Private Sub Command4_Click()

    Dim v As Variant, s As String
    
    With Me.List0
        For Each v In .ItemsSelected
            s = s & .ItemData(v) & ", "
        Next
    End With
    
    s = Left(s, Len(s) - 2)
    Me.Text2 = s
    
End Sub

Results look like;

Alternatively, if the list box values come from a table (as would normally be the case), then you could use a custom function like the one here to concatenate the values directly from the table and display them in a query field or a form/report control.

Rakesh935 · Feb 18, 2013

Hi,

I do understand the risk factor for getting the concateneted values updated in the same table but what to do this as per again the requirement.

Again, after i get the concateneted values updated i got to run a unique/duplicate as per below:

Concatenate value uni/Dup
aa Uni
aa Dup
de Uni
eh Uni
fe Uni

Hope you are able to understand the requirement...

Thank you

Beetle · Feb 18, 2013

To do the concatenation you would need to use one of the methods I described before. To get that value into the table you would have to write the result back to a table field using an Update query, or set the field value in code.

Access_Help · Nov 25, 2019

Code:
If you have a multi-select list box, and you want only the items that were selected, then it might look like;

Code:

Private Sub Command4_Click() Dim v As Variant, s As String With Me.List0 For Each v In .ItemsSelected s = s & .ItemData(v) & ", " Next End With s = Left(s, Len(s) - 2) Me.Text2 = s End Sub

Results look like;

How do I reference the third column in the listbox to concatenate?

moke123 · Nov 25, 2019

I use a public function for multi-select listboxes. There are arguments to select which column to use as well as the seperating character and any delimiters needed.

Code:

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 an array 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 array returned
'Delim is optional delimiter to be return in array 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

            If Nz(lbx.Column(intColumn, (varSelected)), "") <> "" Then

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

            Else

                strList = strList

            End If

        Next varSelected

        If Nz(strList, "") <> "" Then

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

        End If
    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

theres an example here ... https://access-programmers.co.uk/forums/showpost.php?p=1627158&postcount=12

hth

Access_Help · Nov 25, 2019

Can we not reference column numbers in this code?

Private Sub Command4_Click()

Dim v As Variant, s As String

With Me.List0
For Each v In .ItemsSelected
s = s & .ItemData(v) & ", "
Next
End With

s = Left(s, Len(s) - 2)
Me.Text2 = s

End Sub

vba_php · Nov 25, 2019

Access_Help said:
Can we not reference column numbers in this code?

Private Sub Command4_Click()

Dim v As Variant, s As String

With Me.List0
For Each v In .ItemsSelected
s = s & .ItemData(v) & ", "
Next
End With

s = Left(s, Len(s) - 2)
Me.Text2 = s

End Sub

perhaps this?

Code:

    With Me.List0
        For Each v In .ItemsSelected
            s = s & .ItemData(v)[B].column(2)[/B] & ", "
        Next
    End With

theDBguy · Nov 25, 2019

There seems to be a rash lately of old threads being resurrected. I'm not sure if that's a good thing or not.

moke123 · Nov 25, 2019

@dbg - didnt notice this one but agree there has been quite a few lately.

The column property has an index and row arguement so I believe you can just write it as ...

Code:

    With Me.List0
        For Each v In .ItemsSelected
            s = s & .column(2,v) & ", "
        Next
    End With

Concatenate all items in a listbox

Rakesh935

Registered User.

Beetle

Duly Registered Boozer

Attachments

Rakesh935

Registered User.

Beetle

Duly Registered Boozer

Access_Help

Registered User.

moke123

AWF VIP

Access_Help

Registered User.

vba_php

Forum Troll

theDBguy

I’m here to help

moke123

AWF VIP

Similar threads

Users who are viewing this thread