asked on

How populate text box with selections from a multi-select (simple) listbox?

I have a textbox on a form named txtSortBySelection . Also on the form is a listbox named listSortBy.

When I click on a selection in the listbox I want that selection to appear in the textbox. But when I click on another selection in the listbox I want the 1st selection to remain in the textbox but follow it with a comma and then a space and then the 2nd selection from the listbox. In other words the listbox will display the selections from the listbox separated by a comma and a space in the order they were selected in the listbox.

I have this code but it isn't doing the job...

Private Sub listSortBy_AfterUpdate()

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

End Sub

Open in new window

Helen Feddema

It sounds as if you want to create a concatenated string from the selections in the listbox. Here is an Access Archon article that does this for multi-valued fields and linked tables:
http://www.helenfeddema.com/Files/accarch220.zip
and here is some code for working with the ItemsSelected collection of a multi-select listbox; you should be able to combine these techniques to get what you want.

Private Sub cmdProcessListboxItems_Click()

On Error GoTo ErrorHandler

   Dim lst As Access.ListBox
   Dim strData As String
   Dim strPrompt As String
   Dim strTest As String
   Dim strTitle As String
   Dim varItem As Variant
   
   Set lst = Me![lstSelectContacts]
      
   'Check that at least one item has been selected
   If lst.ItemsSelected.Count = 0 Then
      strTitle = "No items selected"
      strPrompt = "Please select at least one item"
      MsgBox prompt:=strPrompt, _
         buttons:=vbInformation + vbOKOnly, _
         Title:=strTitle
      lst.SetFocus
      GoTo ErrorHandlerExit
   End If
   
   strTitle = "Information missing"
   
   'Test for required information, using listbox columns
   For Each varItem In lst.ItemsSelected
      'Check for required address information (or whatever you need to check)
      strTest = Nz(lst.Column(5, varItem))
      Debug.Print "Street address: " & strTest
      If strTest = "" Then
         strPrompt = "Skipping this record -- no street address!"
         MsgBox prompt:=strPrompt, _
            buttons:=vbExclamation + vbOKOnly, _
            Title:=strTitle
         GoTo NextItem
      End If
      
      'Do something with info from the listbox columns, using
      'this syntax
      strData = Nz(lst.Column(5, varItem))
      
NextItem:
   Next varItem

ErrorHandlerExit:
   Exit Sub

ErrorHandler:
   MsgBox "Error No: " & Err.Number _
      & "; Description: " & Err.Description
   Resume ErrorHandlerExit

End Sub

Open in new window

Another possibility is to write the items from the listbox to a temp table, and display it in a datasheet subform.

Jeffrey Coachman

This may be trickier than you might think...
The kicker here is that if you select the items like this: (Down in sequence)
1
3
5
7
...everything is fine, ...you get: 1, 3, 5, 7

However, if you do this:
1
3
5
7
2
...you will end up with this:
1, 2, 3, 5, 7
(see the attached sample)

This is because the listbox will read the list of selected items in the order they appear in the listboxes internal array.
I mean, you could do it the way you ask, ...but the code will need to be more complex...

JeffCoachman

ASKER CERTIFIED SOLUTION

Jeffrey Coachman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jeffrey Coachman

Thanks SteveL13
;-)

Enjoy the weekend,

Glad I could help.