Others

Update an Access Subform with Status from the Main Form

John WilsonJohn Wilson Nov 22, 2026 868 views

Question details

Determine how to add an Access subform record and handle the main form's Status value.

Update an Access Subform with Status from the Main Form
Product
Microsoft Access
Device & OS
not provided
Scenario
A main form contains a Status value and a StatusNotes subform where the user wants to add a new record.
Observed behavior
The desired design depends on whether Status belongs to the parent record, each note, or a composite relationship key.
Before you start

In Access, review the main and StatusNotes tables, their primary keys, and their relationship in Database Tools > Relationships. Identify whether Status describes the parent, an individual note, or part of the key connecting them.

Solution 1Recommended

Store Status in the table that owns it

Use this approach when Status describes either the parent record or each note, rather than forming part of the relationship key.

1
Inspect the subform link

Open the main form in Design View, select the subform control, and open Property Sheet > Data. Check Link Master Fields and Link Child Fields against the relationship between the tables.

2
Keep parent Status on the main form

If Status describes the parent record, keep it in the parent table and show its bound control on the main form. Link the subform using the appropriate parent and child key fields instead of duplicating Status in StatusNotes.

3
Enter note-specific Status

If each note has its own Status, include a Status field in the StatusNotes table and add a bound Status control to the subform. Enter the value when creating each note.

4
Add a note

Open the main form in Form View, select the parent record, and use the subform's new-record row or navigation button to enter the note.

Store Status in the table that owns it
Avoid unnecessary duplication: If Status belongs only to the parent, retrieve it through the relationship rather than storing a second copy in every note. If each note must preserve a historical Status value, that is a separate data requirement.
Free Microsoft Office alternative

Work with tabular data in WPS Office

WPS Office is a free, lightweight alternative for working with familiar Office documents and spreadsheets. Its familiar interface and Microsoft Office format support can help you move existing files easily. It does not configure Access forms or replace an Access relational database.

Organize and review tabular records in WPS Spreadsheets.Open and edit Microsoft Excel XLSX files and other common Office formats.Use a familiar interface for everyday document and spreadsheet tasks.Move compatible Office files into a free, lightweight suite.
QA img-9

Frequently Asked Questions

Where can I find the subform's master and child link fields?

Open the main form in Design View, select the subform control, and open Property Sheet > Data. Look for Link Master Fields and Link Child Fields.

Should I store the main form's Status in every note?

Not if Status belongs only to the parent and the current value is all you need. Keep it in the parent table and retrieve it through the relationship. A historical snapshot is a separate requirement.

How do I link a subform using two fields?

In the subform control's Property Sheet > Data, enter the corresponding master and child fields in the same order, separated by semicolons, such as FileName;Status. Confirm that the fields match the actual relationship and have compatible data types.

Why might notes disappear after I change Status?

If Status is part of the subform link, changing it can cause the subform to display notes matching the new value instead. Check the link fields and relationship design in Property Sheet > Data and Database Tools > Relationships.