Alison Balteramp;#039;s Mastering Microsoft Office Access 1002003 [Electronic resources] نسخه متنی

What Are Access Class Modules, Standard Modules, Form Modules, and Report Modules?

VBA code is written in units called

subroutines and

functions that are stored in modules. Microsoft Access modules are either Standard modules or Class modules.

Standard modules are created by selecting the Modules icon in the Database window, and then clicking New.

Class modules can be standalone objects or can be associated with a form or report. To create a standalone Class module, you choose the Class Module command from the Insert menu. In addition, 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 (

CBF

) . CBF is created and stored in that form or report and triggered from events occurring within it.

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. Like a subroutine, a function can be triggered from an event.

Where Is VBA Code Written?

You write all VBA code in the Visual Basic Editor, also known as the VBE. Access places you in the VBE anytime you attempt to access the code in a Standard or Class module. Figure 7.1 shows the Visual Basic Editor. 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 comprises a menu bar, toolbar, Project window, Properties window, Immediate window, Locals window, Watch window, Object Browser, and Code windows. The various components of the VBE are discussed as appropriate in this chapter and throughout the book.

The Anatomy of a Module

Whether you're dealing with a Standard module or a Class module, all modules contain a General Declarations section (see Figure 7.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. You can also set options in this section. These variables are referred to as

module-level or

Private variables . You can also declare Public variables in the General Declarations section of a module.

Public variables can be seen and modified by any function or procedure in any module in the database.

NOTE

Public variables in Access 97 and above replace Access 2.0's Global variables. Although Global variables are still supported in Access 97 and above, today most people use Public variables rather than Global variables.

A module is also made up of user-defined subroutines and functions. Figure 7.3 shows a subroutine called SayHello. Notice the drop-down list in the upper-left portion of the window titled Chap7ExbasHello (Code). This is referred to as the Object drop-down list. Subroutines and functions are sometimes associated with a specific object, such as a form or a control within a form. This is where such an association is noted. In this case, the subroutine named SayHello is not associated with any object, so the Object drop-down list contains (General).

Access 2003 has an environment option called Full Module View. This option, when checked, enables you to see several subroutines and functions in a module at one time. Notice the difference between Figure 7.3 and Figure 7.4. In the code window shown in Figure 7.3, only one subroutine is visible at a time. The code window shown in Figure 7.4 illustrates the effects of Full Module Viewmultiple subroutines are visible, each separated by a thin, horizontal line. Full Module View is the default in Access 2003. To change the Full Module View environment setting, with the VBE active, choose Tools, Options, click the Editor tab, and check Default to Full Module View. If you want to temporarily change to Procedure view, click the Procedure view button in the lower-left corner of a module window and then click the Full Module View button to return to Full Module View.

Option Explicit

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 saying that a variable is undefined will occur when you compile the module. If Access encounters an undeclared variable when it compiles a module without Option Explicit, VBA will simply treat it as a new variable and continue without 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 you have not set Option Explicit, VBA views intAmont as a new variable and simply continues its processing. The variable intTotal will be set to 0 instead of 4, and no error indication is given at all. This kind of result can be totally avoided by using Option Explicit.

TIP

In Access 2.0, you had to manually enter the Option Explicit statement into each module, form, and report. Going back to 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, choose Tools, Options. Under the Editor tab, click Require Variable Declaration. It's important that you place the Option Explicit statement in all your modules, so make sure you set this option to True. The default when installing Microsoft Access 2003 is False. Option Explicit will save you hours of debugging and prevent your beeper from going off after you distribute your application to your users.

In addition to a General Declarations section and user-defined procedures, forms, and reports, Class modules also contain event procedures that are associated with a particular object on a form. Notice in Figure 7.5 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 as you read through this book.

Creating Event Procedures

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

You are now ready to write code that will execute when that event occurs for the selected object.

NOTE

As discussed at the beginning of the chapter, the VBE opens in a separate window. It provides a programming environment consistent with that of all the other Microsoft Office applications. Modules added in the VBE will not appear in the database container until you save them within the VBE.

