How to Check if a Product Code Exists in Microsoft Access
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.

- 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.
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).
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.
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.
Choose an event such as 'Before Update', click the ellipsis (...) button next to it, and select 'Code Builder' to launch the VBA editor window.
Type the validation code: If IsNull(DLookup("ProductCode", "tblProducts", "ProductCode = Forms!frmProducts!ProductCode")) Then
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.

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. Download the Installer: Visit the official WPS website and click the free download button for your operating system.
- 2. Install WPS Office: Run the downloaded executable file and follow the on-screen prompts to complete the quick installation.
- 3. Start Creating: Launch WPS Writer, Spreadsheet, or Presentation to immediately start managing your files and data.

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.




