Microsoft Access 2010 Bible

Part II: Programming Microsoft Access

530

FIGURE 14.6

The Options dialog box’s General tab contains some valuable options.

Remember
Be sure to turn this option off before distributing the application to end users.

Compile on Demand

Compile on Demand instructs Access to compile modules only when their procedures are required

somewhere else in the database. When this option is unchecked, all modules are compiled any

time any procedure is called. Unchecking this option makes sure that you see all errors that are

detected by the compiler each time you make changes to the modules in your application. If you

leave this option selected, the Access compiler won’t recompile all the code in the application,

which means that some errors may slip through and only be discovered at runtime.

Organizing VBA Code

It shouldn’t come as any surprise that your coding habits have a lot to do with the errors you

encounter in your applications. Very often, the adoption of simple coding conventions eliminates

all but the toughest syntactical and logical errors in VBA code.

One convention you should follow is to put each variable declaration in its own line of code.

Consider the code shown in Figure 14.7. Although bunching multiple variable declarations

together as a single Dim statement is perfectly permissible, you have to scan the entire line of code

to find the declaration of each of the variables. The problem in Figure 14.7 is that it’s far too easy

to overlook a variable or to misunderstand the data type assigned to a variable. You can easily cre-

ate a logical bug by assigning a variable an incorrect value.