Others

How to Duplicate an Access Record and Related Subform Records

Huma Ashraf Ch Oct 14, 2026 870 views

Question details

The user needs to duplicate a parent record along with all related subform records via a command button, ensuring the new parent ID is correctly assigned to the duplicated child records while leaving specific fields blank.

How to Duplicate an Access Record and Related Subform Records

Product: Microsoft Access
Device & OS: not provided
Scenario: Creating a command button to copy a checklist record and its associated items to save time, with the requirement to manually enter a new audit number later.
Observed behavior: The main form records can be copied successfully, but the subform records fail to duplicate and correctly link to the newly generated parent AutoNumber ID.

Before you start

Identify the exact table names for your parent and child records, and note which fields are AutoNumbers or manual-entry fields (like AuditNo) so they can be explicitly excluded from your append queries.

Solution 1Recommended

Duplicate Records Using DAO Recordsets and Append Queries

Use Visual Basic for Applications (VBA) with DAO recordsets to insert the parent record, retrieve the newly generated AutoNumber ID, and correctly append the related child records.

In a properly normalized Access database, copying records requires appending the parent record first, capturing its new system-generated ID, and then using that ID to insert the associated child records. This prevents orphaned records and maintains relational integrity.

Open VBA Editor

Open your main form in Design View, select your command button, navigate to the Property Sheet, and click the ellipsis (...) next to the 'On Click' event to open the VBA Editor.

Copy the Parent Record

Use a DAO Recordset (.AddNew method) or an INSERT INTO SQL statement to copy the fields of the current parent record. Ensure you exclude the AutoNumber field and any fields you want to enter manually later, such as 'AuditNo'.

Retrieve the New Parent ID

If using a DAO Recordset, retrieve the new AutoNumber ID immediately after the .Update method by reading the ID field from the recordset bookmark, or use @@IDENTITY if using SQL execution.

Append the Child Records

Execute an append query (INSERT INTO) that selects the existing subform records matching the old parent ID and inserts them into the child table, replacing the foreign key field (ItemID) with the new parent ID you just retrieved.

Duplicate Records Using DAO Recordsets and Append Queries

AutoNumber Fields: Always let Microsoft Access assign the AutoNumber values automatically. Do not attempt to manually copy or force an AutoNumber primary key during the duplication process.

Free Microsoft Office alternative

Looking for a Lightweight Data and Document Alternative?

If you are managing extensive database reports, tables, or documentation, WPS Office offers a free, lightweight, and highly compatible suite. It provides an intuitive environment for your spreadsheets and documents without the steep learning curve of advanced database programming.

Fully compatible with Microsoft Office formats, including Excel (.xlsx) for managing your exported database tables.Lightweight installation with a familiar, easy-to-use tabbed interface.Built-in PDF tools for seamlessly sharing your data reports.Cross-platform support allowing you to view and edit files on Windows, Mac, Linux, and mobile devices.

microsoft office alternative - wps office

Frequently Asked Questions

Why do my duplicated child records in Access still show the old parent ID?

This usually happens when you copy the tables or records directly without updating the foreign key. You must programmatically retrieve the newly generated AutoNumber ID from the copied parent record and assign it to the child's foreign key field during the append process.

How do I exclude specific fields like 'AuditNo' from being duplicated?

When writing your SQL INSERT statement or defining the fields in your DAO recordset script, simply omit the fields you want to leave blank. Access will leave them null or assign their default values in the new record.

Can I use basic Access macros instead of VBA to duplicate parent and subform records?

While basic macros can duplicate a single record, they lack the capability to easily capture a newly generated parent ID and pass it to multiple subform records. Using VBA with DAO recordsets is the most reliable method for this specific task.

Office Quick Solutions