Solved

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

Posted on 2014-08-01
4
1,215 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

726 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