Microsoft Access 2010 Bible

Part II: Programming Microsoft Access

410

The improvement goes beyond using fewer lines to get the same amount of work done. Notice that

you no longer need an integer variable to count through the Controls collection. You also don’t

have to call on the Controls collection’s Count property to determine when to end the For loop.

All this overhead is handled silently and automatically for you by the VBA programming language.

The code in this listing is easier to understand than in either of the previous procedures. The pur-

pose of each level of nesting is obvious and clear. You don’t have to keep track of the index to see

what’s happening, and you don’t have to worry about whether to start the For loop at 0 or 1. The

code in the For...Each example is marginally faster than the With...End With example

because no time is spent incrementing the integer value used to count through the loop and Access

doesn’t have to evaluate which control in the collection to work on.

On the CD-ROM
The Chapter10.accdb example database includes frmWithDemo (see Figure 10.21), which contains all the
code discussed in this section. Each of the three command buttons along the bottom of this form uses different
code to loop through the Controls collections on this form, changing the font characteristics of the controls.

FIGURE 10.21

frmWithDemo is included in Chapter10.accdb.

Looking at Access Options for Developers

Many of the most important features in Access affect only developers. These features are hidden

from end users and benefit only the person building the application. Spend some time exploring

these features so that you fully understand their benefits. You’ll soon settle on option settings that

suit the way you work and the kind of assistance you want as you write your VBA code.

The Editor tab of the Options dialog box

The Options dialog box contains several important settings that greatly influence how you interact

with Access as you add code to your applications. These options are accessed by opening a module

in the VBA code editor, and choosing Tools ➪ Options.