Others

Use an Access Form to Update Related Tables and Save Contract History

Steven ParkerSteven Parker Nov 20, 2026 868 views

Question details

Create an Access form that retrieves and updates personnel information while preserving contract history in related tables.

Use an Access Form to Update Related Tables and Save Contract History
Product
Microsoft Access
Device & OS
not provided
Scenario
A personnel form needs to display current information and allow users to add contract records for each person.
Observed behavior
The desired form should connect records through matching keys and retain each historical contract as a separate record.
Before you start

Identify the personnel table, the tables you need to edit, and the fields that must be retained for each contract. Open Database Tools > Relationships to inspect the existing keys and links.

Solution 1Recommended

Create a Personnel Form With a Related Contracts Subform

Use a one-to-many relationship and a subform to update personnel data and add separate contract history records.

Keep current personnel information in the main table and store each contract as a separate row in a contracts table. Include the contract terms and any personnel details that must remain unchanged as of that contract in the historical record.

1
Set up the personnel key

Open the personnel table in Design View and assign a primary key such as PersonnelID. Rename fields that use reserved names, such as Name, to descriptive names like FullName.

2
Create the contracts table

Use Create > Table Design to add a Contracts table with its own primary key, a PersonnelID foreign key, and fields for contract dates, terms, and other snapshot values. If PersonnelID is an AutoNumber primary key, set the matching foreign key to Number with the Long Integer field size.

3
Link the tables

Open Database Tools > Relationships, add the relevant tables, and drag PersonnelID from the personnel table to PersonnelID in the contracts table. Select Enforce Referential Integrity when appropriate and create the one-to-many relationship.

4
Build the form and subform

Use Create > Form Wizard to select fields from the personnel and contracts tables and create a form with a subform. In Design View, check the subform control's Link Master Fields and Link Child Fields properties and set both to PersonnelID if they are not already linked.

5
Add a historical contract

Open the form in Form View, navigate to a personnel record, and enter a new row in the contracts subform. Enter the values to preserve for that contract and move to another record to save it; add a new row for a later contract instead of overwriting the old one.

Create a Personnel Form With a Related Contracts Subform
Preserve snapshot values: A link to the current personnel record alone does not preserve past personnel details. Store any values needed for a historical snapshot in the contract record when it is created.
Free Microsoft Office alternative

Manage Related Office Documents With WPS Office

WPS Office is a free, lightweight alternative for working with documents, spreadsheets, and presentations in a familiar interface. Open compatible Office files to make migration easier; WPS Office does not replace Access relational databases or fix Access forms.

Review contract documents and personnel spreadsheets in one office suiteOpen and edit common Microsoft Office formats, including DOCX, XLSX, and PPTXUse a familiar interface for everyday document and spreadsheet tasksGet a free, lightweight office suite for routine work
microsoft office alternative - wps office

Frequently Asked Questions

Why should contracts be stored in a separate table?

A separate Contracts table allows one personnel record to have many contract records. Link the tables through PersonnelID in Database Tools > Relationships.

How do I make the subform show contracts for the current person?

Open the main form in Design View, select the subform control, and check its Link Master Fields and Link Child Fields properties. Set them to the matching personnel key fields.

Will changing a personnel record also change old contract snapshots?

Historical values stored separately in a contract row remain as entered unless that row is edited. A form that displays a value directly from the current personnel table will show the current value instead.

Why can't I add a record in the subform?

Check the subform's Allow Additions property and open its Record Source directly to see whether it is updatable. Also check the foreign key relationship and your permission to edit the table.

Can WPS Office replace an Access form and its related tables?

WPS Office can work with compatible documents and spreadsheets, but it does not replace Access forms or relational database functionality.