Solved

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

Posted on 2014-08-01
4
1,168 Views
Last Modified: 2014-08-01
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
Comment
Question by:SteveL13
  • 3
4 Comments
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 40234772
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40235156
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
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 500 total points
ID: 40235158
.sample
Database22.mdb
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40235341
Thanks SteveL13
;-)

Enjoy the weekend,

Glad I could help.
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now