[AccessD] Referential Integrity -- Fact or Fiction? --- Looks Like a FACT!

Lambert:

Thanks!  I'll bet that's it.  So now I know for sure that they CAN get in
there and it's not an Access issue.  THAT being the case, now I have to
figure out how these orphans are getting in there...because it doesn't
happen consistantly...just when some certain event occurs (that they've
not been able to pin down) that is out of the ordinary.  Out of 400,000+
records entered, I have 55 blanks.  Statistically not bad, but it
sometimes unbalances their books.  And THAT is bad...particularly when the
County Recorder calls me and asks WHY.

Thanks again Lambert.

Greg Smith
GregSmith at Starband.net


> Ah Hah. Now I see what you mean, and looking at the on-line help, here
> is the reason for what you have observed...
>
> "You can't enter a value in the foreign key field of the related table
> that doesn't exist in the primary key of the primary table. However, you
> can enter a Null value in the foreign key, specifying that the records
> are unrelated. For example, you can't have an order that is assigned to
> a customer that doesn't exist, but you can have an order that is
> assigned to no one by entering a Null value in the CustomerID field."
>
> So nulls are "ok".
>
> This whole thing would be moot however if you always do the data entry
> in a form bound to the Fee Book table with a sub-form bound to the Fee
> Book Ledger table. With the appropriate Parent / Child links Access will
> automatically plug in the Foreign Key value for you and you'll never get
> any orphans that way.
>
> Lambert
>