 |
Microsoft Access Subforms Should Reference the Control Rather than Field for Master Link FieldsProvided by: Luke ChungSample database: SubformMaster.zip (28K) Link Master Field Should Reference the Control Rather than FieldMicrosoft 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?
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 SubformsOne 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 EditedTo 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. SummaryThe 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! |
Featured Product
|
Microsoft
Access Resource Center
 |
Contact Us
l Web questions: Webmaster
l Copyright
© 2010 FMS, Inc., Vienna, Virginia |