Chapter 7: Writing VBA Code in Modules

Overview

There were some code samples in previous chapters, mostly event procedures for various form and control events. In this chapter, I’ll explain the Visual Basic window’s components and discuss the use of procedures in code behind forms (CBF) and standard modules, and present some more advanced code samples.

When you open a module (or click the New button when the Modules object is selected in the Object Bar), a new window opens: the Visual Basic Window. If you have worked with VBA code in Word, Excel, or another application, this window will be familiar, though there are a few differences in Access. Figure 7.1 shows the Visual Basic window; I’ll describe its main components briefly.

Figure 7.1

The Project Explorer

On the upper left of the Visual Basic window, the Project Explorer lists the modules in the current database (and possibly modules in add-in libraries). The current database’s modules are divided into three categories, in separate folders. Figure 7.2 shows the Project Explorer with the top folder closed, so you can see all three folders.

Figure 7.2

The Microsoft Access Class Objects folder contains code behind forms (and code behind reports). You’ll see an entry for each form or report that has a code module. Figure 7.3 shows a selection of form and report modules.

Figure 7.3

If you have created UserForms for a Word template, you will recognize the UserForm selection on the Insert Module selector. You can create a UserForm in Access and place controls on it—but there’s no point in doing this, because there’s no way to run UserForms in Access, and in any case, Access has its own forms, with more functionality than UserForms (most notably, Access forms can be bound to data).

The Modules folder contains standard modules, and the Class Modules folder contains class modules.

The Properties Sheet

The Properties Sheet in the lower left of the Visual Basic window displays the properties for the selected module—in Access, there is just one property to work with, the module name. New modules are called Modulen (for standard modules) or Classn (for class modules). I suggest renaming standard modules with the LNC tag bas, with a meaningful base name. For class modules, on the other hand, just use a meaningful base name, since the module name is the name of the custom object that will be referenced in code.

The Immediate Window

The Immediate Window (AKA the Debug Window) displays the results of Debug.Print statements, which you can insert in your code for debugging purposes. Debug.Print statements are especially useful for displaying the value of variables, filter strings, or SQL statements constructed in code, so you can see what’s wrong if they don’t work as expected. You can also test functions by typing them directly into the Immediate window, preceded by a question mark. Figure 7.4 shows several Debug.Print statements (from the AfterUpdate event of the Shipping Address selector combobox on fpriOrders) and two tests of the Mid function with a phone number.

Figure 7.4

The Module Pane

The Module Pane displays the contents of the module selected in the Project Explorer. You can see a portion of the basUtilities module’s contents displayed in the Module pane in Figure 7.4. You create and edit VBA code in the module pane. You can open a module directly in the Visual Basic window, by double-clicking it in the Project Explorer. Alternately (in the case of form and report modules), you can open a form or report module by clicking the Code button on the Form Design toolbar, as shown in Figure 7.5.

Figure 7.5

The Module pane has two unlabeled drop-down lists at the top: the Object selector on the left and the Procedure selector on the right. For code behind forms modules, the Object selector lets you select a form section or control, and the Procedure selector lets you select an event procedure for the selected object. For standard modules, the Object selector has only one choice, (General), and the Procedure selector lets you select either the Declarations section, or a procedure in the module.

Getting Help

For more details on working in the Visual Basic window, open the Visual Basic User Interface Help book from Microsoft Visual Basic Help, and select a topic. Figure 7.6 shows the Help topic for the Debug toolbar.

Figure 7.6

When using Visual Basic window Help, bear in mind that it is generic in nature, not customized for the Access Visual Basic window. For example, you’ll find UserForm topics, although UserForms aren’t functional in Access.