Validation Rule

[Tryandtry]

I am stumped with a question that I'm sure is easy for others in this
group.

I have an Access table with two fields: A and B.
A is the primary key.

I want to create a record-level validation rule prohibiting records
where A equals any B. In other words a join of A to B should come up
empty.

Can someone tell me how to do this? Here is the real kicker, I want to
implement this rule on the table, not the form. Is this possible?

Thanks!

Zac

 

[Allen Browne]

Use the Validation Rule of the table:

1. Open the table in design view.

2. Open the Properties box (View menu.)

3. Beside the Validation Rule in the Properties box, enter:
([A] <> ) OR ( Is Null)

Notes:
a) The Validation Rule of the table is different from the Validation Rule
that appears in the lower pane in table design. That one applies to a field,
not a record.

b) You can omit the OR if is a required field. You can also get away
without it in recent versions of Access. In older versions, it would have
had the effect of making B a required field, so I personally consider it
good practice to include it unless B is required.

 

[RoyVidar]

I am stumped with a question that I'm sure is easy for others in this
group.

I have an Access table with two fields: A and B.
A is the primary key.

I want to create a record-level validation rule prohibiting records
where A equals any B. In other words a join of A to B should come up
empty.

Can someone tell me how to do this? Here is the real kicker, I want
to implement this rule on the table, not the form. Is this possible?

Thanks!

Zac

If I understand you correct, I think you need to use methods not
available in the Access UI -> check constraints. This is a feature of
Jet 4.0, which isn't supported by Access. For more info, see the
following link (watch for linebreak in the link)
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k/html/acintsql.asp

Probably the following might work

currentproject.connection.execute _
"ALTER TABLE myTable " & _
"ADD CONSTRAINT myTable_Check_A_differs_from_any_B " & _
"CHECK (A NOT IN (SELECT B FROM myTable))", , _
adcmdtext + adexecutenorecords

Note - if this is what you're after, then you cannot maintain this
through the Access UI, you need DDL. This might give you some
challenges...
To delete the table, you will probably need to either execute a
drop table DDL, or drop the check constraint explicitly first.

currentproject.connection.execute _
"ALTER TABLE myTable " & _
"DROP CONSTRAINT myTable_Check_A_differs_from_any_B ", , _
adcmdtext + adexecutenorecords

Likewise, if you need to copy/import/export this table to/from other
databases, you might see different behaviour with regards to whether
or not an error occurs, but no method, I think, will transfer the
check constraint, which I think you'll need to to manually.

 

[RoyVidar]

The CHECK does not bite after the second insert and the statement "A
differs from any B" is no longer true for the first inserted row. I
think that with the construt you've used the value of A only for the
inserted row(s) is checked.

Your'e correct, I don't know why that thought stuck, but it did.