Using Multi-Select List Box Items in another Control

[Pamela]

I need to use the chosen items in a multi-select List Box as concatenated
text in another control on a subform. This text will be part of a large
amount of concatenated text. How do I do this? I did find info here about
using such items in a query but it was way over my head and I couldn't figure
out how to convert that idea to this issue so please don't just refer me to
that w/o some other explanation for this novice. Thanks so much for your
help!!

Pamela

 

[Tom Wickerath]

Hi Pamela,

Can you give an example of the data shown in your multiselect listbox, and
the result you are trying to achieve?


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

 

[Pamela]

Hi Tom,

The List Box (actually, I have 3 of them that I'll be doing the same thing
with) have just a list of text. We do wrecked car inspections and so I have
a List Box, bound to tblDamageArea that lists the major parts of the car:
Frt Bumper, Grille, Headlamps, etc (of course, these are each their own entry
in the List). So if all 3 of those items, for example, are damaged, they
will select all 3. After filling out this, and other info, I have a final
unbound text box at the bottom of my form that concatenates all of the
various info and creates a grammatically correct paragraph for the user to
copy and paste into other applications as needed. I want to take that info
and create a sentence like: "The vehicle sustained damage to the Frt bumper,
grille and headlamp." Thanks so much for your help!
Pamela

 

[Tom Wickerath]

Hi Pamela,

I want to take that info and create a sentence like:
"The vehicle sustained damage to the Frt bumper, grille and headlamp."

Okay, so you need to concatenate all items selected, and replace the second
to the last comma with the word "and", and replace the very last comma with a
period. One can built up this string by "iterating" the .ItemsSelected
property of the multiselect listbox, to achieve a result that looks like this:

Frt bumper, grille, headlamp,

Something like this (Caution--untested), where I am referring to the second
field in the Row Source for the text [ie. Column(1) using zero-based column
count]. The name of the list box control is "lboDamagedParts".

Option Compare Database
Option Explicit

Private Function EnumDamagedParts() As Variant
On Error GoTo ProcError

Dim varCategory As Variant
Dim strText As String
Dim intPosition As Integer

' Process all items selected in lboDamagedParts
For Each varCategory In Me.lboDamagedParts.ItemsSelected()
strText = strText & Me.lboDamagedParts.Column(1, varCategory) & ", "
Next varCategory


If Len(strText) > 0 Then
'Replace last comma with a period
strText = Left$(strText, Len(strText) - 2) & "."
'Debug.Print strText
'Replace last remaining comma, if present, with the word "and":
intPosition = InStrRev(strText, ",")
If intPosition > 0 Then
EnumDamagedParts = Left$(strText, intPosition - 1) _
& Replace(strText, ",", " and", intPosition)
Else
EnumDamagedParts = strText
End If
Else
EnumDamagedParts = Null
End If

'Debug.Print EnumDamagedParts

ExitProc:
Exit Function
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in EnumDamagedParts Function..."
Resume ExitProc
End Function


The Debug.Print statements (shown commented out) are useful for debugging
purposes, to print the intermediate results to the Immediate Window.

Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________