Part III: More-Advanced Access Techniques
824
happens. The problem is that the code is doing the wrong thing, even though it’s executing as
programmed.A well-written application may prevent runaway, endless loops by counting how many times the
loop has executed and forcing an end to the loop when the maximum value has been exceeded.
Other sophisticated ways of handling unanticipated errors include keeping track of the number of
times a particular function has been called, or monitoring how long it takes for a query to execute
or a form or report to open. Such extreme measures are not necessary in the vast majority of
Access applications, but you should be aware that there are solutions to virtually any unexpected
application problem.Identifying Which Errors Can Be Detected
Several hundred trappable errors can occur in VBA applications, but only a small portion of these
hundreds of errors is likely to occur in your applications. The question is, then, which of the
remaining 50 or 100 relevant errors should you trap in your applications?Most developers begin simply and write an error handler that catches the most obvious errors. In
the case of the navigation buttons on an Access or Visual Basic form, you should always trap the
error that occurs when the user tries to move off either end of the recordset. Such an error is read-
ily anticipated and is the result of normal navigation through a recordset.However, consider a problem that makes it impossible for your application to create the recordset.
There are many reasons why the OpenRecordset method may fail. Perhaps the table can’t be
found because it’s been deleted or a link to the table is broken, or there could be an error in the
SQL statement used to create the recordset and no records are returned. During development you
may never see an error caused by an empty recordset because your test data is always available,
and (during development) the OpenRecordset method never fails.Experience will tell you which errors are expected as you write your VBA procedures. But, you
should always prepare for the unexpected. Later in this chapter, you’ll see how to add a general-
purpose error handler to a VBA procedure. This general-purpose error handler catches all errors
that occur within the procedure, and avoids the default error dialog box (refer to Figure 23.2).What an error handler is
VBA provides extensive runtime-error-handling capabilities. You can add code to your applications
to detect when an error occurs. Other code directs the program to handle anticipated errors in a
predictable fashion. Still other code can catch unanticipated errors, automatically correcting the
problem, preventing data loss, and reducing support costs. Although it may sound as though dif-
ferent coding techniques are needed for trapping different types of errors, VBA supports just one
basic error-handling process, as described in the “Trapping Errors with VBA” section, later in this
chapter.