In some cases, you can cancel an event from your code. When you do so, the action that triggered the event is also cancelled. For example, the Exit event from a control can be cancelled. You can tell which events can be cancelled because their event procedures have a Cancel argument. To cancel an event, set this argument to True somewhere in your event handling code, as in this example:
Private Sub Client_Exit(Cancel As Integer) Cancel = True End Sub
What happens when you cancel the Exit event? The answer is that whatever action triggered the event is also cancelled by Access. If the users try to tab out of the text box, the cursor won't move. If they try to click elsewhere, they'll still be stuck. So obviously, you don't want to just cancel the event! Typically, you need to add some logic to check the data, and then decide whether to cancel the event based on what the users typed.
Another set of events that you can cancel are the Before events: BeforeUpdate, BeforeInsert, and BeforeDelConfirm. Canceling one of these events stops the corresponding action. For example, if you cancel a BeforeUpdate event, the update does not happen; the data is left in its previous state. This is very useful when you need to perform last-minute data validation before making any changes to the database.
CASE STUDY: Validating Data Before Saving It
There are places in the TimeTrack application where a little extra data validation can come in handy. For example, consider the Projects form. Two of the key pieces of data on this form are the starting date of the project and the estimated ending date of the project. Obviously, the starting date should occur before the ending date. This case study shows how to add some logic to check for improperly entered data, before the users try to save the data to the database. Of course, you can also use a table-level validation rule for this purpose. But using a validation rule means that the data isn't checked until the user tries to save the entire record.
To add this support, put the Projects form into Design view and then open the form's module. Add this code:
Private Sub StartDate_Exit(Cancel As Integer) ' Verify that dates make sense If DatesAreBad Then MsgBox "Start date must be before end date", _ vbCritical Cancel = True End If End Sub Private Sub EstimatedEndDate_Exit(Cancel As Integer) ' Verify that dates make sense If DatesAreBad Then MsgBox "Start date must be before end date", _ vbCritical Cancel = True End If End Sub Private Function DatesAreBad() As Boolean ' Check to be sure that the start date ' is before the end date On Error GoTo HandleErr ' Assume everything is OK DatesAreBad = False Dim dtSDate As Date Dim dtEDate As Date ' Make sure the textboxes hold dates dtSDate = CDate(StartDate.Value) dtEDate = CDate(EstimatedEndDate.Value) ' Check to see if there's a problem If dtEDate - dtSDate < 0 Then DatesAreBad = True End If ExitHere: Exit Function HandleErr: ' In case of any error, assume dates are bad DatesAreBad = True Resume ExitHere End Function
This code responds to the Exit events on both the starting and ending date controls. After all, a change to either control can cause a problem. Because the validation logic is the same in both cases, it makes sense to put the actual tests in a procedure (DatesAreBad) that's called from both event handlers. This procedure looks at the difference in time between the start and end date, and returns True if the end date is before the start date.
In either event handler, if DatesAreBad returns True, the event handler does two things. First, it displays a message to the users. Second, it sets the Cancel argument to True, which prevents the users from leaving the control until they fix the problem.