Combine selected values from listbox into one text field

I am using the following code which was supplied long ago by an expert to combine the text values of a listbox into one text field on the same form.  When the user clicks on an item it does get put into the text box.  But when they click on another item that 2nd item is to be added to the 1st one that was added to the text box separated by a comma and a space.

For some reason the onclick code is not working.

Private Sub listAccessories_Click()

Dim lst As String

With Me.listAccessories
If .Selected(.ListIndex) = True Then
    If lst = "" Then
        lst = .Column(0, .ListIndex)
        Else
        lst = lst & "," & .Column(0, .ListIndex)
    End If
    
    Else
    If InStr(lst, ",") > 0 Then
        If InStr(lst, .Column(0, .ListIndex)) = 1 Then
            lst = Replace(lst, .Column(0, .ListIndex) & ",", "")
        Else
            lst = Replace(lst, "," & .Column(0, .ListIndex), "")
        End If
        Else
        lst = ""
    End If
    
    End If
    End With
    Me.txtItemsSelected = lst

End Sub

Open in new window

SteveL13Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NorieAnalyst Assistant Commented:
Is it working for a single selection/click but not for multiple?
SteveL13Author Commented:
It is single click.  But it is not doing what I need it to do.
jansonoylerCommented:
What does it do now?
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

SteveL13Author Commented:
When the user clicks on an item it does get put into the text box.  But when they click on another item that 2nd item is to be added to the 1st one that was added to the text box separated by a comma and a space.
jansonoylerCommented:
"that 2nd item is to be added to the 1st one that was added to the text box separated by a comma and a space."

This is what it should do. I'm asking what does it do now.
SteveL13Author Commented:
Again...  When the user clicks on an item it does get put into the text box.
NorieAnalyst Assistant Commented:
Do you mean that rather than the multiple selected items in the listbox appearing, separated by a comma,  in the textbox only the last clicked item appears in it?
SteveL13Author Commented:
Yes.
jansonoylerCommented:
Thanks for clarifying Norie. Try this steve.

Private Sub combo_box_click()
    Dim textWeWant As String
    
    With Me.combo_box
            If Me.text_box.Value > 0 Then
                textWeWant = Me.text_box.Value & " , " & .Column(0, .ListIndex)
            Else
                textWeWant = .Column(0, .ListIndex)
            End If
    End With
        Me.text_box.Value = textWeWant

End Sub

Open in new window

Rey Obrero (Capricorn1)Commented:
check the Multi Select property of the List box, it should be set to Simple or Extended
NorieAnalyst Assistant Commented:
Not at a computer right now but I think that some sort of loop is needed here.
Rey Obrero (Capricorn1)Commented:
remove this line

Dim lst As String

from the click event of the listbox

place at the top of the form module as shown below

option compare database
option explicit
Dim lst As String
-----------------------------------------------
Gustav BrockCIOCommented:
Keep it simple.
This works:
Private Sub listAccessories_Click()

    Dim Items   As Variant
    Dim Item    As Variant
    
    Items = Null
    For Each Item In Me!ListAccessories.ItemsSelected
        Items = (Items + ", ") & Me!ListAccessories.ItemData(Item)
    Next
    
    Me!txtItemsSelected.Value = Items

End Sub

Open in new window

/gustav

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mbizupCommented:
Here's another variation - just presenting  'Join' as an option for building a delimited list:

Private Sub ListAccessories_AfterUpdate()
    Dim sArray() As String
    Dim itm
    Dim i As Integer

    i = 0

    For Each itm In Me.ListAccessories.ItemsSelected
        ReDim Preserve sArray(i)
        sArray(i) = Me.ListAccessories.ItemData(itm)
        i = i + 1
    Next

    Me.txtItemsSelected.Value = Join(sArray, ", ")

End Sub

Open in new window

SteveL13Author Commented:
Great solutions.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.