multi select list box?

[jmoore]

I have a form where there will be several instances when the user will need
to select one or more items. My first thought is to change the text box to a
list box, add the options (no more than 4) to the row source and create a
multi-select value list. I am looking for a little guidance as I’ve never
done this. First, is a list box the best solution? If so, is using the row
source best or should I create a separate table with a list of options to
choose and save the choices to the field in tReview? My form is bound to
qYr2ReviewSample, using tables tSample and tReview. If using the row source
or a separate table, is the data automatically saved to the underlying table,
or is code needed?
As an FYI – even though this is not the correct group, I bring this up in
case it makes a difference on how to handle the data entry. I will need to
count the number of times each item is selected for a given group for a
report.
Another question: After the user selects the item(s) from the list box and
that information is saved to the field in tReview, what happens if they have
to go back and change the selections they made? Does it delete/write over
what is stored in tReview?
Thanks so much for any assistance.

 

[Allen Browne]

So one record needs many options associated with it?

Don't do this with a multi-select list box. Create a related table, so than
one record in your main table can have many associated options in the
related table. Your interface will then be a main form, with a subform
instead of a list box. The subform will be in Continuous Form view, so each
item is one line of the subform, and you choose as many rows as you need in
the subform.

It is absolutely essential to learn how to create a related table with a
one-to-many relationship to handle this kind of data. This is the core
concept to grasp when working with a relational database such as Access.

Here's an example:
Don't use Yes/No fields to store preferences
at:
http://allenbrowne.com/casu-23.html
and another:
Relationships between Tables (School Grades example)
at:
http://allenbrowne.com/casu-06.html

If you want to read further on this topic, search on "normalization". Here's
some links:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html#DatabaseDesign101

 

[jmoore]

Thank you for providing so many references. I've read some of these from
other posts before. I'm afraid I did not provide a good explanation. My
situtation is that in each record, I have fields that I'd like to provide a
list of options the user can select one or more from. The list will be text
options. The most one field will have is 4 options, and every field has
different options, and likely the fields will be left blank the majority of
the time. Each record then will have the option(s) selected in each field,
preferable each option on a separate line. For example:

ID Field1 Field2 Field3
1 No date No application No initial in file
No signature Payment history
2 No application No date
3
4
5 No application
6

 

[Allen Browne]

If a field can have from 0 to 4 options chosen, then you do need a related
table where this record can have multiple *records.*

The same is true for your other fields where more than 1 choice may apply:
each one needs a related table.