Chapter 14: Debugging Your Access Applications .......................................................................
525
Maybe you have too many parentheses in an expression, or you’ve used a control name in a for-
mula that conflicts with an Access keyword. Each time you had this problem, you may have asked
someone with more experience than you what the problem was, or perhaps you looked it up
online or in a book, or you researched the syntax of the formula.Most problems with query, form, and report design are pretty obvious. You know you have a
problem when a query returns the wrong data, or a form or report fails to open or displays an error
message as it opens. Behind the scenes, Access does a great deal to help you notice and rectify
problems with your application’s design. When you run forms and reports, Access often reports an
error if it finds something seriously and obviously wrong.It’s much more difficult for Access to help you with incorrectly written code. Very often, a problem
in VBA code exists for months or even years before a user notices it. Even poorly written code can
run without throwing errors or exhibiting obvious problems. However, determining exactly where
a bug exists in VBA code — and figuring out what to do to repair the bug — can be very challeng-
ing. When you create VBA code, you’re pretty much on your own when it comes to detecting and
resolving problems. Fortunately, a wide variety of tools have been built into the editor to help you.Tip
Testing and debugging takes quite a bit of time. Many good developers easily spend a third of their time design-
ing a program, another third writing code, and another third testing and debugging. Having someone other
than the developer test a program’s operation is often a good idea. A person who is unfamiliar with an applica-
tion is more likely to do something the developer never expected, leading to new and surprising bugs and insta-
bility issues.
Using the Module Options
Many VBA errors are very easy to either prevent, or to handle once they occur. The VBA code edi-
tor features a number of options designed to help you avoid errors in your VBA code, and to help
make your code easier to read and understand. Figure 14.1 shows the Editor tab of the Options
dialog box (Tools ➪ Options while the VBA code editor window is open). The Code Settings area
in the upper half of this dialog box contains a number of important options that help you write
and debug the VBA code in your applications.The Editor tab in the Options dialog box (see Figure 14.1) contains a number of options that are
important to the integrity of your VBA code. These options are summarized in the following sections.Auto Syntax Check
By far the most common error when writing VBA code is a syntax error. Syntax errors are caused
by using the VBA language incorrectly, much like mispronouncing a sentence in a foreign lan-
guage. By far, the most common bug in Access VBA code is the simple syntactical error caused by
misspelling a keyword or a variable name or misusing a procedure, property, or method.