Microsoft Access 2010 Bible

Part II: Programming Microsoft Access

534

you specify the correct number of options and in the right order. The VBA compiler can’t detect

logical errors in your code, and it certainly can’t help with runtime problems.

Tip
After you compile your program, be sure to compact your database. Every time you make a change to your
program, Access stores both the changes and the original version. When you compile your program, it may
double in size as the compiled and uncompiled versions of your code are stored. Compacting the database can
reduce the size of the database by as much as 80 percent to 90 percent, because it eliminates all previous ver-
sions internally.

Traditional Debugging Techniques

Two widely used debugging techniques have been available since Access 1.0. The first is to insert

MsgBox statements to display the value of variables, procedure names, and so on. The second com-

mon technique is to insert Debug.Print statements to output messages to the Immediate window.

Using MsgBox

Figure 14.11 shows an example of a message box displaying a long SQL statement to enable the devel-

oper to verify that the statement was properly composed by the application. The example in Figure

14.11 is found in the basUsingMsgBox module in the Chapter14.accdb example database.

FIGURE 14.11

The MsgBox statement makes a satisfactory debugging tool (with some limitations).

Here are the advantages of using the MsgBox statement:

l The MsgBox statement is simple and easy to use and only occupies a single line of code.

l (^) The MsgBox statement can output many types of data.
l The message box itself pops up right on the user interface, and you don’t have to have the
Immediate window open or flip to the Immediate window to view the message box.
l MsgBox halts code execution, and because you know where you’ve put the MsgBox state-
ments, you know exactly where the code is executing.