Listbox question

[Rick B]

I have a form which contains a multiple selection list box. How can I use
the list box so that all items selected are added to the record of it's
corresponding field in a table with each selected item seperated by a comma.

Example Listbox:

Item1
Item2
Item3

If the user selectes all 3 items, I want its corresponding field in the
table to show:

Item1,Item2,Item3

Thank you in advance,

-rick

 

[Dirk Goldgar]

I have a form which contains a multiple selection list box. How can I
use the list box so that all items selected are added to the record
of it's corresponding field in a table with each selected item
seperated by a comma.

Example Listbox:

Item1
Item2
Item3

If the user selectes all 3 items, I want its corresponding field in
the table to show:

Item1,Item2,Item3

Thank you in advance,

-rick

Rick, this can be done but it's generally a bad idea (and a violation of
relational database principles) to store multiple data items in a single
field. That field becomes essentially impossible to query using
straight SQL -- I don't mean that you can't bring up its multiplex
value, but it's exceedingly difficult to run any queries on the elements
that compose that value.

Normally, you would use a related table to hold one record for each item
that is related to the current record on this form. A subform is the
easiest way to view and edit these records, but a list box can be used
if you're willing to write the code to load and unload the selections
from/to the related table. I have code for that lying around here
someplace.

That said, are you sure you want to go ahead with your original design?

 

[Rick B]

I think I understand what your saying. Just so I understand, I should use a
sub form and have the user select each of the items they want in that form
and store the values from this subform into a separate table using the
record ID as the key field to link the two tables.

Is this more acceptable?

Thanks for your quick response

-rick

 

[Dirk Goldgar]

I think I understand what your saying. Just so I understand, I should
use a sub form and have the user select each of the items they want
in that form and store the values from this subform into a separate
table using the record ID as the key field to link the two tables.

Is this more acceptable?

Yes, that's what I'm saying. If you use a subform, you have to give up
the cute listbox that shows the selected items in the midst of all
possible selections, but the relationship can then be managed entirely
without code.

As I said, you *can* use a list box even with a properly normalized
table design, but you have to manage the list box with code, so a
subform is simpler.

Thanks for your quick response.

You're very welcome.