Microsoft Access Subforms Should Reference the Control Rather than Field Name for Link Master Fields

Provided by: Luke Chung, President

Sample database: SubformMaster.zip (39K)

Microsoft Access lets you quickly create forms displaying one-to-many relationships with subforms.

Subforms are most commonly forms displayed as datasheets or continuous forms that show multiple records. Queries and tables can also be used, and even forms in Form View. How the subform is defined does not matter but for this paper, we'll assume it's a form displayed as a datasheet.

The subform is linked based on their LinkMasterFields and LinkChildFields properties, and appear on the subform's property sheet as "Link Master Fields" and "Link Child Fields". One more more fields can be specified separated by commas. MS Access automatically pairs the fields between the master (Parent) and child to filter the data in the subform.

The word "fields" is misleading because the fields may be be fields in your data source or control names on your form. This is the case for the Link Master Fields. For the Child fields, they have to be actual fields in the subform's recordsource.

Is there's a difference in using field names or control names for the master link fields and does it matter? The answer is Yes and Yes. Here's why...

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?

Microsoft Access Subform Linked Master Field to Control
Linked on Control (Good)

Microsoft Access Subform Linked Master Field to Field
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.

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:

Microsoft Access Subform with Linked Master Field to Control and Field

The form uses the table tblMaster as its RecordSource:

Table that is the RecordSource for the Master Form

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. This makes it easy for you to reset it to the MasterID value after changing it.

To see why referencing the control is important, open the form and change the value of the ID value from 22 to 23, then move to the Description control:

Microsoft Access Subform Differences between Linked Master Field to Control vs. Field

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 mess up lots of their ataa in the database.

By default, Microsoft Access names the control the same name as its ControlSource value. So in this case, the text box would be named EditableID (we named it txtMasterID). If the LinkMasterField is set to a value that is the same for the field name and control name, the field is linked and not the control. That creates the problem described here. If you are referring to a control, you should always rename the control to a different name to avoid any confusion. Common convention is to prefix control names based on its type: txt for Text Boxes, cbo for ComboBoxes, cmd for Command Buttons, lbl for Labels, etc.

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.

Good luck!

Table Design

Query Design

Form Design

Form Tips and Mistakes

Copy Command Button and Keep Picture

Module VBA to Forms and Controls

Form Navigation Caption

Resync Record in a Subform

Synchronize Two Subforms

Multiple OpenArgs Values

Late Bind Tab Subforms

Subform Reference to Control Rather than Field

Tab Page Reference

Shortcut Keys


Combo Box Top 6 Tips

Properties and Validation

Select First Item

Cascading Combo Boxes

Zip, City, State AutoFill

Report Design

Suppress Page Headers and Footers on the First Page of Your Report

Add the NoData Event

Annual Monthly Crosstab Columns

Design Environment

Add Buttons to the Quick Access Toolbar

Collapse the Office Ribbon for more space

VBA Programming

Basics: Forms and Controls

Run VBA Code from a Macro

Use Nz() to Handle Nulls

Avoid Exits in the Body of a Procedure

Shortcut Debugging Keys

Set Module Options

Math Rounding Issues

Rename a File or Folder

Avoid DoEvents in Loops

Age Calculations

Weekday Math

Send Emails with DoCmd.SendObject

Source Code Library

Microsoft Access Modules Library

Microsoft Access Modules

VBA Error Handling

Error Handling and Debugging Techniques

Error Number and Description Reference

Basic Error Handling

Pinpointing the Error Line

Performance Tips

Linked Database

Subdatasheet Name

Visual SourceSafe

Deployment

Runtime Downloads

Simulate Runtime

Prevent Close Box

Disable Design Changes

Broken References

Remote Desktop Connection Setup

Terminal Services and RemoteApp Deployment

Reboot Remote Desktop

Missing Package & Deployment Wizard

Avoid Program Files Folder

Unavailable Mapped Drives

Microsoft Access Front-End Deployment

System Admin

Disaster Recovery Plan

Compact Database

Compact on Close

Database Corruption

Remove 'Save to SharePoint Site' Prompt from an Access Database

Class Not Registered Run-time Error -2147221164

Inconsistent Compile Error

Decompile Database

Bad DLL Calling Convention

Error 3045: Could Not Use

Converting ACCDB to MDB

SQL Server Upsizing

Microsoft Access to SQL Server Upsizing Center

Microsoft Access to SQL Server Upsizing Center

When and How to Upsize Access to SQL Server

SQL Server Express Versions and Downloads

Cloud and Azure

Cloud Implications

MS Access and SQL Azure

Deploying MS Access Linked to SQL Azure

SQL Server Azure Usage and DTU Limits

Visual Studio LightSwitch

LightSwitch Introduction

Comparison Matrix

Additional Resources

Connect with Us

 

Free Product Catalog from FMS