How do you put a validation rule for ID numbers in Access?

[Guest]

Require to validate all Identity Nos of my clients when capturing data. When
you enter the invalid ID no, it must report error.I have the validation code
from Department of Home Affairs to identify false ID nos. How do I put this
code in the system and in what fields/tables/forms must I put it in order for
it to work properly.Must also be able to identify a duplication of ID Nos,
whether Member or Spouse and quote the member no where it exists. Please
help!!!

 

[Guest]

in what fields/tables/forms must I put it in order for it to work properly.
Without knowing your table structure it is a little difficult to say where
to put it.

What does a valid ID look like? Do you have a complete list in advance?
How will you be using the ID's? Just to build a list of people?

A few more details please.

 

[Jamie Collins]

Without knowing your table structure it is a little difficult to say where
to put it.

I don't need the specific model to say...

There are two places to implement the business rule: a) the database
table (because MS Access does not support column-level CHECK
constraints nor the ANSI CREATE ASSERTION syntax for validation at the
domain level) and 2) every front end application which uses the
database. See:

http://www.dbazine.com/ofinterest/oi-articles/celko27

" If the code is a foreign key in several tables, the validation needs
to be done only on the table where it is a primary key...

"Should the front end repeat the same validations as the database? The
general rule is that you mop the floor and fix the leak. The puddle is
the application side and the leak is the database side. The database
will be the final, trusted authority since it will reject rows that
violate constraints."

How will you be using the ID's?

The OP should take a look at UNIQUE CONSTRAINT (keywords in uppercase,
not shouting <g>). Because failure or otherwise of the UNIQUE is
dependent on the state of the data, a database round trip is required
for validation.

Jamie.

--