MS Access Form Field Validation

What is a “Validation Rule”?

Table Design - Data Types

Microsoft Access uses validation rules to keep data entry consistent for each field. Some of the validation is done by default during the database table design, when fields are given their data type, size, etc. Any attempt by a user to enter the wrong type of data or exceed the field size will result in an error message.

The screenshot to the right illustrates default data validation in an MS Access table. Each field name has a designated "data type," and each data type has other "rules" (field size, format, etc.).

User-Selected Validation Rules

While still in the table design view, we can also set specific values or rules for our fields. For instance we specify that a field cannot be left blank (i.e., “required”) and that the entry for a number and date fields must fall within certain parameters . We can also specify that one field cannot be greater than another corresponding field (e.g., start and end dates), and so on. We can also design “lookup” fields, specify what goes into the field, and limit the field to the list we specify.

The screenshots below are examples of field validation rules that can be designed during database table design:

Date Validation Rule in Table Design

Lookup data validation in Table Design

How Validation Rules Affect Data Entry in Forms

Data validation rules we specify in the table design are also in the fields in forms based on the table. Using the two examples in the previous section (date and lookup data validationrules), we can see what error messages would result on a form if the user tried to enter data that violated the "rules":

Date Validation Rule in Form View

Lookup Validation in Form View

How to Add a Validation Rule Directly to a Form

Sometimes we need to add a field validation rule directly to a form, bypassing the table design for reasons that apply to the form’s unique design and use. We can add a validation rule to a form by doing the following steps:

  • Open the form in the design view.
  • Click on the field where the new validation rule will be applied.
  • Click on the Property Sheet Icon in the Tools Section.
  • Click on the Data Tab.
  • Enter the Validation Rule in the fifth row, second column.
  • Enter the Validation Text (i.e., the error message you want to appear) in the next row.

The following screenshots illustrate the design steps and the error message that would result in the form if the rule (<100) were violated on data entry:

Form Validation Rule

Data Validation - Table Currency Field

Read more about creating data validation rules in fields at:

Microsoft Office Help: Create a validation rule to validate data in a field