Concatenate Multi-Select List Box Items

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
 
J

John W. Vinson

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

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...
 
P

Pamela

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

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...
 
J

John W. Vinson

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.

Use

=ConcatDamage()

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top