By far the best place to set data validation criteria is at the table level. You can set ranges of values and basic criteria, but more advanced or complex validation must be done via a form interface. If table rules are not met when a user attempts to move to a new record or close a form, the form raises an error and the user gets a somewhat unfriendly Access error message and the user is unable to leave the record.
What you appear to be doing is running data validation at the form level only and this may be due to the fact that you have complex validation that cannot be done at the table level or you wish to preempt the form data error when there is a failure to meet table validation requirements. The best place to do any entry validation is in the form's Before Update event. The form cannot move to a new record if you set the cancel parameter true in the before update. In order to give immediate feedback to users, you may also place validation code in each bound control that needs a validation rule. You may do this in addition to validating at the before update.
In your scenario, what happens if a user enters some data in a new record and uses a record navigation button to move to a previous record before a required field has data entered? The form isn't closed so your validation code does not run as the form close event procedure does not happen. If you have constraints defined at the table level, a form error is triggered. If you rely on form close validation, you are left with invalid data.
To prevent both the possiblity of invalid data and failure to trigger your validation code and to ensure that a user cannot leave a record in an invalid state, your validation code must run in the Form before update event. Although it is possible to prevent users from getting at tables and most updatable queries, it does not appear that you would know how to do this so I would strongly suggest you also set whatever validation rules you can at the table design level.
Assuming you want validation to run as each control is edited because of the way you have implemented it in the control exit event, you may instead wish to run it at the control before update event and undo the edit if it is invalid. Cancelling the exit is also a possiblity.
Private Function ValidateInitiator() As String
'assuming Initiator is simply a required field
'at table level, simply set not to allow empty string
If Not(Len(Trim$(Me.Initiator & ""))) Then
ValidateInitiator = vbcrlf & "Initiator is a required field."
End If
End Function
What this function does is it returns an error string unless there is text in the control named 'Initiator'. Neither Trim nor Len will work against a null field so rather than writing an error handler, you may concatenate an empty string for this test.
Private Function ValidateModelYear() As String
'assuming ModelYear must lie in the range 1965 to current year and is numeric
'this kind of validation can't as readily be done at the table level
Dim intYear As Integer
Dim strErr As String
If IsNumeric(Me.ModelYear) Then
intYear = cInt(Me.ModelYear)
If intYear > 1965 Then
If intYear < DatePart("yyyy", Date) + 1
strErr = "A model year in the future is invalid"
End If
Else
strErr = "Model year must be a number greater than 1964"
End If
Else
strErr = "Model year must be a numeric value"
End If
ValidateModelYear = vbcrlf + strErr
End Function
To make it work, from the Before update event of the controls:
Private Sub Initiator_BeforeUpdate(Cancel As Integer)
Dim strErrMessage
strErrMessage = ValidateInitiator & ""
If Len(ValidateInitiator) Then
MsgBox strErrMessage
Cancel = True
End If
End Sub
Private Sub ModelYear_BeforeUpdate(Cancel As Integer)
Dim strErrReturn As String
strErrReturn = ValidateModelYear & ""
If Len(ValidateModelYear) Then
MsgBox strErrReturn
Cancel = True
End If
End Sub
After you have written appropriate validation code for all your fields, you then call the validation code for all fields in the form before update:
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strErrMessage As String
strErrMessage = ValidateInitiator & ValidateModelYear & ValidateNextOne & ValidateOneAfterThat & ...
If Len(strErr & "")
Cancel = True
MsgBox strErrMessage
End If
End Sub
Calling the functions as shown in the before update will concatenate each of the validation failure messages returned by the validation routines advising the user with a single message boxes of all the errors with each to its own row in the message box. The return value will cancel the update if any validation returns an error string and the user will be prevented from leaving the record.
Ciao
Jürgen Welz
Edmonton AB Canada
[email protected]
Access going to new record on close
