Validation Rule

[Marshall Barton]

I think you are stuck between the proverbial rock and hard
place. The design you're working with is inadequate for
your data integrity rules. I'm thinking this evolves into a
normalization problem where you need a (child) table for the
accounting data so that the presence of accounting data can
be omitted from the data integrity rules.

One way I try to keep some of this straight in my mind is to
use an imaginary (because Access isn't very good at it)
middle tier for business rules. Then I have to decide if a
rule belongs in the middle tier or in the back end.
Occasionally, isolating the business rules to a few(?) class
objects helps to isolate them from the UI and data engine.

All this can really get to be more than a little confusing.
I thought I had a grip on at least the general ideas
involved with this stuff until I ran into a project that
allowed **all** data to be entered as it was discovered
(i.e. in any order at any time, maybe never). Boiled down,
it meant that there were no candidate keys and I was stuck
with a table that had no natural primary key (in other words
it was all business rules with no data integrity rules).
Kind of felt like I was in the twilight zone of relational
theory :-\

 

[Marshall Barton]

You could say that Access has no capability in that area.
However you can sometimes fudge the data integrity features
into helping out, but the UI features are the standard
fallback position. OTOOH, you can do an awful lot with all
the programming features (especially class modules) in
Access.