To my way of thinking, some of the properties in table design control what
the engine does, and some control what the interface does.
The engine-level properties belong in the table -- things like: Required,
Validation Rule, Allow Zero Length, Indexed, Field Size.
The interface-level properties belong in the forms/reports. These
are:Format, Caption, Input Mask, Display Control, and so on.
Keeping the interface properties in the interface makes it easy to manage
the application after you split it. It's messy for a developer to try to
modify these properties in the back end if it's already installed at many
sites.
This can also avert some weird problems in Access. One of the problems with
Name AutoCorrect is that it messes not only with the field names, but also
their captions. So you can mess up a query if the Caption of the field in
the table changes, or have Access interpreting things wrongly if an old
caption (now discarded) is used as an alias in a query.
Similarly, there's a bug where a subform that's based on a multi-table query
generates errors when you try to add a new record to the subform, if one of
the fields in the lookup tables has a DefaultValue assigned. Access tries to
assign this value, even though you are not adding a record to the lookup
table, and throws a wobbly.
So, engine-level properties in table design; interface-level properties in
the forms and reports.
