Default value - form or table?

  • Thread starter Thread starter Pecanfan
  • Start date Start date
P

Pecanfan

Is it best practice to set Default Values for fields from a Form or from the
Table? I've always done it from the Table in the past for no particular
reason - just wondering what the pros and cons are?

TIA,

Andy
 
Advantages of setting Default Value at the Table level:
1. It is automatically applied in all forms, and even append queries.

2. Only one place to manage this. You don't need to trawl through all forms
if you want to change the value.

Advantages of setting Default Value at the Form level:
1. If the database is split and distributed to multiple sites, it is easier
to change the forms than to change the back end.

2. If you use a query as the source for a form so that you can get values
from a lookup table as well as your main table, and the lookup table has a
field that has a Default Value set, the form may fail when you try to add a
new record. In some circumstances, Access tries to apply the Default Value
to the field in the lookup table (even though you are not adding a new
record there), and naturally enough, that fails.

From that experience (particularly the last item), I have changed from my
original practice of defining Default Value at the table-level, and I always
do it at the form level now.

HTH.
 
Interesting - cheers for the reply Allen.

Andy


Allen Browne said:
Advantages of setting Default Value at the Table level:
1. It is automatically applied in all forms, and even append queries.

2. Only one place to manage this. You don't need to trawl through all forms
if you want to change the value.

Advantages of setting Default Value at the Form level:
1. If the database is split and distributed to multiple sites, it is easier
to change the forms than to change the back end.

2. If you use a query as the source for a form so that you can get values
from a lookup table as well as your main table, and the lookup table has a
field that has a Default Value set, the form may fail when you try to add a
new record. In some circumstances, Access tries to apply the Default Value
to the field in the lookup table (even though you are not adding a new
record there), and naturally enough, that fails.

From that experience (particularly the last item), I have changed from my
original practice of defining Default Value at the table-level, and I always
do it at the form level now.

HTH.
Click to expand...
 
Allen-

Thanks for this description of your thought process. I have often
wondered which is more efficient. Do you apply this same logic to all
the field options that could be done at either table or form level?
I'm thinking of: field formats, input masks, setting as a combo box,
etc. Or are the dynamics different for each property?

Betsy
 
Each setting is different.

Anything that can be applied at the engine level should be, so things like
the Required property of the field, or the Validation Rule of the field or
table, and setting Allow Zero Length to No for all text fields are best done
at the table-level.

I never use a combo in a table: all that does is obfuscate what is really
stored there.

I never use input masks: all they do is slow down good data entry operators.

I rarely use the Format property at the table level. Again, it is likely to
mask what is really there.

I don't use the Caption property, as it makes the task more difficult to
identify the field names when looking at a dataset.

Indexing certainly needs to be done in the table, but there is no need to
index your foreign keys where you use relational integrity, because Access
will create a hidden index on those.
 
Again - thanks for your reply. I am learning as I go, and I often try
new things, start new habits and then along the road I'll learn to
really like 'em or discard them when I see problems that sometimes
occur. Your posts speed up my learning!

Betsy
 
Great. It's about learning from each other's experience.

I have certainly learned much from what others post in these forums as well.

Feedback appreciated.
 
Back
Top