Advanced Search

Return to Snippet

Revision: 45461
at May 1, 2011 02:43 by heislekw
Initial Code 
Dim frm as Form
Set frm = Me.Parent

If IsNull(frm.[ID]) Then
   Cancel = True
MsgBox "Create a record in the main form first.", vbExclamation, "Required data."
   frm.[ID].SetFocus
End If
Initial URL 
http://allenbrowne.com/ser-31.html
Initial Description 
You have a table with a primary key, and related table with a foreign key. You have created a relationship between these two tables, with referential integrity. Now you create a main form with a subform for these two tables. Some of the records you enter in the subform seem to disappear never to be seen again. Where have they gone?

Referential integrity does not block Nulls in the foreign key. Normally Access fills in the foreign key in a subform to match the primary key in the main form. But if the main form is at a new record, Access merrily accepts the entry in the subform with a Null foreign key. These records never show up again in the subform, since they do not relate to any record in the main form.

You must take specific action if you want to prevent this occurring.
Solutions:

Do either (or both) of the following:

    * Set the Required property of the Foreign key to True in Table Design.
    * Use the subform's BeforeInsert event procedure to cancel (or question) the insertion:
Initial Title 
Subform records with no main form record
Initial Tags 

                                

                                                            

                                    

                                        

                                        Initial Language
                                        

                                    

                                    
Visual Basic