Concatenate Multi-Select List Box Items

  • Thread starter Thread starter Pamela
  • Start date Start date
P

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.
For example, if the user selects: Frt Bumper, Grille, & Headlamp in the
List Box, I want to then somehow concatenate those selections into a sentence
like:
There was damage to the Frt Bumper, Grille and Headlamp.
Thanks so much for your help!!

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.
For example, if the user selects: Frt Bumper, Grille, & Headlamp in the
List Box, I want to then somehow concatenate those selections into a sentence
like:
There was damage to the Frt Bumper, Grille and Headlamp.
Thanks so much for your help!!

Pamela
Click to expand...

Let's say the listbox is named lstDamage on form MyForm. Put the following
into a Module:

Public Function ConcatDamage() As String
Dim varRow As Variant
ConcatDamage = "There was damage to the "
For Each varRow In Forms![MyForm]![lstDamage].ItemsSelected
ConcatDamage = ConcatDamage & varRow & ", "
Next varRow
ConcatDamage = Left(ConcatDamage, Len(ConcatDamage) - 1) & "."
End Function

Air code, untested...
 
Thanks, John. But could I get a little more help on how to call this from my
form? I saved it as ConcatDamage in Modules which I thought mirrored your
example.

Thanks so much!

John W. Vinson said:
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.
For example, if the user selects: Frt Bumper, Grille, & Headlamp in the
List Box, I want to then somehow concatenate those selections into a sentence
like:
There was damage to the Frt Bumper, Grille and Headlamp.
Thanks so much for your help!!

Pamela
Click to expand...

Let's say the listbox is named lstDamage on form MyForm. Put the following
into a Module:

Public Function ConcatDamage() As String
Dim varRow As Variant
ConcatDamage = "There was damage to the "
For Each varRow In Forms![MyForm]![lstDamage].ItemsSelected
ConcatDamage = ConcatDamage & varRow & ", "
Next varRow
ConcatDamage = Left(ConcatDamage, Len(ConcatDamage) - 1) & "."
End Function

Air code, untested...
Click to expand...
 
Thanks, John. But could I get a little more help on how to call this from my
form? I saved it as ConcatDamage in Modules which I thought mirrored your
example.
Click to expand...

Use

=ConcatDamage()

as the Control Source of a textbox on a form or report.
 
Back
Top