Check Box Control, Combo Box Control, List Box Control, Option Button Control, Option Group Control, Table Fields, Text Box Control, Toggle Button Control.
You can use the ValidationRule property to specify requirements for data entered into a field or control. When data is entered that violates the ValidationRule setting, you can specify the message to be displayed to the user by setting the ValidationText property.
Note The ValidationRule and ValidationText properties do not apply to check box, option button, or toggle button controls when they are in an option group. They apply only to the option group itself.
Enter an expression for the ValidationRule property setting and text for the ValidationText property setting. The maximum length for each property setting is 255 characters.
For controls, you can set the property to any valid expression. For field and record validation rules, the expression can’t contain user-defined functions, domain or aggregate functions, the CurrentUser or Eval function, or references to forms, queries, or tables. In addition, field validation rules can’t contain references to other fields. For records, expressions can include references to fields in that table.
You can set the ValidationRule and ValidationText properties in:
Note For table fields and records, you can also set these properties in Visual Basic by using the Data Access Objects ValidationRule property.
Microsoft Access automatically validates values based on a field’s data type; for example Microsoft Access doesn’t allow text in a numeric field. You can set more specific rules using the ValidationRule property.
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.
For example, when a record is added for a new employee, you can enter a ValidationRule property requiring that the employee Start Date field fall between the company’s founding date and the current date. If the date entered isn’t in this range, you can display the ValidationText property message: “Start date is incorrect.”
If you create a control by dragging a field from the field list, the field’s validation rule remains in effect, although it isn’t displayed in the control’s ValidationRule property box in the property sheet. This is because a field’s validation rule is inherited by a control bound to that field.
Control, field, and record validation rules are applied as follows:
The following table contains expression examples for the ValidationRule and ValidationText properties.
ValidationRule property |
ValidationText property |
<> 0 |
Entry must be a non-zero value. |
> 1000 Or Is Null |
Entry must be blank or greater than 1000. |
Like "A???? " |
Entry must be 5 characters and begin with the letter A. |
>= #1/1/95# And <#1/1/96# |
Entry must be a date in 1995. |
DLookup("[CustomerID] ", "Customers", " [CustomerID] = Forms![Customers]![CustomerID] ") Is Null |
Entry must be unique CustomerID (form-level validation only). |
If you create a validation rule for a field, Microsoft Access doesn’t normally allow a Null value to be stored in the field. If you want to allow a Null value, add Is Null to the validation rule, as in “<> 8 Or Is Null”.
You can’t set field or record validation rules for tables created outside Microsoft Access (for example, dBASE, Paradox, and SQL Server). For these kinds of tables, you can create validation rules for controls only.
AllowZeroLength Property; Required Property; ValidationRule, ValidationText Properties (Microsoft Office 95 Data Access Reference).
The following example creates a validation rule for a Field that allows only values over 65 to be entered. If a number less than 65 is entered, a message is displayed. The properties are set using the SetFieldValidation function.
Dim strTblName As String, strFldName As StringstrValidRule As String, strValidText As String, intX As Integer= "Customers"= "Age"= ">= 65"= "Enter a number greater than or equal to 65."= SetFieldValidation(strTblName, strFldName, _ strValidRule, strValidText) SetFieldValidation(strTblName As String, _ strFldName As String, strValidRule As String, _ strValidText As String) As Integer Dim db As Database, td As TableDef, fld As Field Set db = CurrentDb Set td = db.TableDefs(strTblName) Set fld = td.Fields(strFldName) fld.ValidationRule = strValidRule fld.ValidationText = strValidTextFunction
The next example uses the SetTableValidation function to set record level validation to ensure that the value in the EndDate field comes after the value in the StartDate field.
Dim strTblName As String, strValidRule As StringstrValidText As StringintX As Integer= "Employees"= "EndDate > StartDate"= "Enter an EndDate that is later than the StartDate."= SetTableValidation(strTblName, strValidRule, strValidText) SetTableValidation(strTblName As String, _ strValidRule As String, strValidText As String) As Integer Dim db As Database, td As TableDef Set db = CurrentDb Set td = db.TableDefs(strTblName) td.ValidationRule = strValidRule td.ValidationText = strValidTextFunction