Stumped...

  • Thread starter Thread starter Stephen
  • Start date Start date
S

Stephen

I've got a form with a list box that has the following
data within it:

AG Agriculture
HM Hydromod
FS Forestry

I've set the list boxes' "Multi Select" property
to "Simple" so that I can make multiple selections. What I
would like to happen is when I make multiple selections
within this list box, I only want the abbreviations to be
inserted into the underlying table's field so that they
appear like:

AG, HM, FS

When I set the list boxes' "Multi Select" property
to "None", the single abbreviation IS properly inserted
into the underlying table's field. However, when I change
the form's list boxes' "Multi Select" to "Simple", nothing
is inserted into the underlying table's field.

What is the simplest way to accomplish this action (if at
all)?

I will be very grateful for any help that anybody can give
me that might help me in this matter!

--Stephen
 
I've got a form with a list box that has the following
data within it:

AG Agriculture
HM Hydromod
FS Forestry

I've set the list boxes' "Multi Select" property
to "Simple" so that I can make multiple selections. What I
would like to happen is when I make multiple selections
within this list box, I only want the abbreviations to be
inserted into the underlying table's field so that they
appear like:

AG, HM, FS
Click to expand...

You have described a "multi-valued" field. That is a violation of good
relational database design principles, and you'll find it difficult to work
with, later. The suggested approach is to have a related table with a
foreign key back to the table where you now have the field, and a record for
each of the values. Then, for example, should you want to create a query to
find every "something" that has an "AG", it will be easy. With a multivalued
field, it would be difficult.

Larry Linson
Microsoft Access MVP
 
Stephen said:
I've got a form with a list box that has the following
data within it:

AG Agriculture
HM Hydromod
FS Forestry

I've set the list boxes' "Multi Select" property
to "Simple" so that I can make multiple selections. What I
would like to happen is when I make multiple selections
within this list box, I only want the abbreviations to be
inserted into the underlying table's field so that they
appear like:

AG, HM, FS

When I set the list boxes' "Multi Select" property
to "None", the single abbreviation IS properly inserted
into the underlying table's field. However, when I change
the form's list boxes' "Multi Select" to "Simple", nothing
is inserted into the underlying table's field.

What is the simplest way to accomplish this action (if at
all)?

I will be very grateful for any help that anybody can give
me that might help me in this matter!
Click to expand...

You could write code to do this, but it would be, well, wrong. What you
are asking to do is to store multiple pieces of information in a single
field, which is a bad idea for a relational database like Access. It
can be done, but it becomes quite difficult to work with the result
using SQL. Queries like "How many items have the 'FS' attribute?"
cannot be written efficiently unless there is a background full-text
indexing system in place, which is not the case with Access.

The normal way to store this sort of thing would be in a related table,
with one record per selection per item. Unfortunately I don't know what
these selections represent, so I can only speak very vaguely; I hope
you can understand what I mean. That would solve the data storage
problem.

That leaves the user-interface question. A subform is the easiest,
code-free way to present and edit this related information on your form.
If you want to use a multiselect list box, you have to use code to load
and unload the list box selections from the related table. That's
doable, and I even like it for small lists, but if you're not happy
writing code you should stay away from it and just use a subform. If
you want to pursue the matter, please post back.
 
Back
Top