Microsoft Access Subforms Should Reference the Control Rather than Field
for Master Link Fields
Provided by: Luke Chung
Sample database: SubformMaster.zip (28K)
Link Master Field Should Reference the Control Rather than Field
Microsoft Access lets you quickly create forms displaying one-to-many
relationships with subforms. Subforms are linked based on their Link Master
Fields and Link Child Fields properties.
We recommend referencing the control rather than field when
specifying the Link Master Field. This is different from the Link Child
Field which should always be a field in the subform. For situations where the link master field is editable, a subform MUST be
linked to the control rather than underlying field. Otherwise, the wrong
data could be displayed or edited by the user.
What we're considering is how the subforms should reference the Master.
Should it reference the control (txtMasterID) or the field name directly
(EditableID) below?
Linked on Control (Good) |
Linked Directly to Field (bad) |
If the link master field were MasterID which is an autonumber field and
can't be edited, this would not be an issue. However, if the field/control can be edited, it's critical that the Link
Master Field uses the control name rather than the field name. It's also
important that you rename the control so it's not the same name as the field
name.
Background: Setting Up Subforms
One of the most powerful features of displaying data is the use of
subforms. A subform is a form with its own data source that is embedded in
another "master" form. From our sample database, open the form frmEditableMaster:
This form uses the table tblMaster as its recordsource:
In the sample form, the field that's being used to link the subforms is
the EditableID, rather than the MasterID. The two fields happen to contain
the same information, but the EditableID field can be modified.
Referencing the Control is Critical if It Can be Edited
To see why referencing the control is important, open the form and change
the value of the ID value from 22 to 23, then tab to the Description
control:
The control's value has changed, but the underlying value in the table
has not because the record is not saved. That only occurs when the user
leaves the record. However, the subforms displayed on the screen are totally different. The
one linked to the control (left) shows the records related to Master ID 23,
while the one linked to the field (right), continues to show the saved value
of 22.
By referencing the control, the subform linked on the control
automatically updates when the control's value changes. This ensures the
subform always reflects the displayed master field value.
By referencing the field, it's easy to get confused and think the subform
records are related to the value when they aren't. Users think they're
looking at data related to one master record, when they were really viewing
another one. Who knows what bad decisions could result? Even worse, if the
user adds, edits, or deletes records in the subform, they could completely
mess up lots of data in the database.
Summary
The example is obviously quite trivial but displays the difference.
As a habit, you should rename the control to a different name from the
field name, and always reference the control name rather than field as the
Link Master Field.
Additional Resources
Good luck!
|
Email Newsletter
Developer Team Blog
Facebook
Twitter