Referential Integrity not being applied in forms

  • Thread starter Thread starter Mus' via AccessMonster.com
  • Start date Start date
M

Mus' via AccessMonster.com

Hi All

I have forms with subforms mirroring my tables and subtables. I have set up
enforced RI in the relationships and the parent/child links in the forms.
However, on some forms it is allowing the creation of child records without a
parent?? At present I cannot see why this is doing it. Any ideas?

Thanks in advance.
 
Hi All

I have forms with subforms mirroring my tables and subtables. I have set up
enforced RI in the relationships and the parent/child links in the forms.
However, on some forms it is allowing the creation of child records without a
parent?? At present I cannot see why this is doing it. Any ideas?

Thanks in advance.
Click to expand...

Check the field definitions to see if there are default values in the
child (FK) fields.
 
Mus, this is a very important question.

Referential Integrity guarantees that the foreign key field cannot contain a
value that does not match the p.k. of the lookup table. It does *not*
guarantee that there will be a foreign key value!

To prevent orphan records, open the related table in design view, select the
foreign key field, and set its Required property to Yes. If you do not do
this Access permits null foreign key values. This is technically correct,
and sometimes useful, but it constantly catches people out as it may not be
what you expect.

That solves the problem, but the user does not get the message until they
have filled in all the subform fields and try to save the record. If you
would like to give them a message when they begin adding the subform record,
use the subform's BeforeInsert event procedure as well:

Private Sub Form_BeforeInsert(Cancel As Integer)
If Me.Parent.NewRecord Then
Cancel = True
MsgBox "Enter the main form record first."
End If
End Sub

This issue is #3 in this article:
Common errors with Null
at:
http://allenbrowne.com/casu-12.html
 
Thanks All who responded. FYI

Bob, as a precaution I had already set the default value of the FK to match
the parent PK.

Karl, the PK's were definitely Null (AutoNumber).

...and the winner is...... Allen, you're a superstar !

I'm so chuffed with this important piece of learning. You hit the nail right
on the head! (I'm self-taught and read a lot of books on relational database
design theory, Access, VBA, etc but missed this.)

I had encountered the error also but was struggling to trap it. I had tried
the forms OnDirty and OnError events but this was leading me on a wild goose
chase.

Thanks gain for the lesson in Referential Integrity and introducing the
BeforeInsert event.

I've now bookmarked your website ;o)
 
Back
Top