Table Design   «Prev  Next»
Lesson 3 Displaying custom validation messages
Objective Set the Validation Text property to display a custom message in MS Access

Displaying Custom Validation Messages in MS Access

A validation rule can prevent invalid values from being saved, but the default error prompt is often vague and unhelpful. For example, suppose the State field in a Clients table should allow only two values: "AZ" or "TX". If someone enters anything else, Access blocks the entry and shows a generic message that doesn’t clearly explain what to type next.
Access Invalid Data message
Access Invalid Data message

To make the prompt more user-friendly, set the Validation Text property for the field. It appears directly below Validation Rule in the field’s property sheet (Table Design view). Whatever you type into Validation Text is what Access displays when the rule fails—so you can provide a clear, polite, “here’s what to do” message.

This is how the setting looks in Validation Text.
Clients : Table
Fields 
| Field Name | Data Type    | Description |
| ---------- | ------------ | ----------- |
| City       | Short Text   |             |
| State      | Short Text   |             |
| Zip        | Short Text   |             |

Field Properties (State field selected)
General Tab 
| Property            | Value                                      |
| ------------------- | ------------------------------------------ |
| Field Size          | 50                                         |
| Format              |                                            |
| Input Mask          |                                            |
| Caption             |                                            |
| Default Value       |                                            |
| Validation Rule     | In("AZ","TX")                              |
| Validation Text     | Please enter "AZ" or "TX" for the state.   |
| Required            | No                                         |
| Allow Zero Length   | No                                         |
| Indexed             | No                                         |
| Unicode Compression | No                                         |
1) This is how the setting looks in Validation Text.

Valid text Good
2) This is what the error message looks like.

Setting validation text

Access validation rules use the Access expression language (the same expression system used in queries, calculated controls, and conditional formatting). For text fields, rules often check:
  • Allowed values (for example: specific state codes)
  • Length (minimum/maximum characters)
  • Patterns (for example: a ZIP code shape like 12345 or 12345-6789)
  • Required characters (for example: an “@” in an email address)
While this is not full regex, you can accomplish many pattern checks using operators like Like and functions such as Len(), InStr(), and Is Null.
When you write Validation Text, keep two goals in mind:
  1. Be friendly — users are already interrupted by an error, so keep the message calm and respectful.
  2. Be specific — tell them exactly what valid input looks like.
For the State example, “Invalid data” is not actionable. A better message is: Please enter "AZ" or "TX" for the state.

Practical tips for modern Access (Microsoft 365)

  • Prefer In("AZ","TX") for readability. It’s easier to maintain than "AZ" Or "TX", and it scales cleanly if you add more values.
  • Differentiate field vs. table rules:
    • Field validation checks a single field (like State).
    • Table validation checks relationships across fields (for example, requiring EndDate >= StartDate).
    You can also define table-level Validation Text to explain cross-field failures.
  • Use forms for user experience. Table validation protects the data, but forms are where you can guide users: combo boxes, input masks, and event-driven messages (like a form’s validation or “before update” logic) help prevent errors before the save attempt.
  • Don’t rely on table “Lookup” fields for data integrity. A common best practice is: store a foreign key to a related lookup table (e.g., States) and use a combo box in the form to present friendly choices. That keeps the table schema clear and portable while still giving users a drop-down.
  • Combine validation tools:
    • Validation Rule to enforce allowed values
    • Required to prevent missing values
    • Input Mask to guide formatting (covered in the next lesson)
    • Referential Integrity to ensure referenced values exist
In short: validation rules protect your tables; validation text protects your users from confusion. Use both. In the next lesson, you’ll learn how to apply the Input Mask Wizard to enforce consistent input formats.

Validating Data Fields - Quiz

Click the Quiz link below to answer a few questions about validating data in fields.
Validating Data Fields - Quiz

SEMrush Software 3 SEMrush Banner 3