ValidationRule Property

Applies To Dynaset-Type Recordset object, Field object, Forward-Only-Type Recordset 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 (Microsoft Jet workspaces only).

Settings and Return Values

The settings or return values is a String that describes a comparison in the form of an SQL WHERE clause without the WHERE reserved word. 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.

Remarks

The ValidationRule property determines whether or not a field contains valid data. If the data is not valid, 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 to which the Field object is appended.

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
Linked 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 inherits 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 linked table, the ValidationRule property inherits the ValidationRule property setting of the underlying base table. If the underlying base table doesn't support validation, the value of this property is a zero-length string (" ").

Note If you set the property to a string concatenated with a non-integer value, and the system parameters specify a non-U.S. decimal character such as a comma (for example, strRule = "PRICE > " & lngPrice, and lngPrice = 125,50), an error will result when your code attempts to validate any data. This is because during concatenation, the number will be converted to a string using your system's default decimal character, and Microsoft Jet SQL only accepts U.S. decimal characters.

See Also ValidateOnSet property, ValidationText property.

Specifics (Microsoft Access)

Use the DAO ValidationRule property to set validation rules for a Field, Recordset, or TableDef object by using 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 by using 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

This example creates a new Field object in the specified TableDef object and sets the ValidationRule and ValidationText properties based on the passed data. It also shows how the ValidationRule and ValidationText properties are used during actual data entry. The SetValidation function is required for this procedure to run.

Sub ValidationRuleX()

    Dim dbsNorthwind As Database
    Dim fldDays As Field
    Dim rstEmployees As Recordset
    Dim strMessage As String
    Dim strDays As String
    Dim errLoop As Error

    Set dbsNorthwind = OpenDatabase("Northwind.mdb")
    ' Create a new field for the Employees TableDef object
    ' using the specified property settings.
    Set fldDays = _
        SetValidation(dbsNorthwind.TableDefs!Employees, _
        "DaysOfVacation", dbInteger, 2, "BETWEEN 1 AND 20", _
        "Number must be between 1 and 20!")
    Set rstEmployees = _
        dbsNorthwind.OpenRecordset("Employees")

    With rstEmployees

        ' Enumerate Recordset. With each record, fill the new
        ' field with data supplied by the user.
        Do While Not .EOF
            .Edit
            strMessage = "Enter days of vacation for " & _
                !FirstName & " " & !LastName & vbCr & _
                "[" & !DaysOfVacation.ValidationRule & "]"

            Do While True
                ' Get user input.
                strDays = InputBox(strMessage)
                If strDays = "" Then
                    .CancelUpdate
                    Exit Do
                End If
                !DaysOfVacation = Val(strDays)

                ' Because ValidateOnSet defaults to False, the
                ' data in the buffer will be checked against the
                ' ValidationRule during Update.
                On Error GoTo Err_Rule
                .Update
                On Error GoTo 0

                ' If the Update method was successful, print the
                ' results of the data change.
                If .EditMode = dbEditNone Then
                    Debug.Print !FirstName & " " & !LastName & _
                        " - " & "DaysOfVacation = " & _
                        !DaysOfVacation
                    Exit Do
                End If

            Loop

            If strDays = "" Then Exit Do
            .MoveNext
        Loop

        .Close
    End With

    ' Delete new field because this is a demonstration.
    dbsNorthwind.TableDefs!Employees.Fields.Delete _
        fldDays.Name
    dbsNorthwind.Close
    
    Exit Sub
    
Err_Rule:

    If DBEngine.Errors.Count > 0 Then
        ' Enumerate the Errors collection.
        For Each errLoop In DBEngine.Errors
            MsgBox "Error number: " & _
                errLoop.Number & vbCr & _
                errLoop.Description
        Next errLoop
    End If
    
    Resume Next

End Sub

Function SetValidation(tdfTemp As TableDef, _
    strFieldName As String, intType As Integer, _
    intLength As Integer, strRule As String, _
    strText As String) As Field

    ' Create and append a new Field object to the Fields
    ' collection of the specified TableDef object.
    Set SetValidation = tdfTemp.CreateField(strFieldName, _
        intType, intLength)

    SetValidation.ValidationRule = strRule
    SetValidation.ValidationText = strText
    tdfTemp.Fields.Append SetValidation

End Function

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 reference to current database.
    Set dbs = CurrentDb
    ' Return referent to Order Details table.
    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%."
    Set dbs = Nothing
End Sub