Others

How to Open or Create Related MS Access Records with VBA

Elise Williams Oct 10, 2026 868 views

Question details

The user needs to use VBA in Microsoft Access to check if a related assessment record exists for a current grant, open it if found, or create and populate a new record if it does not exist.

How to Open or Create Related MS Access Records with VBA

Product: Microsoft Access
Device & OS: not provided
Scenario: Navigating between related forms and passing data to new records using a VBA button procedure, specifically avoiding overly complex nested subforms.
Observed behavior: The goal is to automate the process of filtering an existing record or initializing a new form in add mode with inherited values from the parent form.

Before you start

Before modifying your database logic, ensure you have a backup of your Access file and verify that the control names on both your main form and related form match exactly what you will use in your VBA code.

Solution 1Recommended

Use VBA and DCount to Conditionally Open or Add a Record

This method uses the DCount function to check for an existing related record. It opens the target form filtered to the specific record if found, or in data entry mode if not.

Using a VBA button procedure allows for clean navigation without overcrowding your main form with nested subforms. This is especially useful when your forms are already complex.

Open the Form in Design View

Right-click your main form (e.g., Grants form) in the navigation pane and select Design View. Add a Command Button to the form from the design ribbon.

Add an On Click Event Procedure

Select the new button, open the Property Sheet, navigate to the Event tab, and choose '[Event Procedure]' for the On Click event. Click the ellipsis (...) to open the VBA editor.

Write the DCount Logic

In the VBA editor, write an If statement using DCount to check if the related ID exists in the target table. For example: `If DCount("GrantID", "AssessmentsTable", "GrantID = " & Me.GrantID) > 0 Then`.

Open the Existing Record

Inside the If block, use `DoCmd.OpenForm` to open the related form with a where-condition matching your ID: `DoCmd.OpenForm "AssessmentForm", , , "GrantID = " & Me.GrantID`.

Open a New Record and Pass Values

In the Else block, use `DoCmd.OpenForm "AssessmentForm", , , , acFormAdd` to open it in add mode. Then, assign the main form's values to the new form's controls, such as `Forms!AssessmentForm!GrantID = Me.GrantID`.

Use VBA and DCount to Conditionally Open or Add a Record

Debugging Tip: If you encounter a compile error, click 'Debug' in the top menu of the VBA editor and select 'Compile Database' to quickly locate typos or missing references in your code.

Solution 2

Use a Subform for Direct Data Linking

If you prefer a no-code solution and your form layout permits it, you can use a Subform to link the assessment records directly to the grants form.

Free Microsoft Office alternative

Need a Lightweight Office Alternative?

Microsoft Access is a powerful database tool, but for many users, advanced spreadsheet functions can handle data tracking just as effectively. WPS Office offers a free, lightweight, and highly compatible alternative to Microsoft Office suites, allowing you to manage complex data seamlessly.

1. Download WPS Office: Visit the official WPS website and download the free WPS Office suite for your operating system.
2. Install the Software: Run the installer and follow the simple on-screen instructions to set up the software.
3. Manage Your Data: Launch WPS Spreadsheet to import your existing Excel files or create new tables to track related data without complex coding.

Completely free and lightweight Office suiteHigh compatibility with Microsoft Excel, Word, and PowerPoint formatsAdvanced data analysis and linking tools in WPS SpreadsheetFamiliar tabbed interface for a seamless migration experience

microsoft office alternative - wps office

Frequently Asked Questions

How do I fix a compile error when adding VBA code in Access?

Compile errors usually happen due to misspelled control names, missing references, or incorrect syntax. Open the VBA Editor, click 'Debug' on the top menu, and select 'Compile Database'. This will highlight the exact line causing the issue so you can correct your DCount or DoCmd syntax.

Can I pass multiple values to a new form using VBA?

Yes. After opening the target form in Add mode (using acFormAdd), you can assign values to multiple controls on the new form sequentially. For example: Forms!NewForm!Field1 = Me.Field1, followed by Forms!NewForm!Field2 = Me.Field2.

Why should I use VBA instead of a subform for related records?

While subforms are great for simple one-to-many relationships, they can clutter the screen and degrade performance if your main form already contains multiple nested subforms. VBA allows you to keep forms visually separate and only load the related data into memory when a user explicitly requests it.

Office Quick Solutions