Part III: More-Advanced Access Techniques
820
Dealing with Errors
We all have to deal with the errors that occur in our applications. Even the best-written code fails
now and then, very often because of problems with data entry or poorly trained users. Other times,
errors occur because we’ve written the code incorrectly, or we haven’t adequately tested and
debugged an application before distributing to users.Errors fall into three basic categories: logical errors, runtime errors, and unanticipated errors.Logical errors
Logical errors can be difficult to detect and remove. A logical error (also called a bug) often occurs
because of some mathematical error: Perhaps data was misused in a recordset, maybe there is some
other problem dealing with the data or program flow of the application, or maybe erroneous user
input isn’t properly captured by the application.For example, let’s say that a patient-management program assumes that the user has entered both a
first name and a last name for a patient. A patient data-entry form contains text boxes for both
these values. So far so good. But what if the user typing in the details doesn’t enter something in
both boxes? (There are all kinds of reasons this might happen — for example, maybe the user
entered both the first and last names into the text box intended to hold only the last name.) An
empty text box may contain a null value, unless a default value has been provided. A logical error
is generated if the application then tries to use the patient’s name in a lookup or sorting operation.
If you’re lucky, the user will notice the logical error. The user could be informed through the use
of a pop-up dialog box or error message, and the missing data could be corrected before the data is
committed to the database.Other logical errors are created when, for instance, an application incorrectly calculates the days
between dates, uses the wrong value in a division or multiplication operation, and so on. Virtually
any time data is mishandled or inappropriately used in your application, a logical error is likely to
occur as a result.The obvious solution to the missing first name/last name situation is to add some VBA code to the
form’s BeforeUpdate event to verify that both a first name and a last name have been entered,
and notify the user that one or both names are missing. Alternatively, the application may insert a
default value such as “N/A” for the first and last names when either has been left blank.Even so, a well-mannered application should detect errors when they occur, and handle them
gracefully. Access, like most Windows applications, handles errors in a fairly unfriendly fashion,
popping up a dialog box that may or may not adequately describe the error to the user. A carefully
written application traps these errors before Access takes over, handling them without disturbing
the user’s workflow.