How to Add a Related Subform Record When Saving a Main Record in Access
Question details
The user needs to automatically create a related child record in a status table when a new main record is saved in a Microsoft Access form.
- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- Developing a database form with a one-to-many relationship where closing the form or saving the main record should automatically generate a corresponding tracking record in a linked subform.
- Observed behavior
- The main record is successfully saved to the primary table when the form closes, but no corresponding child record is inserted into the related status table.
Ensure that your primary table and status table share a properly configured foreign key relationship (e.g., RevID) and that referential integrity is strictly enforced in your Database Tools relationship window.
Use the Form's After Insert Event via VBA
This is the most reliable method because the After Insert event triggers immediately after the main record is saved, ensuring the primary key (RevID) has been fully generated.
By binding an SQL INSERT statement to the After Insert event of the main form, you guarantee that a primary key has been successfully committed to the database. This prevents foreign key errors when creating the related subform record.
Right-click your main Access form in the Navigation Pane and select Design View.
Double-click the form selector box in the top-left corner to open the Property Sheet. Navigate to the Event tab.
Locate the 'On After Insert' property, click the ellipsis (...) button, and choose Code Builder.
In the VBA editor, type the execution code using CurrentDb.Execute to run an INSERT INTO SQL statement. For example: CurrentDb.Execute "INSERT INTO tblStatus (RevID) VALUES (" & Me.RevID & ")", dbFailOnError.
Add a line of code below your SQL execution to refresh the subform data so the new record becomes visible instantly: Me.YourSubformControlName.Requery.
Use a Custom Command Button for Saving
If users navigate your form primarily through custom interface buttons rather than standard Access navigation, you can sequence the parent and child saves sequentially in a single click event.
Looking for a Lightweight and Versatile Office Suite?
While Microsoft Access is a robust database tool, many day-to-day data management and documentation tasks can be handled more efficiently with a unified office suite. WPS Office is a highly compatible, free alternative for Word, Excel, and PowerPoint tasks.
- 1. Download the Installer: Visit the official WPS Office website and download the free installation package for your operating system.
- 2. Run the Setup: Open the downloaded executable and follow the quick on-screen instructions to install the suite.
- 3. Manage Your Data: Open WPS Spreadsheet to import, filter, and organize your tabular data or CSV exports effortlessly.
Frequently Asked Questions
Why isn't my subform updating automatically after inserting a new main record?
Even if the related record is successfully appended to the underlying table via VBA, the subform control on your screen will not reflect the changes until it is explicitly requeried. Add 'Me.YourSubformName.Requery' to the end of your VBA script.
Can I use Access Data Macros instead of VBA for this?
Yes. If you are using Access 2010 or newer, you can open the main table (tblMain) in design view and create an 'After Insert' Data Macro. Use the 'Create a Record' macro block targeting tblStatus to set the RevID automatically without using form-level VBA.
How do I handle required fields validation before the main record saves?
You should use the form's 'Before Update' event to validate data. If required fields are missing, you can display a message box and set 'Cancel = True'. This stops the record from saving, which in turn prevents the After Insert event from trying to create an orphaned subform record.
