Populate textbox with multiselect list box

[Manuel C via AccessMonster.com]

Hi:

I have a form that contains a multiselect listbox (simple multiselect) and a
command button. The listbox shows a list of all reports (about 20) available
for users to print and the command button has an onclick() event for
printing these reports.

The problem i have is that i would like to have a textbox in the form that
shows which reports have been selected.

Example:

lbReportList shows the following available reports:

- Report 1
- Report 2
- Report 3
- Report 4
- Report 5
- Report 6

If the user selects reports 1, 4, and 5 to print, i would like the textbox to
read "Report 1, Report 4, Report 5). This textbox would not be bound to any
tables in my database, i just want to include whatever is in the textbox in
one of my reports.

I have looked everywhere in previous discussions but have not found a clear
answer.

Thanks for your help

 

[Arvin Meyer [MVP]]

Something like this ought to do it:

Private Sub lstWhatever_Click()
Dim varItem As Variant
Dim strList As String

With Me!lstWhatever
If .MultiSelect = 0 Then
Me!txtSelected = .Value
Else
For Each varItem In .ItemsSelected
strList = strList & .Column(0, varItem) & ","
Next varItem
If strList <> "" Then
strList = Left$(strList, Len(strList) - 1)
End If
Me.txtSelected = strList
End If

End With

End Sub
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[Manuel C via AccessMonster.com]

Thank you for your reply. Your code works perfectly as it is!

I also played around with it and ran into a little problem. Instead of
placing a comma between selected items in the listbox, i checked to see what
the textbox would look like if i started a new line with vbNewLine after each
selection. Code works fine but when i go to print a report with the results
of the textbox, i get a small square at the end of the last selection. I
found how to get rid of it with an update query in a table but since my
textbox is unbound, i can't seem to find a way to get rid of this character.


I can't thank you enough for taking the time to read our questions and for
sharing some of your vast knowledge with us.

Something like this ought to do it:

Private Sub lstWhatever_Click()
Dim varItem As Variant
Dim strList As String

With Me!lstWhatever
If .MultiSelect = 0 Then
Me!txtSelected = .Value
Else
For Each varItem In .ItemsSelected
strList = strList & .Column(0, varItem) & ","
Next varItem
If strList <> "" Then
strList = Left$(strList, Len(strList) - 1)
End If
Me.txtSelected = strList
End If

End With

End Sub

Hi:

[quoted text clipped - 26 lines]

Thanks for your help

 

[Arvin Meyer [MVP]]

There is no equivalent for vbNewLine in Windows/Ascii characters, so Access
can't print it and you see the square for each unprintable character. You
might try adding a Carriage Return and Line Feed with: Chr(13) & Chr(10) so
you might try:

strList = strList & .Column(0, varItem) & Chr(13) & Chr(10)

The comma is required as a data separator
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

Thank you for your reply. Your code works perfectly as it is!

I also played around with it and ran into a little problem. Instead of
placing a comma between selected items in the listbox, i checked to see what
the textbox would look like if i started a new line with vbNewLine after each
selection. Code works fine but when i go to print a report with the results
of the textbox, i get a small square at the end of the last selection. I
found how to get rid of it with an update query in a table but since my
textbox is unbound, i can't seem to find a way to get rid of this character.


I can't thank you enough for taking the time to read our questions and for
sharing some of your vast knowledge with us.

Something like this ought to do it:

Private Sub lstWhatever_Click()
Dim varItem As Variant
Dim strList As String

With Me!lstWhatever
If .MultiSelect = 0 Then
Me!txtSelected = .Value
Else
For Each varItem In .ItemsSelected
strList = strList & .Column(0, varItem) & ","
Next varItem
If strList <> "" Then
strList = Left$(strList, Len(strList) - 1)
End If
Me.txtSelected = strList
End If

End With

End Sub

Hi:

[quoted text clipped - 26 lines]

Thanks for your help

 

[Manuel C via AccessMonster.com]

Thanks for your reply Arvin

I had also tried with Chr(13) Chr(10) and still got the same result. What i
ended up doing was adding a space (& " ") after vbNewLine. For some reason
the unprintable character is not visible if it is not the last character
typed. What i get now is a space before the name of each report. It is
probably not the best fix, but a small space is more aesthetically pleasing
that the unprintable character box.

Thanks again Arvin for your suggestions

There is no equivalent for vbNewLine in Windows/Ascii characters, so Access
can't print it and you see the square for each unprintable character. You
might try adding a Carriage Return and Line Feed with: Chr(13) & Chr(10) so
you might try:

strList = strList & .Column(0, varItem) & Chr(13) & Chr(10)

The comma is required as a data separator

Thank you for your reply. Your code works perfectly as it is!

[quoted text clipped - 36 lines]