Concatenate all items in a listbox (1 Viewer)

Rakesh935

Registered User.
Local time
Tomorrow, 02:08
Joined
Oct 14, 2012
Messages
71
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

Duly Registered Boozer
Local time
Today, 14:38
Joined
Apr 30, 2011
Messages
1,808
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.
 

Attachments

  • Untitled1.jpg
    Untitled1.jpg
    34.4 KB · Views: 4,181
  • Untitled2.jpg
    Untitled2.jpg
    34.6 KB · Views: 3,846

Rakesh935

Registered User.
Local time
Tomorrow, 02:08
Joined
Oct 14, 2012
Messages
71
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

Duly Registered Boozer
Local time
Today, 14:38
Joined
Apr 30, 2011
Messages
1,808
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

Registered User.
Local time
Today, 13:38
Joined
Feb 12, 2005
Messages
136
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

AWF VIP
Local time
Today, 16:38
Joined
Jan 11, 2013
Messages
3,852
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
 
Last edited:

Access_Help

Registered User.
Local time
Today, 13:38
Joined
Feb 12, 2005
Messages
136
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

Forum Troll
Local time
Today, 15:38
Joined
Oct 6, 2019
Messages
2,884
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

I’m here to help
Staff member
Local time
Today, 13:38
Joined
Oct 29, 2018
Messages
21,358
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

AWF VIP
Local time
Today, 16:38
Joined
Jan 11, 2013
Messages
3,852
@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
 

Users who are viewing this thread

Top Bottom