You may want to create a validation rule for a particular field that depends on the values of one or more of the other fields in the table. You can create a table-level validation rule to compare one field to another.
Suppose you want to specify a rule for a Marketing Survey table that requires that all surveys using the name TestAccount have a Class field value of X. You can’t validate the Name field without knowing the value of the Class field, and vice versa. Only when a record is either updated or inserted is all the information available to evaluate the rule.
The validation rule must contain an expression that can be evaluated as either True or False. If the expression evaluates to True, the update is allowed. If the expression evaluates to False, Microsoft Jet generates a trappable run-time error.
The following example uses DAO to add a table-level validation rule, where strDbPath
is the path to the NorthwindTables database:
Function AddTableLevelValidationRule() As Boolean ' This procedure checks for an existing table-level validation rule and ' validation text on the Orders table and prompts the user to create new ones. Dim dbs As Database, tdf As TableDef Dim strValRule As String, strValText As String Dim strDbPath As String On Error GoTo Err_AddTableLevelValidationRule Set dbs = OpenDatabase("C:\JetBook\Samples\NorthwindTables.mdb") Set tdf = dbs.TableDefs("Orders") If Len(tdf.ValidationRule) Then If MsgBox("The following validation rule already exists: " & vbCrLf _ & vbCrLf & tdf.ValidationRule & vbCrLf & vbCrLf _ & "Delete this rule and create a new one?", vbYesNo) = vbYes Then tdf.ValidationRule = "" Else GoTo Exit_AddTableLevelValidationRule End If End If strValRule = "([RequiredDate]>=[OrderDate]) And ([ShippedDate]>=[OrderDate])" tdf.ValidationRule = strValRule If Len(tdf.ValidationText) Then If MsgBox("The following validation text already exists: " & vbCrLf _ & vbCrLf & tdf.ValidationText & vbCrLf & vbCrLf _ & "Delete this validation text and create new text?", vbYesNo) = _ vbYes Then tdf.ValidationText = "" Else GoTo Exit_AddTableLevelValidationRule End If End If strValText = "Both the Required Date and the Shipped Date must be " & _ "the same date or later than the Order Date." tdf.ValidationText = strValText AddTableLevelValidationRule = True Exit_AddTableLevelValidationRule: On Error Resume Next dbs.Close Set dbs = Nothing Exit Function Err_AddTableLevelValidationRule: MsgBox "Error: " & Err & vbCrLf & Err.Description AddTableLevelValidationRule = False Resume Exit_AddTableLevelValidationRule End Function
Note that you can test the properties as well as set them from DAO code. The previous example checks that no current table-level validation rule and validation text exist before adding the new ones.
Because table-level validation rules can’t be evaluated until all the fields in a new record are populated, the validation rule is not checked until just before the new record is inserted in the table, or just before the existing record is updated.
The validation rule in the previous example uses the built-in IIf (Immediate If) function. While you can’t use your own custom functions within a validation rule, you can make use of a wide variety of built-in functions, including date functions and string-manipulation functions such as InStr, Len, and Mid.