Set default value in table or form

[BruceM]

I have been wondering if there is a reason to favor either the table, a
bound control, or VBA (the form's Current event, for instance) for setting
the default value of a field. I prefer to stay away from the table just
because once it is designed I would rather leave it alone and work with the
form, but I don't know if there is any technical reason to prefer one over
the other.

 

[Allen Browne]

Yes, that's a good reason. Particularly in a split database, maintenance is
simpler if you set the Default Value in the text box on a form, rather than
have to change the default in the back end database.

There is also a bug in Access that you can workaround if you do not set the
Default Value in the table. Picture a form that has a query as its
RecordSource. The query contains the table you really want for your form, as
well as a lookup table that you are not writing to but want to read values
from (e.g. for sorting or filtering.) If this lookup table has a Default
Value set on one of its fields, then in some situations you will have
problems adding new records in the subform. At the first keystroke when you
begin typing the new record, Access complains with a fairly unobvious
message that says it is unable to assign the value. It is trying to assign
the Default Value to the field in the Lookup table (even though you are not
trying to add a record to that table), and naturally that fails. Removing
the Default Value from the lookup table (or from the text box on the form)
averts this problem. Having struck this a couple of times, I never assign a
Default Value to any table field now.

 

[BruceM]

Allen,

Thanks for your response, and for all of the help you provide here and on
your web site, including your awareness of bugs and how to go about swatting
them. The point about maintenance on a split database is quite apparent now
that I have read it. I am still relatively new to Access, but one of the
things I picked up right away (from where I do not recall) was the idea of
setting formats, defaults, etc. in the interface rather than in the table
when possible. Lately I have been wondering if this was actually a good
idea. Thanks once again.

 

[Tim Ferguson]

I have been wondering if there is a reason to favor either the table,
a bound control, or VBA (the form's Current event, for instance) for
setting the default value of a field.

In my book, it's horses for courses:

Table-level settings should be reserved for items vital to data
integrity. For example, Required fields probably should have default
values to prevent nasty error messages on insert commands.

The control's defaultvalue property is convenient on forms but won't help
users who insert records using VB or Excel or Word etc. Or a different
form! -- you have to remember to dial it in for all your user interface
objects.

Using the OnCurrent or BeforeInsert event is very flexible, and you can
change the defaulted value in response to all kinds of other aspects; but
suffers from all the disadvantages of the control DV and you need to take
care not to let it overwrite good data in existing records.

Hope that helps


Tim F

 

[BruceM]

Tim,

Thanks for your thoughts on the subject. I hadn't considered the point
about using several different forms for the same table. Apparently I
haven't used default values in such cases, or I might have noticed the
inconvenience of changing several forms. Same for importing data, a
situation I usually encounter only at the beginning of the process of say,
converting from Excel to Access, or form an old database into a newer one.
Since default value as I understand it applies only to new records I would
not have thought overwriting existing data to be a concern.

 

[Tim Ferguson]

I hadn't considered the
point about using several different forms for the same table.

Standard practice: one form for creating the purchase order, a different
one for the packers, another one for the payment receipts office.. My
design philosophy is One Table = One Business Entity, One Form = One
Business Process.

Since default value as I understand it
applies only to new records I would not have thought overwriting
existing data to be a concern.

It's only really a concern if you use the form OnCurrent event to fill in
the default values -- you have to check if the current record is a new one
(need to put something in) or an old one (don't overwrite old data).

Best wishes


Tim F