John W. Colby
jwcolby at colbyconsulting.com
Sun May 16 10:19:13 CDT 2004
Another way to handle this is set the default value to 0 and have a "Zeroth record" that matches. Then set the "allow nulls" to false. The database engine will absolutely prevent the nulls from getting in there and will in fact error when it happens, allowing you to see when and why it is happening. John W. Colby www.ColbyConsulting.com -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Greg Smith Sent: Friday, May 14, 2004 2:34 PM To: accessd at databaseadvisors.com Subject: RE: [AccessD] Referential Integrity -- Fact or Fiction? --- LooksLike 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 > -- _______________________________________________ AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com