Need field Validation rule between two tables

[PAUPET]

Hello Experts
I need to create a field validation rule to ensure data entere
directly into [TABLE1].[FIELD6], exists in [TABLE2].[FIELD2]. Th
rule will trap invalid entries when an append query is executed. N
forms are used -only tables
Thanks...

 

[Larry Linson]

I need to create a field validation rule
to ensure data entered directly into
[TABLE1].[FIELD6], exists in
[TABLE2].[FIELD2]. The rule will
trap invalid entries when an append
query is executed. No forms are used -
only tables.

What you want to do is not supported. With a "developed application" using
appropriate forms, security, and code, you can validate as you describe,
however.

Larry Linson
Microsoft Access MVP

 

[Jamie Collins]

I need to create a field validation rule
to ensure data entered directly into
[TABLE1].[FIELD6], exists in
[TABLE2].[FIELD2]. The rule will
trap invalid entries when an append
query is executed. No forms are used -
only tables.

What you want to do is not supported.

Larry, One of us has misread this post because I can think of two ways
of achieving this:

Scenario 1: TABLE2.FIELD2 is UNIQUE, so use a FOREIGN KEY:

CREATE TABLE TABLE2 (
FIELD2 INTEGER NOT NULL UNIQUE
)
;
CREATE TABLE TABLE1 (
FIELD6 INTEGER NOT NULL
REFERENCES TABLE2 (FIELD2)
ON UPDATE CASCADE ON DELETE CASCADE
)
;

Scenario 2: TABLE2.FIELD2 is not UNIQUE, so use a CHECK:

CREATE TABLE TABLE2 (
FIELD2 INTEGER NOT NULL
)
;
CREATE TABLE TABLE1 (
FIELD6 INTEGER NOT NULL,
CHECK (FIELD6 IN (SELECT FIELD2 FROM TABLE2))
)
;

Jamie.

--