databasedev.co.uk - database solutions and downloads for microsoft access

Microsoft Access Data Validation

Validating Data in Microsoft Access:

Validating data in Microsoft Access allows you to check data whilst it is being entered by the database user and will enable you to not save the data if it breaks any validation rules or routines. It is easy for an end user to make a mistake when they are entering data or even to leave out important details that must be entered, so we need to check this data when it is being entered into the database. Allowing invalid data to be entered into the database ensures that the database and the data available will become less valuable and harder for the user to use.

Microsoft Access provides several methods on how to check the data being entered, and provides the following validation features:

  • Microsoft Access Data Types: Using the different data types available in Microsoft Access is one of the most simple forms of data validation. Access will not allow you to store the incorrect type of data in a field if the data type is not appropriate for the field's pre-defined data type.

    Articles on this topic:
  • Required Property: You can use the Required property to specify whether a value is required in a field, ensuring that the field is not left blank. If the required property is set to yes, and a user attempts to leave a field blank or removes a value from a required field when trying to save the record, Microsoft Access will display an error message.
  • InputMask Property: You can use the Input Mask property to make data entry easier and to control the values users can enter in a text box. Input masks are helpful for data-entry operations such as an input mask for a Phone Number field that shows you exactly how to enter a new number: (___) ___-____. Microsoft Access will only allow you to enter the characters that will fit the specified values in the input mask.
  • Validation Rule Property: You can use the ValidationRule property to specify requirements for data entered into a record, field, or control. When data is entered that violates the Validation Rule setting, you can use the Validation Text property to specify the message to be displayed to the user. If you set the ValidationRule property but not the ValidationText property, Microsoft Access displays a standard error message when the validation rule is violated. If you set the ValidationText property, the text you enter is displayed as the error message.
  • BeforeUpdate Event: Creating an event procedure will allow you to provide more complex field validation, that is usually not available from writing validation expressions for. Using the BeforeUpdate event procedure, you will need to specify the validation routines that you wish to apply to the data and if the rules are not adhered to you will need to display the validation message and cancel the updates to the data.

    Articles on the BeforeUpdate topic: