Others

How to Add a New Supplier from a Microsoft Access Form

Muhammad Talha Sep 29, 2026 868 views

Question details

The user needs a way to add a new supplier via a main Microsoft Access form and automatically update the supplier combo box list without losing their place on the current record.

How to Add a New Supplier from a Microsoft Access Form

Product: Microsoft Access
Device & OS: not provided
Scenario: Data Entry and Form Design
Observed behavior: The goal is to dynamically add new items to a combo box and requery it seamlessly without interrupting the primary data entry workflow.

Before you start

Ensure you have a Suppliers table set up and a main form containing your supplier combo box. It is highly recommended to back up your Access database before modifying form layouts or adding VBA event codes.

Solution 1Recommended

Use an Embedded Hidden Subform

Embed a subform within the main form that can be made visible via a button to add a new supplier, then automatically update the combo box using the After Insert event.

This method involves creating a smaller subform specifically for adding a supplier and placing it directly on your main form. You can keep it hidden until needed, ensuring a clean interface.

Create and Embed the Subform

Create a new subform linked to your Suppliers table. Embed this subform into your main form and set its Visible property to False in the Property Sheet.

Add a Button to Display the Subform

Place an Add New Supplier button next to the combo box. Add a VBA On Click event to set the subform's Visible property to True and move the focus to a new blank record.

Configure the After Insert Event

In the subform's After Insert event, write VBA code to requery the main form's combo box (e.g., Me.Parent.ComboBoxName.Requery). Then, add code to set the subform's Visible property back to False.

Verify Combo Box Row Source

Ensure that the combo box's Row Source query is configured properly so that it does not filter out or exclude the newly added supplier.

Seamless Experience: This method keeps the user entirely on the main form, preventing them from losing uncommitted data on the current record.

Solution 2

Use the Combo Box NotInList Event

Utilize the built-in NotInList event to detect when a user types an unknown supplier, prompting them to add it through a popup dialog box.

Free Microsoft Office alternative

Manage Supplier Lists Seamlessly with WPS Office

While Microsoft Access offers robust tools for database management, handling supplier lists and data entry forms can often be simplified. If writing VBA code or managing form events is too complex, WPS Spreadsheets offers a highly compatible, easy-to-use environment for managing relational lists using intuitive Data Validation rules.

1. Download and Install: Download WPS Office for free and install it on your device.
2. Organize Your Data: Open WPS Spreadsheets and create a master list of your suppliers in a dedicated worksheet.
3. Create a Drop-Down List: Go to the Data tab, select Data Validation, and choose 'List' to reference your suppliers, creating an instant, dynamic combo box equivalent.

Easily create dynamic drop-down lists using Data Validation without writing any code.Highly compatible with Microsoft Excel formats (.xlsx, .xls, .csv).Familiar user interface that requires zero learning curve.Free, lightweight, and fast alternative to complex database software.

microsoft office alternative - wps office

Frequently Asked Questions

How do I requery a combo box in Access using VBA?

You can requery a combo box by referencing its name and using the Requery method. For example, add 'Me.YourComboBoxName.Requery' to the relevant event procedure, such as a button click or an After Insert event.

Why isn't the NotInList event triggering when I type a new supplier?

The NotInList event will only trigger if the combo box's 'Limit To List' property is set to Yes. If it is set to No, Access will simply accept the typed text without firing the event.

How do I open a supplier form as a dialog box?

In VBA, use the DoCmd.OpenForm method and specify the WindowMode argument as acDialog. For example: DoCmd.OpenForm "frmAddSupplier", , , , , acDialog. This pauses the code in the main form until the dialog is closed.

Office Quick Solutions