Posted By

heislekw on 05/01/11


Tagged

key null Foreign subform


Versions (?)

Subform records with no main form record


 / Published in: Visual Basic
 

URL: http://allenbrowne.com/ser-31.html

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:
  1. Dim frm as Form
  2. Set frm = Me.Parent
  3.  
  4. If IsNull(frm.[ID]) Then
  5. Cancel = True
  6. MsgBox "Create a record in the main form first.", vbExclamation, "Required data."
  7. frm.[ID].SetFocus
  8. End If

Report this snippet  

You need to login to post a comment.