ValidationRule, ValidationText Properties Example

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 by using the SetFieldValidation function.

Dim strTblName As String, strFldName As String
Dim strValidRule As String
Dim strValidText As String, intX As Integer

strTblName = "Customers"
strFldName = "Age"
strValidRule = ">= 65"
strValidText = "Enter a number greater than or equal to 65."
intX = SetFieldValidation(strTblName, strFldName, _
    strValidRule, strValidText)

Function SetFieldValidation(strTblName As String, _
    strFldName As String, strValidRule As String, _
    strValidText As String) As Integer

    Dim dbs As Database, tdf As TableDef, fld As Field

    Set dbs = CurrentDb
    Set tdf = dbs.TableDefs(strTblName)
    Set fld = tdf.Fields(strFldName)
    fld.ValidationRule = strValidRule
    fld.ValidationText = strValidText
End Function

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 String
Dim strValidText As String
Dim intX As Integer

strTblName = "Employees"
strValidRule = "EndDate > StartDate"
strValidText = "Enter an EndDate that is later than the StartDate."
intX = SetTableValidation(strTblName, strValidRule, strValidText)

Function SetTableValidation(strTblName As String, _
    strValidRule As String, strValidText As String) _
    As Integer

    Dim dbs As Database, tdf As TableDef

    Set dbs = CurrentDb
    Set tdf = dbs.TableDefs(strTblName)
    tdf.ValidationRule = strValidRule
    tdf.ValidationText = strValidText
End Function