Trouble w/ Value List

[ikcaj]

Hello,

I'm attempting to create a database after seven years of not using Access
and I'm already stuck on what is probably a very simple issue:

I am using Win Vista w/ MS Office 2000. I am creating a resource DB, similar
to the contact template DB, except for three unique fields. Each entry must
have a) one of three set choices b)any combination of eight set choices and
c)any combination of four set choices.

I have created the basic db and form without problem. It is trying to
specify the list fields that is causing me trouble. In following the "Help"
directions, I keep ending up with a column of all my choices and it only
choosing the first one.

What I am wanting is three drop down lists, two of which allow for multiple
selections. This is a private db, not for use on the Web or a server. I am
the only one inputting data.

If I need to offer more information in order to receive advice please let me
know.

Thanks,
Jacki

 

[Philip Herlihy]

Hello,

I'm attempting to create a database after seven years of not using Access
and I'm already stuck on what is probably a very simple issue:

I am using Win Vista w/ MS Office 2000. I am creating a resource DB, similar
to the contact template DB, except for three unique fields. Each entry must
have a) one of three set choices b)any combination of eight set choices and
c)any combination of four set choices.

I have created the basic db and form without problem. It is trying to
specify the list fields that is causing me trouble. In following the "Help"
directions, I keep ending up with a column of all my choices and it only
choosing the first one.

What I am wanting is three drop down lists, two of which allow for multiple
selections. This is a private db, not for use on the Web or a server. I am
the only one inputting data.

If I need to offer more information in order to receive advice please let me
know.

Thanks,
Jacki

Right-click one of the combo boxes and look at the properties. The
RowSource says where it gets the items from, and the RowSourceType
indicates if that's a simple list or a reference to a table or query.
If you set "Limit to List" to True, then only those items can be picked.
Under some circumstances you may want to use a validation rule instead
- you couldn't have a list of all the email addresses in the world
(limit to list) but you could devise a validation rule expression which
returned True if the text entered contained an "@" character. The row
sources for your three combo boxes should be different. If the items
are in the same table, then you'll need three different queries on that
table to pick out the right items.

It's often worth experimenting to figure this sort of thing out. Make
sure the control Wizard is turned on, then add a few extra combo boxes
to your form (you can delete them later). Fool around with the options
that the Wizard offers you, and then study the properties of the
resulting controls.

One thing rings alarm bells. You mention multiple selections. The List
Box control has a "Multi Select" property - I don't think the Combo Box
has (can't find it in Access 2007 anyway). However, there are dangers -
it's considered bad design to have multiple values in one table field
(violates the "normalisation" rule of "1st Normal Form" to be specific).
Think of it this way: if multiple selections would mean you'd want to
put several values - maybe separated with commas - into a single field,
then think again. The "Normal Form" rules exist to prevent your data
structure being too complex to manage. On the other hand, if multiple
selects meant that each item was tied to a separate true/false field,
then it's ok. Generally, though, multiple values means multiple
records, or you'll be up very late puzzling out later stages of your design.

HTH

Phil, London

 

[John W. Vinson]

I am using Win Vista w/ MS Office 2000. I am creating a resource DB, similar
to the contact template DB, except for three unique fields. Each entry must
have a) one of three set choices b)any combination of eight set choices and
c)any combination of four set choices.

If you're trying to store multiple values in a field... DON'T.

A2007 has a "multivalue field" but it's a mistake on Microsoft's part, in my
opinion. And it's not necessary.

Rather than a *field*, you should consider using two new *tables*. You can use
a simple table field for your "one of three set choices", but the other two
should use a Many to Many Relationship; e.g. your (b) option would involve
three tables in all:

1. Your current table, with a primary key ResourceID (or whatever it is)
2. A new, 8-row table with the eight set choices, and a primary key SetBID (or
something more meaningful)
3. A new table with fields ResouceID and SetBID as a joint two-field primary
key

You would use a Subform based on this third table, with a combo box to allow
you to enter zero, one, two or eight SetB choices into separate records.

 

[ikcaj]

I appreciate the helpful advice. I was thinking that multiple values in one
field was not the right way to go, but wasn't sure how to get around it.

Thanks again for helping an out-of-date (and not that great to begin with)
user get back up to speed.

Jacki