Databases - MSAccess SubForms

MS Access uses sub-forms to disply 1->M (one-to-many) relationships.

Disabling a Sub-Form until a New Master Record is Created

When working with 1->M tables, it is important to always create a master record (the 1) before entering the detail records (the M).

One of the problems is that MS Access lets you enter data into a sub-form before the master record is created. (This occurs when you create a new master record and then enter data into the subform before entering data into the master table's link field.) When this is done, no link is created and any data entered into the sub-form is lost. (It is actually saved in the table, but there are no links to a master record.) There are 2 simple solutions

Enable referential integrity (I haven't tested this)
Use code to hide/display the sub-form

This is the code.

Private Sub Form_Current()
 Check_For_Valid_Master_Record
End Sub

Private Sub Check_For_Valid_Master_Record()
  If [Any Required Master Record Field] <> "" Then
    [Detail Subform].Visible = True
  Else
    [Detail Subform].Visible = False
  End If
End Sub

Form_Current is called each time a new record is displayed, and when a new record is being added.

Since the name Form_Current can not be changed, it makes the code more readable to create an explicitly named subroutine and to call that from Form_Current.

Note: This is not a problem if the tables are linked on an autonumber filed.

Author: Robert Clemenzi - clemenzi@cpcug.org

Databases - MSAccess SubForms

Disabling a Sub-Form until a New Master Record is Created

URL: http:// cpcug.org / user / clemenzi / technical / Databases / MSAccess / SubForms.html