Creating Functions and Subroutines

You can also 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 your application or from a particular Code module, Form module, or Report module.

Creating a User-Defined Routine in a Code Module

Whereas event routines are tied to a specific event that occurs for an object, user-defined routines are not associated with a particular event or a particular object. Here are the steps that you can take to create a user-defined routine:

Access creates a user-defined routine. Your cursor is placed within the routine, and you can now write the code that encompasses the body of the routine.

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

Just as you can create a user-defined routine in a code module, you can also create a user-defined routine in a form or report class module. Here's the process:

Access places a user-defined procedure within your form or report class module. You are now ready to write the code that executes when another procedure calls the user-defined procedure.

TIP

Whether you're creating a procedure in a Standard module or a Class module, you're now ready to enter the code for your 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. Example: Sub

Whateve

r or Function

Whatever . This creates a new subroutine or function as soon as you press Return.

Calling Event and User-Defined Procedures

Event procedures are automatically called 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 keywordCall SayHello, for example. You can also call the same procedure without using the Call keyword: 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.

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 your application's functionality and performance.

Another attribute of a procedure has to do with the lifetime of any variables that you declare within the procedure. By default, the variables you declare within a procedure have a

lifetime that is, they have value and meaning only while the procedure is executing. When the procedure completes execution, the variables that it declared are destroyed. You can alter this default lifetime by using the Static keyword.

Public Procedures

You can call a public procedure placed in a code module from anywhere in the application. Procedures declared 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 your application.

You might think that two public procedures can't have the same name. Although this was true in earlier versions of Access, it isn't true in Access 2000 and later. If two public procedures share a name, the procedure that calls them must explicitly state which of the two routines it's calling. This is illustrated by the following code snippet found in frmHello's Class module in the sample database, Chap7ex.mdb:

Private Sub cmdSayGoodBye_Click()
Call basUtils.SayGoodBye
End Sub

NOTE

Unless noted otherwise, this code, and all the sample code in this chapter, is found in Chap7ex.mdb on the sample code CD-ROM.

You can 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. The procedure called cbfIAmPublic, shown in Figure 7.7, is found in the form called frmHello. The only requirement for this procedure to be called from outside the form is that the form containing the procedure must be open in Form view. The cbfIAmPublic procedure can be called from anywhere within the application by using the following syntax (found in the Standard module basHello):

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

TIP

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, 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 7.8).

The procedure shown in Figure 7.8, 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 is executed if it's called by any routine in the module where it was declared. Naming conflicts don't occur between public and private procedures (unless you declare a public and private variable with the same name in the same module).Chapter 17, "Optimizing Your Application"). Regardless of when Access loads the code, an advantage of placing code behind forms and reports (rather than within modules) is that the form or report is self-contained and, therefore, portable. You can import the form or report into any other database, and it still operates as expected. This object-oriented approach means that the form requires nothing from the outside world.

As you can see, there are pluses and minuses to each method. As a general rule, if a routine is specific to a particular form or report, place that routine in the form or report; if it's widely used, place it in a module.

Static Procedures

If a procedure is declared as static, all the variables declared in the procedure maintain their values between calls to the procedure. This is an alternative to explicitly declaring each variable in the procedure as static. Here's an example of a static procedure, found in basVariable:

Static Sub IncrementThem()
Dim intCounter1 As Integer
Dim intCounter2 As Integer
Dim intCounter3 As Integer
intCounter1 = intCounter1 + 1
intCounter2 = intCounter2 + 1
intCounter3 = intCounter3 + 1
MsgBox intCounter1 & " - " & intCounter2 & " - " & intCounter3
End Sub

Ordinarily, each variable in this procedure would be reinitialized to zero each time the procedure is run. This means that all 1s would appear in the message box each time you run the procedure. Because the procedure is declared as static, the variables in it retain their values from call to call. That means that each time you run the procedure, the values in the message box increase. This behavior should become much clearer after the discussion of variables later in this chapter.