• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1365
  • Last Modified:

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

0
SteveL13
Asked:
SteveL13
  • 3
1 Solution
 
Helen FeddemaCommented:
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.
0
 
Jeffrey CoachmanMIS LiasonCommented:
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
0
 
Jeffrey CoachmanMIS LiasonCommented:
.sample
Database22.mdb
0
 
Jeffrey CoachmanMIS LiasonCommented:
Thanks SteveL13
;-)

Enjoy the weekend,

Glad I could help.
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now