VBA & Macro Problems

How to Open a Selected Access Record from a List Box in VBA

Khadija Khan Oct 12, 2026 870 views

Question details

The user needs to open and edit a specific record selected from a list box on an Access form, rather than the form defaulting to the first record.

How to Open the Selected Access Record from a List Box in Access VBA

Product: Microsoft Access
Device & OS: not provided
Scenario: Using an Edit button on an Access VBA form to open a second form based on the user's selection in a list box.
Observed behavior: Clicking the Edit button consistently opens the first record in the database instead of the specific record highlighted in the list box.

Before you start

Ensure your database table utilizes a unique, surrogate numeric primary key (such as an AutoNumber ID) rather than relying on text fields like personal names, which are prone to duplication.

Solution 1Recommended

Use a Primary Key and VBA WhereCondition

Bind a hidden primary key to the list box and use it to filter the target form via the DoCmd.OpenForm method.

To reliably identify and open a specific record, the list box must pass a unique identifier (Primary Key) to the target form. Relying on names or other non-unique fields can cause the database to pull incorrect or duplicate records.

Add a Primary Key to the Row Source

Open the list box properties in Design View. Under the Data tab, edit the Row Source query to ensure the table's AutoNumber Primary Key (e.g., PatientID) is the very first column in the query.

Set the Bound Column

Still in the Data tab of the list box properties, set the 'Bound Column' property to 1. This ensures the list box's value evaluates to your unique Primary Key.

Hide the Primary Key Column

Navigate to the Format tab in the list box properties. Set the 'Column Count' to include your new column, and adjust 'Column Widths'. Set the first value to 0 (e.g., 0";1.5";1.5") to hide the ID from the user while keeping it functional.

Apply VBA Filter Code to the Button

Right-click the Edit button, select Build Event -> Code Builder. In the On Click event, add the following code: Me.Dirty = False DoCmd.OpenForm "NameOfSecondForm", WhereCondition:="[NameOfPrimaryKeyColumn] = " & Me.NameOfListBoxControl

Use a Primary Key and VBA WhereCondition

Code Explanation: The 'Me.Dirty = False' command saves any pending edits on the current form before opening the new one. The 'WhereCondition' acts as a filter, matching the target form's ID to the list box's selected ID.

Free Microsoft Office alternative

Need a Reliable and Free Office Suite for Everyday Tasks?

While Microsoft Access is used for database management, most daily productivity tasks involve documents, spreadsheets, and presentations. WPS Office offers a lightweight, feature-rich, and free alternative to Microsoft Office that is perfect for handling all your standard files.

1. Visit the Official Website: Navigate to the official WPS Office website to access the latest secure download.
2. Download the Installer: Click the 'Free Download' button to get the lightweight installation package for your operating system.
3. Install and Start: Run the installer, follow the simple on-screen instructions, and launch WPS Office to immediately begin managing your documents and spreadsheets.

Seamlessly open, edit, and save Microsoft Word, Excel, and PowerPoint files (.docx, .xlsx, .pptx).Fully supports advanced Spreadsheet functions and VBA macros for complex data manipulation.Lightweight software architecture ensures fast startup even on older devices.Familiar tabbed user interface minimizes the learning curve and boosts productivity.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my Access list box always open the first record?

This happens if the target form is not being properly filtered by a unique identifier. Without a specific 'WhereCondition' in your VBA DoCmd.OpenForm statement, Access defaults to opening the target form at the very first record in its recordset.

Can I use a person's name as the bound column in an Access list box?

It is highly discouraged. Personal names are not strictly unique; multiple people can have the exact same name, which will cause Access to query the wrong record. Always use a unique surrogate numeric key, like an AutoNumber, as the bound column.

How do I hide the primary key column in an Access list box so users don't see it?

Open the list box properties in Design View, navigate to the Format tab, and find the 'Column Widths' property. Set the width of the first column (your primary key) to zero inches or centimeters (e.g., type 0";1";1"), which renders it invisible while keeping it functionally selectable in the background.

Excel & Data Problems