Displaying Custom Validation Messages in MS Access

Although setting the Validation Rule property does the job as far as validating your data, it does not present a very user-friendly message when you enter invalid data. The figure below shows you an example of a not-very-friendly message for a validation rule. In the example below, the Validation Rule property for the State field in the Clients table is set to AZ or TX. If the data entered into the State field is not one of these values, the message below appears.

To present a more polite message, you need to set another property called Validation Text for the field. This property is located right below the Validation Rule property in the properties for the field. By typing the message you want displayed in the Validation Text property, you can make Access display that text instead of Access's more cryptic message. You can see both the validation text setting and the resulting message displayed in the Slide Show below.

Validation rules for text allow you to check if text has a certain length or contains certain characters or sequences of characters.
Access provides an expression language that allows you to express such rules. This expression language contains elements of regular expressions. Regular expressions, or regex in short, is a language that is used in computer programming to match text against certain criteria. Expressions like the ones you will see on this page are not used just in validation rules. They are used in other parts of Access as well, most importantly in SQL queries to match records against certain criteria.

There are a couple of things to keep in mind when specifying your Validation Text property. The first thing is to make the message friendly, because people may be frustrated already at putting in erroneous data. The other is to be as helpful as possible. For instance, in the validation text just displayed, the users are informed that they need to supply AZ or TX for the state requested.
This field would function better by using a lookup field, which naturally limits input using a Combo Box or List Box control. Lookup fields are discussed later in the module. In the next lesson, you will learn how to use the Input Mask Wizard to specify data input formats.

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