How to Copy a Parent Record and Related Child Records in Access VBA
Question details
The user needs to copy a Microsoft Access parent record and all related child records using VBA, correctly linking the copied child rows to the new parent primary key.
- Product
- Microsoft Access VBA
- Device & OS
- not provided
- Scenario
- The user is automating data duplication in Access. When a parent record is copied, one or more related child table records must also be copied and linked to the new parent record so table relationships remain intact.
- Observed behavior
- The user is trying to copy a parent record and related child records but needs the correct VBA pattern to retrieve the newly generated primary key and use it as the foreign key for every copied child record.
Open the Microsoft Access database and press Alt+F11 to open the Visual Basic Editor. Confirm the exact parent table name, child table name, primary key field, foreign key field, and the required recordset permissions before running any copy code.
Copy the Parent Record First, Capture the New Primary Key, and Insert Linked Child Records
Use this VBA pattern when you need to duplicate an Access parent record and keep all related child rows correctly linked to the new parent key.
The safest order is to create the new parent row first, read the generated primary key, and then insert each child row with that key as the foreign key. This avoids orphaned child rows or incorrect relationships.
In Microsoft Access, press Alt+F11 to open the Visual Basic Editor, then locate the module or form class where the copy code will run.
Open a DAO or ADO recordset on the parent table with edit or add permissions, use AddNew to create the new parent record, populate the copied fields, and call Update to save it.
After saving the parent record, read the new RecipeID field from the parent recordset or use the recordset's last-modified record to obtain the generated primary key value.
Open a recordset on the original child table, tblRecipeIngredients, filtered to the original parent ID, and loop through each matching child record.
For each original child record, use AddNew on the child table, copy the ingredient fields, set the foreign key to the new RecipeID, and call Update.
Compile and run the code with the Visual Basic Editor break and error-handling options enabled. Check that field names match the table definitions and that the new parent and child records appear correctly.
WPS Office Is Not an Access Replacement, but a Free Microsoft Office Alternative
This Access VBA parent/child copy workflow belongs to Microsoft Access itself and cannot be performed in WPS Office. For daily document, spreadsheet, and presentation tasks, WPS Office is a free, lightweight Office alternative that preserves Microsoft Office file compatibility, offers a familiar ribbon UI, and lets you migrate without retraining.
- 1. Download WPS Office Free: Go to the official WPS Office website, click Download, and install WPS Office Free on your compatible Windows or Mac computer.
- 2. Open existing Office documents: Launch WPS Office, click Menu > Open, and select a Microsoft Word, Excel, or PowerPoint file to open it in a familiar editing interface.
- 3. Continue editing and save: Use the WPS Office ribbon and formatting tools to edit the file, then save it in its original Microsoft Office format or export it as PDF.
Frequently Asked Questions
Do I need to copy the parent record first in Access VBA?
Yes. Create the new parent record first so you can capture its generated primary key, then use that value as the foreign key for each copied child record.
What recordset permissions are required for copying parent and child records in Access VBA?
Open the parent and child recordsets with the required add or edit permissions, and make sure the field names in the VBA code match the actual table definitions.
Why do my copied child records not link to the new parent record?
Usually the new parent primary key was not captured or used as the child foreign key. After saving the parent record, read the generated RecipeID and assign it to every inserted child row.
Can WPS Office run Microsoft Access VBA macros?
No. WPS Office does not include an Access database engine or run Access VBA. Keep Microsoft Access for this database automation and use WPS Office for Word, Excel, and PowerPoint-compatible files.
How can I troubleshoot errors in an Access VBA copy routine?
Enable break and error-handling options in the Visual Basic Editor, compile the code, post or read code as text, and verify table and field names and recordset permissions.
