About restricting or validating data

Microsoft Access provides a variety of ways to control how users enter data into your database. For example, you can limit the data that a user can enter into a field by defining a validation rule for that field. If the data that a user enters into the field breaks the rule, Access will display a message telling the user what kind of entries are allowed. Another method of controlling data entry is to create an input mask to restrict the kind of values that can be entered in positions across the field. You can perform these simple forms of validation and restriction by setting properties for fields in tables, or by setting properties for controls on forms.

In most cases, it is preferable to define data validation and restriction by setting a field's properties in table Design view. That way, whenever you use that field in a form, the field's validation rule and other properties will apply to data entry performed using the form.

However, if the data entered into a control on a form isn't bound to a field in a table, and you need to restrict or validate data entry, you must define those properties in the form. Additionally, there are situations where you must use macros or Microsoft Visual Basic for Applications code in conjunction with a form to perform more complex validation. For example, you might want to be able to override your validation rule or compare values from different tables.

Using data types and field properties to restrict or validate data in tables

Microsoft Access provides a variety of ways to control how data is entered in fields in your table.

The first thing you should consider is whether a field has an appropriate data type. For example, Date/Time fields only allow entry of valid date and time formats. If you are currently using a Text field for such information, it may be appropriate to change the field to a Date/Time field.

You can set field properties that provide further control over the values that can be entered into the field:

• For Number fields, you can choose from a variety of field sizes to control the kind and range of values that can be entered.
• For Text fields, you can set the maximum number of characters that can be entered.
• For all but AutoNumber fields (which generate their own data), you can require that data be entered in the field.
• For Text, Date/Time, and Number fields, you can define an input mask to provide blanks to fill in and can control what values can be entered in those blanks.
• For all but OLE Object fields, you can prevent duplicate values from being entered into a field or combination of fields.

Validation rules

Validation rules allow you to define a rule to limit what will be accepted. They are enforced whenever you add or edit data, whether it is through table Datasheet view, a form bound to the table, an append query, an update query, Visual Basic for Applications code, or by importing data from another table. You can define two kinds of validation rules: field validation rules and record validation rules.

• A field validation rule is used to check the value entered into a field as the user leaves the field. For example, you could define ">=10 And <=100" as the validation rule for a Number field to allow only values from 10 to 100 to be entered.
• A record validation rule controls when an entire record can be saved. Unlike field validation rules, record validation rules can refer to other fields in the same table. This makes them useful when you want to compare values in different fields. For example, you could define "[RequiredDate]<=[OrderDate]+30" as the validation rule for an Orders table. This rule would make sure that the date entered into the RequiredDate field is within 30 days of the date in the OrderDate field.

When a field or record validation rule is broken, Access displays a message that informs the user how to properly enter data.

Using a macro or event procedure for data validation

In most cases, you can verify that data is entered correctly into a control on a form by setting the ValidationRule property for the control, or by setting record or field validation rules in the underlying table to which the control is bound. Macros and event procedures provide additional power and flexibility for more complex validation.

Use a macro or an event procedure for data validation if:

• Your validation rule involves conditions for more than one value on the form. For example, you might want to verify that any two of three fields are filled in before saving a record.
• You want to display different error messages for different types of errors in the field. For example, if the value entered is greater than the acceptable range, you can display one message, and if it's less than the acceptable range, you can display a different message.
• You want the user to be able to override your validation rule. In this case, you can display a warning message asking the user to confirm the data.
• The validation involves references to controls on other forms or contains a function.
• You have a validation rule that you can use for more than one form, and you want the convenience of defining the rule once and then referring to it in each form.