Validation Rules

[Emmz]

I have a field called "Type" that I want to add to my table. In this field,
I will be using a list box with my own values that I will add. However, if a
person selects one of these values that happens to be "Will not be
incorporated".....I would like them to be required to enter the "reason" in
another text box.

Is there a validation rule/text that can work with this problem?

Emmz

 

[Jerry Whittle]

Not really at the table level. Validation rules are for entry into that field
so if they enter something then need to enter something else in another
field, it won't work.

However you can do what you want in a form using some VBA code.

 

[RoyVidar]

Not really at the table level. Validation rules are for entry into that field
so if they enter something then need to enter something else in another
field, it won't work.

However you can do what you want in a form using some VBA code.

True, in Access there is really no table level validation, there is
column level validation and row level validation. To have table level
validation, you'd need check constraints, which isn't supported through
the Access interface.

But, this challenge is solvable from the Access interface using row
level validation. While in table design view, bring up the properties
dialog (hit Alt+Enter), then use something like the below in the
validation rule property (also, stuff some meaningful text into the
validation text property)

Iif([Type]="Will not be incorporated", Len([ReasonField] & ""),1) > 0

Perhaps, in stead of using text as datatype for your "Type" field, use
a lookuptable and a numeric PK/FK?

 

[Jamie Collins]

in Access there is really no table level validation, there is
column level validationand row level validation. To have table level validation, you'd need
check constraints, which isn't supported through
the Access interface.

Well, CHECK constraints can be created and maintained using the Access
interface via SQL DDL code and, being engine-level constraints, data
entered via the interface (and everywhere else) gets checked against
such constraints. So 'support' indeed exists but I would agree they
are not 'exposed' well in the Access interface.

I think there are some fundamental problems to exposing CHECK
constraints in the Access interface. Have you ever wondered what a
table level CHECK constraint expression builder would look like? Let's
face it, the Access Query builder tool thing cannot write subqueries
and what truly table level CHECK constraint would not involve a
subquery?! Also, the interface has a 'hard-coded' assumption of a one-
to-one relationship between column and validation rule, ditto for row
but, worse, it's referred to as the Table Validation Rule and is
exposed in the table properties! Obviously, a table can have more than
one CHECK constraint.

So I think leaving their creation and maintenance to those who can
write SQL is probably for the best. One 'nice to have' would be the
definition of the failure message (ditto other flavours of constraint
e.g. PK and FK), as is the case with Validation Rules. But I think
it's essential that copying a table's 'structure' using the Access
interface should also copy its CHECK constraints and I consider this
omission a bug. And what conclusions can we draw from the fact that
Access Help has no mention of CHECK constraints? (merely poor
editorship, IMO).

Jamie.

--