Using Value Lists

  • Thread starter Thread starter TonyB
  • Start date Start date
T

TonyB

Hi,
I'm lookng for some advice re value lists in fields. I have a table with a
field containing one of three values in a value list format.
If I'm creating a form to input data into the table, might it best to
actually create the value list in the form and keep the field in table as a
simple text field ? Or can a control such as a combo box on the form reflect
the values that the field in the table can take ?
In short when using a value list and if always inputting data via a form, is
it best to put the value list in the form or table ?
I appreciate I could use a separate table for the values but that seems
overkill for such a small list.
TIA
Tony
 
Hi,
If you are talking about 'lookup' values. A list that is fairly static.
Then I would put these values in their own table. Where else would you store them?
Use a combo box on a form to allow users to choose.
 
Here's another way to look at it...

Access uses tables to store data. You are (hopefully) keeping all (the
rest) of your data in tables. But you are considering putting three pieces
of data someplace else (in a value list)?!

From a consistency standpoint, not having to look in multiple places to find
data is easier.

If you are the only one who will ever "maintain" this database, and you are
unlikely to ever forget that you put some data in tables and other data in
lists, do whatever makes sense to you. Or ... <g>!

JOPO (just one person's opinion)

Jeff Boyce
<Access MVP>
 
Hi Dan,
Maybe I didn't explain myself very well. In a field in a table I have used
the lookup feature in Access to use a static value list as the possible
values for the field.
I enter data into the table using a form. It occured to me that you that you
make this field a text type without any lookup from a value list, and in the
form have a combo box to show the current value of the field, and for a new
record use the combo box to select the value that is to be written into the
field.
So either the combo box in the form has the value list, or the field in the
table has the value list. I can see that if I did have any other forms that
enter data into that table I would have to duplicate the combo box, and if I
change the list, I would have to remember to edit it in all the forms that
may enter data into this table. So maybe the table would be a better place
for the list anyway.
I realize that you could have a totally separate table that contains the
data for this list and store the key to the selected item in the orginal
table. Then it would be very easy to add new items to the list etc. But for
three items it seemed a bit of overkill !
Thanks
Tony
 
Tony

I'm curious, and I'd like to do a better job at offering support ideas.
Could you let me know what about my suggestion didn't work for you? Thanks.

Jeff Boyce
<Access MVP>
 
If you think there is any possibility that the list may change, I would use
a separate table, as Jeff has advised. You already know that if you use a
value list in a form combo box, you'll need to update each form if you want
to change the list. But also consider that, if you use a value list at the
table level, you'll need exclusive access to the table to save design
changes - you'll have to get all users to stop using the app while you make
the change. I would advise using value lists only for lists that you expect
not to change.
 
To reinforce Brendan's comment, I would add...

If you are absolutely, positively certain that the list will NEVER change,
you probably need to use a table (because it probably WILL <g>!).

If you are only fairly certain it never will change, but can imagine a
circumstance where it might, you need to use a table.

Anything else, use whatever (but be ready to "refactor" your list to a table
<G>!)

Jeff Boyce
<Access MVP>
 
Hi Jeff,
You did answer my question by saying don't do it that way at all, use a
another table.
I knew a separate table works in this situation, and this database I'm
working on has several tables storing a few items that may change or grow in
number.
I guess I regard setting up a table to store possible values for a field in
another table as a bit of a chore, which I will do when I can see that I
really need one. But if the
list of values is small and definitely not going to change, a list may be
ok. In that case I was asking whether the list of values should reside in
the table or the form ? I guess
you answer would still be in another table ! I guess I ought to get
comfortable with the idea of making tables for everything. <g>
Thanks
Tony
 
TonyB said:
In that case I was asking whether the list of values
should reside in the table or the form ?
Click to expand...

You have had enough people tell you to use a separate table, so I will just
address this question.

It doesn't matter. If you store it in the table, and you later change/add
to the value list, you'll still have to go to each form and change it there
as well (these things don't propagate automatically).

If you don't store it in the table, then you won't get a combo box
automatically created when you create a form by the wizard, or drag the
field to the form.

If you have this list on multiple forms, and you want to add to the list or
modify it, you'll have to make the change manually on every form where you
have the value list.

If this list were a table, rather than a value list (regardless of where it
is), if you have to change/add to the list, you just have to open the table
and add the value - presto done.
 
Back
Top