Others

How to Highlight Invalid Combo Box Values in Microsoft Access

Chanuka GeekiyanageChanuka Geekiyanage Oct 21, 2026 869 views

Question details

The user wants to identify and visually format values entered into an Access combo box that do not exist within the predefined RowSource list.

How to Highlight Invalid Combo Box Values in Microsoft Access
Product
Microsoft Access
Device & OS
not provided
Scenario
Entering data via a form where a combo box allows manual input, and the user needs to quickly spot values that are not part of the standard reference list.
Observed behavior
When using a direct inequality comparison with DLookup, the condition only evaluates against the first item in the query, failing to recognize valid entries located further down the list.
Before you start

Ensure you have Design View access to your Microsoft Access database form and know the exact name of the table or query acting as your combo box's RowSource.

Solution 1Recommended

Use DLookup with Conditional Formatting

Apply a conditional formatting rule using the IsNull function combined with properly parameterized DLookup criteria to test if an entered value exists anywhere in the source list.

DLookup inherently returns the first matching value based on the criteria provided. To check if an entered value exists anywhere in the RowSource list, you must pass specific criteria testing whether any record matches the combo box value. If no match is found, DLookup returns Null, which can trigger your formatting rule.

1
Open Form in Design View

Right-click your form in the Access navigation pane and select Design View.

2
Access Conditional Formatting

Select the target combo box. Navigate to the Format tab on the ribbon and click on Conditional Formatting.

3
Set Expression

Click 'New Rule' and choose 'Expression Is' from the drop-down menu. Enter the formula: IsNull(DLookup("ColumnName", "RowSource", "ColumnName = '" & [ComboBoxName] & "'")). Replace ColumnName, RowSource, and ComboBoxName with your actual names.

4
Apply Formatting Style

Select the visual formatting you wish to apply, such as a red background or bold text, to flag the invalid entry. Click OK to save the rule.

Use DLookup with Conditional Formatting
Data Type Formatting: If your combo box column contains numeric values instead of text, remove the single quotes in the DLookup criteria: "ColumnName = " & [ComboBoxName].
Free Microsoft Office alternative

Manage Data Easily with WPS Office Spreadsheets

While Microsoft Access is powerful for complex database operations, many everyday data validation and tracking tasks can be accomplished more intuitively using WPS Office Spreadsheets. WPS is a free, lightweight suite that requires no programming knowledge to build robust drop-down lists.

  1. 1. Download and Install: Visit the official WPS Office website to download the free application to your device.
  2. 2. Open WPS Spreadsheets: Launch the Spreadsheets module to start managing your data records in an intuitive grid interface.
  3. 3. Apply Data Validation: Select a cell, navigate to the Data tab, and click Data Validation. Choose 'List' to create a controlled drop-down menu that automatically prevents invalid inputs.
Free and lightweight office suite requiring fewer system resourcesSeamless compatibility with Microsoft Excel (.xlsx) formatsIntuitive Data Validation tools to create and enforce drop-down lists instantlyFamiliar user interface ensuring zero learning curve
microsoft office alternative - wps office

Frequently Asked Questions

Why does my DLookup expression only check the first item in the combo box list?

DLookup inherently searches for and returns the first record it encounters in the domain. If you do not provide a specific third argument (the criteria) comparing a column to your entered value, it simply returns the very first item in the query, causing your condition to fail for other valid items.

What is the NotInList event in Microsoft Access?

The NotInList event triggers when a user types a value into a combo box that does not exist in its underlying RowSource. This event only fires if the combo box's Limit To List property is set to Yes, allowing developers to execute a macro or VBA code to handle the new entry.

Can I use Access conditional formatting to highlight empty combo box values?

Yes. You can select the combo box, open Conditional Formatting, create a new rule, and use the 'Field Value Is' condition set to 'Null', or use an 'Expression Is' condition with the formula IsNull([ComboBoxName]). Then, choose your desired highlighting style.