How to Highlight Invalid Combo Box Values in Microsoft Access
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.

- 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.
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.
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.
Right-click your form in the Access navigation pane and select Design View.
Select the target combo box. Navigate to the Format tab on the ribbon and click on Conditional Formatting.
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.
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.

Enforce the Limit To List Property
Prevent users from entering invalid values altogether by utilizing the Limit To List property, ensuring data integrity without needing conditional formatting.
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. Download and Install: Visit the official WPS Office website to download the free application to your device.
- 2. Open WPS Spreadsheets: Launch the Spreadsheets module to start managing your data records in an intuitive grid interface.
- 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.

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.




