Using the Validation Rule property
Keep in mind that if you assign the Validation Rule property to a field at the table level and then assign a different
Validation Rule property to a control at the form level, Access tries to use both properties to validate the data in that field.
An example of this would be if, for a field at the table level, you said that age had to be greater than 21, but for a field at the form level, you said that age had to be less than 21.
You would not be able to enter any ages, because no entry could pass both validation rules.
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