Others

How to Check if a Product Code Exists in Microsoft Access

Algirdas JasaitisAlgirdas Jasaitis Nov 2, 2026 869 views

Question details

The user needs to verify if a specific product code is already present in a Microsoft Access database table using VBA or macros.

How to Check Whether a Product Code Exists in Microsoft Access
Product
Microsoft Access
Device & OS
not provided
Scenario
Preventing duplicate product code entries when adding new records via a data entry form.
Observed behavior
Attempting to assign a SQL SELECT statement directly to a variable fails; a specialized function like DLookup is required to validate the product code successfully.
Before you start

Ensure you have your Access database open in Design View and know the exact names of your target table (e.g., tblProducts) and the form where the product code is entered (e.g., frmProducts).

Solution 1Recommended

Use the DLookup Function in VBA

The DLookup function is the standard and most efficient way to search for a specific field value based on criteria in Microsoft Access.

Unlike SQL SELECT statements which return a recordset, DLookup returns a single value. This makes it perfect for checking existence. If the queried record doesn't exist, DLookup returns a Null value, which can be easily verified using the IsNull function.

1
Open your Form's VBA Module

In Microsoft Access, open your form in Design View, select the Product Code text box, and navigate to the Event tab in the Property Sheet on the right side of the screen.

2
Add an Event Procedure

Choose an event such as 'Before Update', click the ellipsis (...) button next to it, and select 'Code Builder' to launch the VBA editor window.

3
Insert the DLookup Code

Type the validation code: If IsNull(DLookup("ProductCode", "tblProducts", "ProductCode = Forms!frmProducts!ProductCode")) Then

4
Handle the Result

Complete the IF statement by adding a MsgBox to indicate an available code (when the result is Null) and a warning message for a duplicate code (when the result is Not Null). Ensure you use 'Forms!' and not 'Form!' in your criteria string reference.

Use the DLookup Function in VBA
Syntax Tip: Always verify your table name, field names, and form names match exactly what is in your database to prevent runtime errors.
Free Microsoft Office alternative

Looking for a lightweight alternative to Microsoft Office? Try WPS Office

While Microsoft Access is designed specifically for complex relational databases, WPS Office provides a free, lightweight, and highly compatible suite for all your daily document, spreadsheet, and presentation needs. Enjoy a familiar interface and seamless migration from MS Office.

  1. 1. Download the Installer: Visit the official WPS website and click the free download button for your operating system.
  2. 2. Install WPS Office: Run the downloaded executable file and follow the on-screen prompts to complete the quick installation.
  3. 3. Start Creating: Launch WPS Writer, Spreadsheet, or Presentation to immediately start managing your files and data.
Fully compatible with Microsoft Word, Excel, and PowerPoint formats (.docx, .xlsx, .pptx).Manage spreadsheet data seamlessly as a lightweight alternative to database tracking.Lightweight installation that runs smoothly on older or less powerful computers.Built-in PDF reader and editor for comprehensive document management.Familiar ribbon interface ensures zero learning curve for transitioning users.
microsoft office alternative - wps office

Frequently Asked Questions

Why can't I assign a SQL SELECT statement directly to a variable in Access VBA?

A SQL SELECT statement in Microsoft Access returns a Recordset object containing rows and columns, not a simple scalar value. To assign a single value to a string or integer variable, you must either open a Recordset in VBA or use domain aggregate functions like DLookup.

What happens if DLookup doesn't find a matching product code?

If no record satisfies the criteria specified in the third argument of the DLookup function, it returns a Null value. This is why wrapping the function in IsNull() is the standard method for checking if a record exists.

Why do I get an error saying 'Microsoft Access cannot find the form'?

This error usually occurs because of incorrect syntax in the criteria argument. Ensure you are using the plural 'Forms!' collection (e.g., Forms!frmProducts!ProductCode) rather than the singular 'Form!' when referencing form controls in your DLookup string.

Can I check for multiple conditions using DLookup?

Yes, you can include multiple criteria by concatenating them with 'AND' or 'OR' in the third argument of the DLookup function. The syntax follows standard SQL WHERE clause formatting, allowing for complex lookups.