Field.ValidateOnSet Property (DAO)

Sets or returns a value that specifies whether or not the value of a Field object is immediately validated when the object's Value property is set (Microsoft Access workspaces only).

Syntax

expression .ValidateOnSet

expression A variable that represents a Field object.

Remarks

Only Field objects in Recordset objects support the ValidateOnSet property as read/write.

Setting the ValidateOnSet property to True can be useful in a situation when a user is entering records that include substantial Memo data. Waiting until the Update call to validate the data can result in unnecessary time spent writing the lengthy Memo data to the database if it turns out that the data was invalid anyway because a validation rule was broken in another field.

Example

This example uses the ValidateOnSet property to demonstrate how one might trap for errors during data entry. The ValidateData function is required for this procedure to run.

Sub ValidateOnSetX() 
 
 Dim dbsNorthwind As Database 
 Dim fldDays As Field 
 Dim rstEmployees As Recordset 
 
 Set dbsNorthwind = OpenDatabase("Northwind.mdb") 
 
 ' Create and append a new Field object to the Fields 
 ' collection of the Employees table. 
 Set fldDays = _ 
 dbsNorthwind.TableDefs!Employees.CreateField( _ 
 "DaysOfVacation", dbInteger, 2) 
 fldDays.ValidationRule = "BETWEEN 1 AND 20" 
 fldDays.ValidationText = _ 
 "Number must be between 1 and 20!" 
 dbsNorthwind.TableDefs!Employees.Fields.Append fldDays 
 
 Set rstEmployees = _ 
 dbsNorthwind.OpenRecordset("Employees") 
 
 With rstEmployees 
 
 Do While True 
 ' Add new record. 
 .AddNew 
 
 ' Get user input for three fields. Verify that the 
 ' data do not violate the validation rules for any 
 ' of the fields. 
 If ValidateData(!FirstName, _ 
 "Enter first name.") = False Then Exit Do 
 If ValidateData(!LastName, _ 
 "Enter last name.") = False Then Exit Do 
 If ValidateData(!DaysOfVacation, _ 
 "Enter days of vacation.") = False Then Exit Do 
 
 .Update 
 .Bookmark = .LastModified 
 Debug.Print !FirstName & " " & !LastName & _ 
 " - " & "DaysOfVacation = " & !DaysOfVacation 
 
 ' Delete new record because this is a demonstration. 
 .Delete 
 Exit Do 
 Loop 
 
 ' Cancel AddNew method if any of the validation rules 
 ' were broken. 
 If .EditMode <> dbEditNone Then .CancelUpdate 
 .Close 
 End With 
 
 ' Delete new field because this is a demonstration. 
 dbsNorthwind.TableDefs!Employees.Fields.Delete _ 
 fldDays.Name 
 dbsNorthwind.Close 
 
End Sub 
 
Function ValidateData(fldTemp As Field, _ 
 strMessage As String) As Boolean 
 
 Dim strInput As String 
 Dim errLoop As Error 
 
 ValidateData = True 
 ' ValidateOnSet is only read/write for Field objects in 
 ' Recordset objects. 
 fldTemp.ValidateOnSet = True 
 
 Do While True 
 strInput = InputBox(strMessage) 
 If strInput = "" Then Exit Do 
 ' Trap for errors when setting the Field value. 
 On Error GoTo Err_Data 
 If fldTemp.Type = dbInteger Then 
 fldTemp = Val(strInput) 
 Else 
 fldTemp = strInput 
 End If 
 On Error GoTo 0 
 If Not IsNull(fldTemp) Then Exit Do 
 Loop 
 
 If strInput = "" Then ValidateData = False 
 
 Exit Function 
 
Err_Data: 
 
 If DBEngine.Errors.Count > 0 Then 
 ' Enumerate the Errors collection. The description 
 ' property of the last Error object will be set to 
 ' the ValidationText property of the relevant 
 ' field. 
 For Each errLoop In DBEngine.Errors 
 MsgBox "Error number: " & errLoop.Number & _ 
 vbCr & errLoop.Description 
 Next errLoop 
 End If 
 
 Resume Next 
 
End Function