ValidationRule Property

Applies To

Dynaset-Type Recordset Object, Field Object, Recordset Object, Snapshot-Type Recordset Object, Table-Type Recordset Object, TableDef Object.

Description

Sets or returns a value that validates the data in a field as it's changed or added to a table. For an object not yet appended to the Fields collection, this property is read/write. See Remarks for the more specific read/write characteristics of this property.

Settings and Return Values

The settings or return values is a string expression that describes a comparison in the form of an SQL WHERE clause without the WHERE reserved word. The data type is String.

Remarks

The ValidationRule property determines whether or not valid data is in the associated field. If the data is not legal, a trappable run-time error occurs. The returned error message is the text of the ValidationText property, if specified, or the text of the expression specified by ValidationRule.

For a Field object, use of the ValidationRule property depends on the object that contains the Fields collection that the Field object is appended to, as the following table shows.

Object appended to	Usage

Index	Not supported
QueryDef	Read-only
Recordset	Read-only
Relation	Not supported
TableDef	Read/write

For a Recordset object, use of the ValidationRule property is read-only. For a TableDef object, use of the ValidationRule property depends on the status of the TableDef object, as the following table shows.

TableDef	Usage

Base table	Read/write
Attached table	Read-only

Validation is supported only for databases that use the Microsoft Jet database engine.

The string expression specified by the ValidationRule property of a Field object can refer only to that Field. The expression can't refer to user-defined functions, SQL aggregate functions, or queries. To set a Field object's ValidationRule property when its ValidateOnSet property setting is True, the expression must successfully parse (with the field name as an implied operand) and evaluate to True. If its ValidateOnSet property setting is False, the ValidationRule property setting is ignored.

The ValidationRule property of a Recordset or TableDef object can refer to multiple fields in that object. The restrictions noted earlier in this topic for the Field object apply.

For a table-type Recordset object, the ValidationRule property takes the value of the ValidationRule property setting of the TableDef object that you use to create the table-type Recordset object.

For a TableDef object based on an attached table, the ValidationRule property takes the value of the underlying base table. If the underlying base table doesn't support validation, the value of this property is a zero-length string ("").

See Also

ValidateOnSet Property, ValidationText Property.

Specifics (Microsoft Access)

Use the data access ValidationRule property to set validation rules for a Field, Recordset, or TableDef object from Visual Basic.

You can also set validation rules for a field or control in the Microsoft Access user interface. Set the Microsoft Access ValidationRule property for a field or a table in table Design view. Set the ValidationRule property of a control in the property sheet in form Design view.

In Microsoft Access, the string expression specified by the ValidationRule property of a Field object can't refer to user-defined functions, domain aggregate functions, SQL aggregate functions, the CurrentUser function, the Eval function, or queries.

Example (Microsoft Access)

The following example sets the ValidationRule and ValidationText properties for two fields in an Order Details table. Once these properties have been set, you can view them in table Design view as well.

Sub SetValidation()
    Dim dbs As Database, tdf As TableDef
    Dim fldQuantity As Field, fldDiscount As Field
    
    ' Return Database variable that points to current database.
    Set dbs = CurrentDb
    Set tdf = dbs.TableDefs![Order Details]
    Set fldQuantity = tdf.Fields!Quantity
    Set fldDiscount = tdf.Fields!Discount
    ' Set ValidationRule and ValidationText.
    fldQuantity.ValidationRule = ">= 4"
    fldQuantity.ValidationText = "Quantity must be four or more items."
    fldDiscount.ValidationRule = "Between .05 and .30"
    fldDiscount.ValidationText = "Discount must be between 5% and 30%."
End Sub