Use an Access Form to Update Related Tables and Save Contract History
Question details
Create an Access form that retrieves and updates personnel information while preserving contract history in related tables.

- 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.
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.
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.
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.
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.
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.
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.
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.

Check Why an Access Form or Subform Cannot Accept Edits
Use these checks if the form displays related data but does not allow changes or new records.
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.

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.




