concatenate list box text values into a string

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm new to VB code. I've got a form that allows the user to select options
from a number of list boxes. I'd like to have a button concateneate several
of these list box values into one text string, then create a new record in an
existing table with this value as one of the fields.

Any ideas or a direction to point me in to see sample code for this sort of
thing?

Thanks.
 
I'm new to VB code. I've got a form that allows the user to select options
from a number of list boxes. I'd like to have a button concateneate several
of these list box values into one text string, then create a new record in an
existing table with this value as one of the fields.

Any ideas or a direction to point me in to see sample code for this sort of
thing?

Thanks.
Click to expand...

As a rule, this is a Very Bad Idea. Fields should be "atomic", having
only one value. You can easily store the separate fields, and
concatenate them on demand for data display or printing. Could you
explain what these values are, and why you feel that you need to store
a redundant, composite field?

It can certainly be done - most straightforwardly using a command
button on the form and a bit of VBA code - but I really question
whether it *should* be done!

John W. Vinson[MVP]
 
Thanks John.

We design and manufacture timber components for our business. This almost
always requires us to supply a bunch of hardware components that we custom
order from a handful of different suppliers. Nuts, bolts, washers, split
rings, rods and custom steel components. This information needs to be
entered into some sort of list by the designer. The designer needs to be
able to do this geographicaly throught the project in order to maintain
accuracy. They dont want to look for all the 3/4" nuts throught the whole
project, then come back and look for all the split rings. The designers are
currently doing this with a excel spreadsheet but it's cumbersome and time
consuming. It seems like a natural fit for a form that can later create
purchase orders to the different suppliers.

Problem is that split rings have different fields asoociated with them,
namely size and finish. Bolts have diameter, length, head shape, grade, and
finish. It would also be convienient to have an option for "add (1) washer
and (1) nut" or "add (2) washers and (1) nut. This information is *always*
compiled into the description field and is never modified. (except for the
exta washer or nut options, those need to be separate records.) Right now,
the designers just type it all into a single excel cell. I'm trying to make
it a little easier for them.

I understand the concept of atomic data in fields, but practiacly, for us,
the compiled description *is* atomic. We never need to sort or query by
"head shape" or grade, but we do need to query by the aggregate info in the
Description field. Right now, if a designer types "3/4"x10" A307 MB Hex Gal"
(3/4" x 10" a307grade, Machine Bolt, Hex head, Galvanized) in one record then
types "3/4"x10" MB A307 MB hex Gal" in another record, the text string forces
them to be "not alike" for query purposes. If I have a VBA routine run when
they hit the "post to table" button, it can concatenate their selections
consistently and accurately.

I know I can have related tables for bolts, split rings, rods,etc., but it
just seems like so much database overhead for such a simple list that it
doesn't seem worth it.

Thanks again.
 
I'm new to VB code. I've got a form that allows the user to select options
from a number of list boxes. I'd like to have a button concateneate several
of these list box values into one text string, then create a new record in an
existing table with this value as one of the fields.

Any ideas or a direction to point me in to see sample code for this sort of
thing?

Thanks
Click to expand...

Sorry for not getting back to you earlier!

Try code like this, on the Click event of a button next to the
listbox:

Private Sub cmdProcessList_Click()
Dim strConcat As String
Dim varItem As Variant
strConcat = ""
With Me.lstMyListbox
For Each varItem in .ItemsSelected
strConcat = strConcat & .Column(0, varItem) & " "
Next varItem
End With
Me!txtMemofield = strConcat
End Sub

John W. Vinson[MVP]
 
Thanks for your help John.

I'm actually trying to concatenate the values from multiple listboxes, not
multiple selections from one listbox.

Any help there?
 
Thanks for your help John.

I'm actually trying to concatenate the values from multiple listboxes, not
multiple selections from one listbox.

Any help there?
Click to expand...

ah... sorry!

Me.txtMemofield = Me.lstOne & Me.lstTwo & Me.lstThree

Include commas, vbCrLf (new lines), blanks as literals if you wish; or
if the value you need concatenated isn't the bound column of the
listbox,

Me.txtMemofield = Me.lstOne.Column(x) & Me.lstTwo.Column(x)

where x is the zero based subscript of the desired field in the
listbox's row source.

John W. Vinson[MVP]
 
Back
Top