Others

How to Open an Access Form at a Newly Created DAO Record in VBA

Maira Mehtab Sep 20, 2026 868 views

Question details

A developer needs a VBA button to create a new database record in a related table using DAO, and then immediately open the associated Access form displaying that newly inserted record.

Product: Microsoft Access
Device & OS: not provided
Scenario: Creating a new database record via VBA scripting and transitioning the user interface to view or edit the new entry.
Observed behavior: By default, opening the form does not navigate to the specific record just created unless the new primary key (such as an AutoNumber) is captured and passed as a filter.

Before you start

Ensure your database table has a designated primary key (such as an AutoNumber field) and that you are familiar with writing and editing VBA code behind Microsoft Access forms.

Solution 1Recommended

Retrieve the New AutoNumber and Use DoCmd.OpenForm

The most robust method is to capture the new primary key generated by DAO and use it to filter the form upon opening using a WhereCondition.

When using DAO to insert a record, you can read the generated AutoNumber value immediately after the `.AddNew` method is called, but before or right after calling `.Update`.

Passing this retrieved ID into the `DoCmd.OpenForm` command ensures the form opens explicitly to the newly created record.

Initialize DAO and AddNew

In your VBA module, declare your DAO Recordset and execute the `rs.AddNew` method to initiate the creation of the new record.

Capture the Primary Key

Immediately after `AddNew` (or by navigating to `rs.LastModified` after `Update`), assign the value of the AutoNumber field to a variable, for example: `lngNewID = rs!YourIDFieldName`.

Update the Record

Call the `rs.Update` method to commit the insertion to the database table and save the new record.

Open the Associated Form

Execute the form opening command using the captured variable as the filter: `DoCmd.OpenForm "YourFormName", , , "[ID] = " & lngNewID`.

Syntax Verification: Ensure that the primary key field name in your WhereCondition string exactly matches the field name in the target form's Record Source.

Solution 2

Use Form Bookmark with LastModified

If the form is already bound to the same recordset being manipulated, you can navigate the open form to the newly added record using DAO bookmarks.

Free Microsoft Office alternative

Looking for a Lightweight Office Suite Alternative?

While Microsoft Access handles complex database management and VBA scripting, WPS Office provides a free, robust alternative for your standard office needs. Enjoy lightweight word processing, spreadsheets, and presentations with full Microsoft compatibility.

1. Download WPS Office: Visit the official WPS Office website and download the installer.
2. Install the Software: Run the installation file and follow the on-screen prompts to set up the suite on your device.
3. Open Your Files: Launch WPS Office and directly open your existing DOCX, XLSX, and PPTX files to continue working seamlessly.

Highly compatible with Microsoft Word, Excel, and PowerPoint filesLightweight design that runs smoothly on most operating systemsFree to use with a familiar, tabbed user interfaceSeamless migration with no learning curve required

microsoft office alternative - wps office

Frequently Asked Questions

Why does my Access form not show the newly created record?

The form may not be filtered to the correct record, or the underlying recordset hasn't been refreshed to include the new entry. Using the WhereCondition parameter in DoCmd.OpenForm is the best way to explicitly target the new record.

How do I get the AutoNumber of a new DAO record?

You can retrieve the AutoNumber immediately after the `.AddNew` command but before `.Update`, or by moving the recordset to the `.LastModified` bookmark after the update is committed.

What is the correct syntax for the WhereCondition in DoCmd.OpenForm?

The syntax is a string expression, such as `"[ID] = " & lngNewID`, where `[ID]` is the primary key field name in your form's data source, and `lngNewID` is the VBA variable storing the new record's ID.

Office Quick Solutions