Table-Level Validation

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.