Table vs Form Level Validation

[Kostas]

Just a quick question on this issue.
Assume a small set of fixed values for a field.
For instance Field Gender, values: Male, Female, Unknown

If I create a Combo Box with the above values, I believe I am adding a
form-level validation.

Is it wise to also include the table-level validation for the above
constraint?
For instance, go to the field's validation rule property in Design Mode and
add
IN ("Male","Female","Unknown") ?

This looks like being too pedantic, but there might be cases where it is
necessary (For instance when upsizing, will the combo box constraint be
preserved or not?). I am just asking if there are reasons why one would want
to add validation both ways.

Cheers!
Konstantinos

[Allen Browne]

In general, using engine-level validation is better, because it is applied
regardless of how the data is processed (e.g. append query).

For your particular example, it probably doesn't matter too much if bad data
does get into the field. Personally, I would use a table-level validation
rule on the field of:
Is Null Or "M" or "F"

In cases where it does matter, use a lookup table instead of literal values.
Makes it much easier to maintain the values, and you have consistency
between the combo (RowSource as the lookup table) and the engine-level
referential integrity.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Kostas" <no*****@noemail.net> wrote in message
news:10*************@corp.supernews.com...

Just a quick question on this issue.
Assume a small set of fixed values for a field.
For instance Field Gender, values: Male, Female, Unknown

If I create a Combo Box with the above values, I believe I am adding a
form-level validation.

Is it wise to also include the table-level validation for the above
constraint?
For instance, go to the field's validation rule property in Design Mode
and add
IN ("Male","Female","Unknown") ?

This looks like being too pedantic, but there might be cases where it is
necessary (For instance when upsizing, will the combo box constraint be
preserved or not?). I am just asking if there are reasons why one would
want to add validation both ways.

Cheers!
Konstantinos

[Kostas]

Perfect answer.
Thank you Allen.

Konstantinos

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:41***********************@per-qv1-newsreader-01.iinet.net.au...

In general, using engine-level validation is better, because it is applied
regardless of how the data is processed (e.g. append query).

For your particular example, it probably doesn't matter too much if bad
data does get into the field. Personally, I would use a table-level
validation rule on the field of:
Is Null Or "M" or "F"

In cases where it does matter, use a lookup table instead of literal
values. Makes it much easier to maintain the values, and you have
consistency between the combo (RowSource as the lookup table) and the
engine-level referential integrity.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Kostas" <no*****@noemail.net> wrote in message
news:10*************@corp.supernews.com...

Just a quick question on this issue.
Assume a small set of fixed values for a field.
For instance Field Gender, values: Male, Female, Unknown

If I create a Combo Box with the above values, I believe I am adding a
form-level validation.

Is it wise to also include the table-level validation for the above
constraint?
For instance, go to the field's validation rule property in Design Mode
and add
IN ("Male","Female","Unknown") ?

This looks like being too pedantic, but there might be cases where it is
necessary (For instance when upsizing, will the combo box constraint be
preserved or not?). I am just asking if there are reasons why one would
want to add validation both ways.

Cheers!
Konstantinos