Access Class Modules, Standard Modules, Form Modules, and Report Modules

You write VBA code in units called subroutines and functions that you store in modules. Microsoft Access modules are either Standard modules or Class modules. You create Standard modules by selecting the Modules icon in the Database window and then clicking New. Standard modules contain libraries of subroutines and functions. Class modules can be standalone objects, or they can be associated with a form or report. To create a standalone Class module, you choose Insert | Class Module. Whenever you add code behind a form or report, Microsoft Access creates a Class module associated with that form or report that contains the code you create.

Modules specific to a form or report are generally called Form and Report Class modules, and their code is often referred to as Code Behind Forms (CBFs). Access creates and stores CBF in that form or report and triggers the code within it from events occurring within the form or report.

A subroutine (or subprocedure) is a routine that responds to an event or performs some action. An event procedure is a special type of subroutine that automatically executes in response to an event such as a mouse click on a command button or the loading of a form. A function is a special type of routine because it can return a value; a subroutine can't return a value. As with a subroutine, you can trigger a function from an event.

Where Do You Write VBA Code?

You write all VBA code in the Visual Basic Editor (VBE). Access places you in the VBE any time you attempt to access the code in a Standard or Class module. Figure 23.1 shows the VBE. The VBE environment in Microsoft Access is now consistent with the editor interfaces in other Microsoft Office products. The VBE is a separate window from that of Microsoft Access, and it consists of a menu bar, toolbar, Project window, Properties window, Immediate window, Locals window, Watch window, Object Browser, and Code windows.

The Anatomy of a Module

Whether you're dealing with a Standard module or a Class module, every module contains a General Declarations section (see Figure 23.2). As the name implies, this is where you can declare variables and constants that you want to be visible to all the functions and subroutines in the module. We refer to these variables as module-level or Private variables. You can also declare Public variables in the General Declarations section of a module, and you can set options there, too. Any function or procedure in any module in the database can see and modify Public variables.

Public variables in Access 97, 2000, 2002, and 2003 replace Access 2.0's Global variables. Although Access still supports Global variables, today most people use Public variables rather than Global variables.

A module is also made up of user-defined subroutines and functions. Figure 23.3 shows a subroutine called SayHello. Notice the drop-down list in the upper-right portion of the window with SayHello selected. This is the Procedure drop-down list. You might want to associate subroutines and functions with a specific object, such as a form or a control within a form. The Procedure drop-down list is where Access notes such an association. In the example shown in Figure 23.3, the subroutine named SayHello is not associated with any object, so the Object drop-down list contains (General).

Using the Option Explicit Statement

Option Explicit is a statement that you can include in the General Declarations section of any module, including the Class module of a form or report. When you use Option Explicit, you must declare all variables in that module before you use them, or an error message saying that a variable is undefined appears when you compile the module. If the VBA compiler encounters an undeclared variable when compiling a module without Option Explicit, it simply treats it as a new variable and continues without issuing a warning. It might appear at first glance that because Option Explicit can cause compiler errors that would otherwise not occur, it might be better to avoid the use of this option. However, just the opposite is true. You should use Option Explicit in every module, without exception. For example, look at the following code:

intAmount = 2
intTotal = intAmont * 2

Clearly, the intent of this code is to multiply the value contained in the variable intAmount, in this case 2, by 2. Notice, however, that the variable name is misspelled on the second line. If Option Explicit is not set, VBA views intAmont as a new variable and simply continues processing. The code sets the variable intTotal to 0 instead of 4, and the VBA compiler provides no error indication at all. You can completely eliminate this kind of result by using Option Explicit.

In Access 2.0, you have to manually enter the Option Explicit statement into each module, form, and report. Since Access 97, developers have had the option of globally instructing Access to insert the Option Explicit statement in all new modules. To do this in Access 2003, with the VBE active, you choose Tools | Options. On the Editor tab, click Require Variable Declaration. It's important that you place the Option Explicit statement in all modules, so you need to make sure you set this option to True. The default when you install Microsoft Access 2003 is False. Option Explicit can save you hours of debugging and prevent your beeper from going off after you distribute an application to users.

In addition to a General Declarations section and user-defined procedures, forms, and reports, Class modules also contain event procedures that Access associates with a particular object on a form. Notice in Figure 23.4 that the Object drop-down list says cmdHello. This is the name of the object whose event routines you are viewing. The drop-down list on the right shows all the events that you can code for a command button; each of these events creates a separate event routine. You will have the opportunity to write many event routines in this hour.

Creating Event Procedures

