Others

How to Copy Records in an Access Subform with Combo Boxes

Kushani Nimanthika Oct 10, 2026 869 views

Question details

The user needs a reliable method to copy rows in an Access continuous subform that includes bound combo boxes and is based on a multi-table query, as standard copy-pasting fails.

How to Copy Records in an Access Subform with Combo Boxes

Product: Microsoft Access
Device & OS: not provided
Scenario: Attempting to duplicate or copy multiple records in an Access subform that uses a multi-table query and bound combo boxes.
Observed behavior: When copying and pasting, the combo box value is not pasted. Attempting to change the combo box value manually produces a "Must save record first" error.

Before you start

Ensure you have a recent backup of your Access database file and verify that the table relationships defining your multi-table queries are correctly configured before making form structure or VBA changes.

Solution 1Recommended

Use a Command Button and VBA to Duplicate Records

Implement a VBA script attached to a command button to explicitly control data insertion, ensuring that foreign keys and combo box values are correctly assigned and saved.

When dealing with multi-table queries and bound combo boxes, standard copy-and-paste functionality behaves differently than it does in a spreadsheet. Because multiple tables are involved, Access struggles to automatically determine the correct foreign-key assignment during a paste operation.

Creating a VBA procedure triggered by a command button provides exact control over data duplication. It allows you to explicitly capture the combo box value and prevents the "Must save record first" error by executing a save command immediately after the record is created.

Open form in Design View

Right-click your continuous subform in the Access navigation pane and select 'Design View'.

Add a Command Button

From the Form Design toolbar, select the 'Button' control and drag it onto the detail section of your continuous subform.

Open the VBA Editor

Right-click the newly added button, select 'Build Event', choose 'Code Builder', and click 'OK' to open the VBA editor.

Write the Duplication Script

Write a VBA procedure to duplicate the current record. Explicitly assign the combo box's foreign-key value to the new record's corresponding field in your script.

Force Record Save

Include the command 'DoCmd.RunCommand acCmdSaveRecord' within your VBA script to ensure the new record is committed to the database immediately, resolving the save error.

Use a Command Button and VBA to Duplicate Records

Reliable Record Creation: Using VBA bypasses the inherent limitations of the Windows clipboard when working with complex relational database forms.

Solution 2

Stage Copied Data Using a Temporary Table

Utilize a temporary staging table for operations requiring users to copy and paste multiple rows at once before appending them to the live production table.

Free Microsoft Office alternative

Simplify Data Management with WPS Spreadsheet

While Microsoft Access is a robust tool for complex relational databases, managing data across multi-table queries can lead to frustrating script errors and convoluted workflows. If you prefer straightforward data tracking, copying, and pasting without having to write VBA code, WPS Office offers a lightweight, highly compatible alternative for everyday data management.

1. Download WPS Office: Visit the official WPS website to download and install the free WPS Office suite.
2. Open WPS Spreadsheet: Launch WPS Spreadsheet to create a new workbook for intuitive data tracking.
3. Manage Data Freely: Import your existing data or CSV exports to manage, copy, and paste records easily without database constraints.

Effortless copy and paste functionality for multiple rows without VBA requirements or database errors.Highly compatible with Microsoft Excel (.xlsx) formats for seamless data migration and sharing.Free and lightweight suite including Spreadsheet, Writer, and Presentation tools.Familiar, intuitive user interface that requires no steep learning curve.

microsoft office alternative - wps office

Frequently Asked Questions

Why does Access show a 'Must save record first' error when pasting?

This error frequently occurs when a form is based on a multi-table query. Access requires the primary record to be fully saved and committed to the database before it can process related foreign keys, such as those selected in bound combo boxes.

Can I copy and paste Access subform rows exactly like an Excel spreadsheet?

Generally, no. While simple single-table forms might allow direct pasting, multi-table queries and continuous forms with bound combo boxes require specialized workflows or VBA scripts to properly handle relational data integrity.

How do I ensure combo box values are included when duplicating a record?

The most reliable method is to use a VBA script attached to a command button. By explicitly referencing the combo box control in your VBA code, you guarantee its selected value is passed to the new row before the record is saved.

Office Quick Solutions