ValidationRule and ValidationText Properties 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