Access automatically creates event procedures when you write event code for an object. For example, it automatically creates the routine Private Sub cmdHello_Click when you place code in the Click event of the cmdHello command button shown in Figure 23.4. To get to the event code of an object, follow these steps:

As discussed at the beginning of this hour, the VBE opens in a separate window from Access. It provides a programming environment that is consistent with that of all the other Microsoft Office applications. Modules you add in the VBE do not appear in the database container until you save them within the VBE.

Creating Functions and Subroutines

You can create your own procedures that aren't tied to a particular object or event. Depending on how and where you declare them, you can call them from anywhere in an application or from a particular Code module, Form module, or Report module.

Creating a User-Defined Routine in a Code Module

There are several different methods that you can use to create a user-defined routine in a Code module. They are all quite simple. The text that follows defines one process:

Creating a User-Defined Routine in a Form or Report Class Module

The process of creating a user-defined routine in a Form or Report class module is almost identical to that of creating a subroutine or function in a Standard module. Here's the process:

Whether you're creating a procedure in a Standard module or a Class module, you're now ready to enter the code for a procedure. A great shortcut for creating a procedure is to type directly in the code window the name of the new procedure, preceded by its designation as either a Sub or a Function?for example, Sub Whatever or Function Whatever. As soon as you press Enter, the new subroutine or function is created.

You'll learn about the concept of Public versus Private procedures and the concept of scope later in this hour, in the section "The Scope and Lifetime of Procedures."

Calling Event and User-Defined Procedures

Access automatically calls Event procedures when an event occurs for an object. For example, when a user clicks a command button, the Click event code for that command button executes.

The standard method for calling user-defined procedures is to use the Call keyword?for example, Call SayHello. You can also call the same procedure without using the Call keyword?for example, SayHello.

Although not required, using the Call keyword makes the statement self-documenting and easier to read. You can call a user-defined procedure from an event routine or from another user-defined procedure or function. Here's an example:

SayHello
Call SayHello

Both of these lines of code accomplish the same task: calling the SayHello routine. The only difference is that the second is more self-documenting due to the Call statement.

The Scope and Lifetime of Procedures

You can declare the scope of a procedure as Public or Private. A procedure's scope determines how widely you can call it from other procedures. In addition to a procedure's scope, the placement of a procedure can noticeably affect an application's functionality and performance.

Public Procedures

You can call a Public procedure that you place in a code module from anywhere in the application. Procedures you declare in a module are automatically Public. This means that unless you specify otherwise, you can call procedures you place in any code module from anywhere within an application.

You might think that two Public procedures can't have the same name. Although this is the case in earlier versions of Access, it isn't true in Access 2000, Access 2002, and Access 2003. If two Public procedures share a name, the procedure that calls them must explicitly state which of the two routines it's calling. You can find the following code snippet, in frmHello's Class module in the sample database, Chap23Ex.mdb, at www.samspublishing.com:

Private Sub cmdSayGoodBye_Click()
   Call basUtils.SayGoodBye
End Sub

This code calls the SayGoodBye routine in the basUtils module.

You can find this code, and all the sample code in this chapter, in Chap23Ex.mdb at www.samspublishing.com.

You will find the SayGoodBye routine in two Access code modules; however, the prefix basUtils indicates that the routine you want to execute is in the Standard module named basUtils.

Procedures declared in Form or Report Class modules are also automatically Public, so you can call them from anywhere within the application. You can find the procedure called cbfIAmPublic, shown in Figure 23.6, in the form called frmHello. In order to call this procedure from outside the form, the only requirement is that the form containing the procedure must be open in Form view. You can call the cbfIAmPublic procedure from anywhere within the application by using the following syntax (found in the Standard module basHello):

Sub CallPublicFormProc()
   Call Forms.frmHello.cbfIAmPublic
End Sub

Although all procedures (except event procedures) are by default Public, you should use the Public keyword to show that the procedure is visible to any subroutine or function in the database.

Private Procedures

As mentioned previously, all user-defined procedures are automatically Public. If you want a procedure declared in a module to have the scope of that module only, meaning that you can call it only from another routine within the module, you must explicitly declare it as Private (see Figure 23.7).

The procedure shown in Figure 23.7, called IAmPrivate, is Private. You can call it only from other procedures in the Standard basUtils module.

Scope Precedence

Private procedures always take precedence over Public procedures. If a Private procedure in one module has the same name as a Public procedure declared in another module, the Private procedure's code executes if you call it from any routine in the module where it was declared. Naming conflicts don't occur between Public and Private procedures (unless you declare a Public variable and a Private variable with the same name in the same module).