Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2014-08-01
4
Medium Priority
?
1,285 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 2000 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

730 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