Others

How to Copy an Access Form and Subform Records as New Data

Muhammad Talha Oct 9, 2026 869 views

Question details

The user needs to duplicate a Microsoft Access form along with its subform records to create a new set of data.

How to Copy an Access Form and Its Subform Records as New Data

Product: Microsoft Access
Device & OS: not provided
Scenario: Duplicating existing parent and child data entries displayed within an Access form to create a new, identical record relationship.
Observed behavior: Copying the form itself only duplicates the design window; it does not duplicate the underlying relational records stored in the database tables.

Before you start

Identify the primary key of your parent table and the corresponding foreign key in your child table, and ensure your database is backed up before running any action queries.

Solution 1Recommended

Duplicate Records Using Append Queries and VBA

Use a sequence of two append queries executed via VBA or a macro to copy the parent record, capture its new ID, and then append the child records.

Microsoft Access forms are merely windows that display data stored in tables. To copy the 'contents' of a form and subform, you must actually duplicate the underlying relational records.

This requires appending a new parent record, finding out what new AutoNumber Primary Key was assigned to it, and using that new key as the Foreign Key for the new child records.

Create the Parent Append Query

Open the Query Design view and create an Append Query for the parent table. Include all fields except the primary key (AutoNumber) field, setting the criteria to select the current record displayed on the form.

Capture the New Primary Key

Write a VBA procedure to execute the parent append query. Immediately after execution, use the @@IDENTITY variable or the DMax function within your VBA code to retrieve the newly generated primary key.

Create the Child Append Query

Create a second Append Query for the child table. Configure this query to append the child records associated with the old parent ID, but replace their foreign key value with the newly captured primary key.

Execute within a Transaction

Run both queries sequentially within a single VBA macro routine. Wrapping these executions in a transaction ensures that if the child query fails, the parent query can be rolled back to prevent orphaned records.

Duplicate Records Using Append Queries and VBA

Data Duplication Complete: Once the VBA routine finishes, the new parent and child records will be stored in your tables and can be viewed by navigating to the new record in your Access form.

Solution 2

Copy the Form and Subform Design

If your goal is to create a visually identical form for a different purpose without duplicating the actual table data.

Free Microsoft Office alternative

Looking for a lightweight alternative for daily Office tasks?

While WPS Office does not include a direct relational database tool like Microsoft Access, it is an exceptional choice for managing documents, spreadsheets, and presentations. For flat data management, reporting, and analysis, WPS Spreadsheet provides a robust and free alternative to Excel.

1. Download the Installer: Visit the official WPS website and click the free download button for your operating system.
2. Install WPS Office: Run the downloaded installer and follow the on-screen instructions to complete the setup.
3. Manage Data with Spreadsheets: Open WPS Spreadsheet to easily organize, filter, and analyze your data sets in a familiar grid format.

Fully compatible with Microsoft Word, Excel, and PowerPoint formats.Lightweight software that runs smoothly on Windows, Mac, Linux, and mobile devices.Familiar tabbed user interface for a seamless transition from Microsoft Office.Powerful spreadsheet functions for managing flat-file database records and data analysis.

microsoft office alternative - wps office

Frequently Asked Questions

Can I copy an Access form's data just by copying the form object?

No. Access forms do not store data; they are simply interfaces used to view and edit data stored in tables. Copying the form object in the Navigation Pane only duplicates its visual design and layout, not the database records.

How do I maintain the parent-child relationship when copying relational records?

You must append the parent record first and capture its newly generated AutoNumber primary key. Then, use that newly generated key as the foreign key value when appending the related child records into the subform's underlying table.

Is it good practice to copy form designs for minor changes?

Generally, no. Creating multiple nearly identical forms makes database maintenance much more difficult. It is usually better to use VBA code to dynamically hide, show, or configure controls on a single form based on the specific application context.

Office Quick Solutions