Chapter 8. VBA: An Introduction

IN THIS CHAPTER

• Why This Chapter Is Important
• VBA Explained
• What Are Access Class Modules, Standard Modules, Form Modules, and Report Modules?
• Working with Variables
• Adding Comments to Your Code
• Using the Line Continuation Character
• Using the VBA Control Structures
• Passing Parameters and Returning Values
• Executing Procedures from the Module Window
• The DoCmd Object: Performing Macro Actions
• Working with Built-In Functions
• Working with Constants
• Working with the Visual Basic Editor Tools
• Customizing the VBE
• Practical Examples: Using Event Routines, User-Defined Functions, and Subroutines

Why This Chapter Is Important

The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This chapter introduces you to the VBA language. It serves as a foundation for the remainder of the book. After reading this chapter, you will be familiar with the development environment. You will know how to declare variables, use control structures, pass and return parameters, work with built-in functions, and more.

VBA Explained

VBA is the development language for Microsoft Access 2007. It offers a consistent language for application development in the Microsoft Office suite. The core language, its constructs, and the environment are the same in Microsoft Access 2007, Microsoft Visual Basic 6.0 and earlier, Microsoft Excel, Microsoft Word, Microsoft Outlook (for application-wide programming), and Microsoft Project. What differs among these environments are the built-in objects specific to each application. For example, Access has a CurrentProject object, but Excel has a Workbook object. Each application’s objects have appropriate properties (attributes) and methods (actions)—and, in some cases, events—associated with them. This chapter gives you an overview of the VBA language and its constructs.

Unlike macros in Word or Excel, Access macros are not subprocedures in modules; instead, they are a different type of database object, with their own interface. Because of this, you can’t use Access macros to learn to program in VBA, as you can by recording a Word or Excel macro and then examining its VBA code. You can write some Access 2007 applications by using macros. Although macros are okay for relatively basic application development, you will do most serious Access development by using the VBA language. Unlike macros, VBA enables you to do the following:

• Work with complex logic structures (case statements, loops, and so on)
• Take advantage of functions and actions not available in macros
• Loop through and perform actions on recordsets
• Perform transaction processing
• Create database objects programmatically and work with them
• Create libraries of user-defined functions
• Call Windows API functions
• Perform complex object linking and embedding (OLE) automation commands

The VBA language enables you to use complex logic structures. Macros let you perform only simple If...Then...Else logic, but the VBA language offers a wealth of logic and looping constructs, which are covered later in this chapter.

If you try to develop an application using only macros, you can’t take advantage of many of the rich features available in the VBA language. In addition, many of the actions available in both macros and modules can be performed much more efficiently with VBA code.

Complex Access applications often require you to loop through a recordset, performing some action on each member of the set. There’s no way to do this using Access macros. However, with the VBA language and ActiveX Data Objects (ADO), you can add, delete, update, and manipulate data. Chapter 15, “What Are ActiveX Data Objects, and Why Are They Important?” covers the details of ADO.

When manipulating sets of records, you want to ensure that all processing finishes successfully before the Access Database Engine permanently updates your data. Macros don’t enable you to protect your data with transaction processing. Using the BeginTrans, CommitTrans, and Rollback methods, you can make sure that the Access Database Engine updates your data only if all parts of a transaction finish successfully. Transaction processing, if done properly, can substantially improve your application’s performance because no data is written to disk until the process is finished. Transaction processing and its benefits are covered in Alison Balter’s Mastering Access 2002 Enterprise Development.

With Access macros, you can’t create or modify database objects at runtime. Using VBA, you can create databases, tables, queries, and other database objects; you can also modify existing objects. There are many practical applications of this capability to create or modify database objects (discussed in more detail in Chapter 15). When users are able to build queries on the fly, for example, you might want to give them the capability to design a query by using a front-end form that you provide. You can also enable users to store the query so that they can run it again later.

VBA also makes it easier for you to write code libraries of reusable functions, design and debug complex processes, and even write your own add-ins. If you’re developing even moderately complex applications, you want to be able to create generic function libraries that can be used with all your Access applications. Doing this using macros is extremely difficult, if not impossible.

Many powerful functions not available within the VBA language are available as part of Windows itself. The Windows API (Application Programming Interface) refers to the nearly 1,000 Windows functions that Microsoft exposes for use by Access programmers. You can’t take advantage of these functions from an Access macro. However, by using VBA code, you can declare and call these functions, improving both the performance and functionality of your applications. Chapter 25, “Exploiting the Power of the Windows API,” covers the Windows API.

Both DDE and Automation technology enable you to communicate between your Access applications and other applications. Although DDE is an older technology than Automation, it’s still used to communicate with a few applications that don’t support Automation. Automation is used to control Automation server applications, such as Excel and Project, and their objects (all Microsoft Office applications are Automation servers). Automation is covered in Chapter 24, “Automation: Communicating with Other Applications.”

Although macros in Microsoft Office Access 2007 are significantly more powerful than macros in previous versions of Access (see Chapter 7, “What Are Macros, and When Do You Need Them?”), it is best to use a combination of both macros and VBA for developing complex solutions. If you would ever like to convert a macro to VBA code, a Save As menu option is available when saving an existing macro.

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 clicking to select the Database Tools tab and then selecting the Visual Basic button from the Macro group. Access takes you to the Access Visual Basic Editor (VBE). Finally, select Insert, Module from the VBE menu. 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 VBE 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 select Visual Basic from the Macro group on the Database Tools tab or press Alt+F11. Figure 8.1 shows the Visual Basic Editor. The VBE environment in Microsoft Access is 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.

Figure 8.1. The Visual Basic Editor (VBE).

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 8.2). As the name implies, this is the place 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.

Figure 8.2. The General Declarations section of a module is used to declare private and public variables.

A module is also made up of user-defined subroutines and functions. Figure 8.3 shows a subroutine called SayHello. Notice the drop-down list in the upper-left portion of the window titled Chap8Ex—basHello (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 the place 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).

Figure 8.3. An example of a user-defined subroutine called SayHello.

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; otherwise, 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. At first glance, you might think 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 will be given at all. You can totally avoid this kind of result by using Option Explicit.

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 8.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.

Figure 8.5. An event procedure for the Click event of the cmdHello command button.

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 8.5. To get to the event code of an object, follow these steps:

1. Click on the object in Design view and click the Property Sheet button on the toolbar, or right-click on the object and choose Properties from the context-sensitive menu.
2. Click on the Event properties tab.
3. Select the event for which you want to write code (for example, the On Click event).
4. Select [Event Procedure] from the drop-down list.
5. Click on the ellipsis button, which places you in the VBE within the event code for that object.

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

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:

1. Click to select the Create tab.
2. Open the Macro drop-down in the Other group and select Module (see Figure 8.6). The VBE appears, and Access places you in a new module.

   Figure 8.6. You use the Macro drop-down in the Other group to insert a new module.

   

3. Select Procedure from the Insert menu. The Add Procedure dialog box shown in Figure 8.7 appears.

   Figure 8.7. In the Add Procedure dialog box, you specify the name, type, and scope of the procedure you’re creating.

   

4. Type the name of the procedure.
5. Select Sub, Function, or Property as the Type of procedure.
6. To make the procedure available to your entire application, select Public as the Scope (Scope is covered later in this chapter in the section “Scope and Lifetime of Procedures”); to make the procedure private to this module, select Private.
7. Finally, indicate whether you want all the variables in the procedure to be static. (Static variables are discussed in this chapter under “Scope and Lifetime of Variables: Exposing Your Variables as Little as Possible.”) Then click OK.

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:

1. While in Design view of a form or report, click to select the Design tab. Select the View Code button in the Tools group. Access places you in the VBE.
2. Choose Procedure from the Insert menu to open the Insert Procedure dialog box.
3. Type the name of the procedure.
4. Select Sub, Function, or Property as the Type of procedure.
5. To make the procedure available to your entire application, select Public as the Scope; to make the procedure private to this module, select Private.
6. Finally, indicate whether you want all the variables in the procedure to be static. When you’re finished, click OK.

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.

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 keyword—Call 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 that 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, CHAP8EX.ACCDB:

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 8.8, 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. You can call the cbfIAmPublic procedure from anywhere within the application by using the following syntax (found in the Standard module basHello):

Figure 8.8. A public form 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 8.9).

Figure 8.9. A private procedure is visible only to subroutines and functions in the basUtils module.

The procedure shown in Figure 8.9, 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).

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 basVariables:

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.

Working with Variables

You must consider many issues when creating VBA variables. The way that you declare a variable determines its scope, its lifetime, and more. The following topics will help you better understand declaring variables in VBA.

Declaring Variables

There are several ways to declare variables in VBA. For example, you could simply declare x=10. With this method of variable declaration, you really aren’t declaring your variables at all; you’re essentially declaring them as you use them. This method is quite dangerous. It lends itself to typos and other problems. If you follow the practice recommended previously—of always using the Option Explicit statement—Access will not allow you to declare variables in this manner.

You could also type Dim intCounter; the Dim statement declares the variable. The only problem with this method is that you haven’t declared the type of the variable to the compiler, so it’s declared as a variant variable.

Another common mistake is declaring multiple variables on the same line, as in this example:

Dim intCounter, intAge, intWeight As Integer

In this line, only the last variable is explicitly declared as an integer variable. The other variables are implicitly declared as variants. If you’re going to declare multiple variables on one line, make sure each variable is specifically declared, as in the following example:

Dim intCounter As Integer, intAge As Integer, intWeight As Integer

The most efficient and bug-proof way to declare your variables is to strong-type them to the compiler and declare only one variable per line of code, as in this example:

Dim intCounter As Integer
Dim strName As String

As you can see, strong-typing declares the name of the variable as well as the type of data it can contain. This type of declaration enables the compiler to catch errors, such as storing a string in an integer variable, before your program runs. If implemented properly, this method can also reduce the resources needed to run your programs by selecting the smallest practical data type for each variable.

VBA Data Types

VBA offers several data types for variables. Table 8.1 shows a list of the available data types, the standard for naming them, the amount of storage space they require, the data they can store, and their default values.

Table 8.1. Data Types and Naming Conventions

Scope and Lifetime of Variables: Exposing Your Variables as Little as Possible

You have read about the different types of variables available in VBA. Like procedures, variables also have a scope. A variable can be declared as local, private (Module), or public in scope. You should try to use local variables in your code because they’re shielded from being accidentally modified by other routines.

Variables also have an attribute referred to as their lifetime. The lifetime of a variable reflects the time during which the variable actually exists and, therefore, the time during which its value is retained. In the following sections, we take a closer look at how to set the scope and lifetime of variables.

Local Variables

Local variables are available only in the procedure where they are declared. Consider this example (not included in Chap8ex):

This code can behave in one of three ways. If Option Explicit is in effect, meaning that all variables must be declared before they’re used, this code generates a compiler error. If the Option Explicit statement isn’t used, strAnimal is changed to Cat only within the context of the subroutine ChangeAnimal. If the Dim strAnimal As String statement is moved to the General Declarations section of the module, the variable’s value is changed to "Cat".

Static Variables: A Special Type of Local Variable

The following examples illustrate the difference between local and static variables. Local variables are reinitialized each time the code is called. You can run the following procedure by opening the form named frmScopeAndLifeTime and clicking the Local Age button. Notice that each time you run the procedure, the numeral 1 is displayed in the txtNewAge text box.

Each time this code runs, the Dim statement reinitializes intAge to zero. This is quite different from the following code, which illustrates the use of a static variable:

Each time this code executes, the variable called sintAge is incremented, and its value is retained. You can test this by opening on the website the form named frmScopeAndLifeTime and clicking the Static Age button.

Private Variables

So far, this discussion has been limited to variables that have scope within a single procedure. Private (module-level) variables can be seen by any routine in the module they were declared in, but not from other modules. Thus, they are private to the module. You declare private variables by placing a Private statement, such as the following, in the General Declarations section of a form, report, or Access module:

[General Declarations]
Option Explicit
Private mintAge As Integer

You can change the value of a variable declared as private from any subroutine or function within that module. For example, the following subroutine increments the value of the private variable mintAge by 1. You can run this code by opening the form named frmScopeAndLifeTime on the website and clicking the Module Age button.

Notice the naming convention of using the letter m to prefix the name of the variable, which denotes the variable as a private module-level variable. You should use private declarations only for variables that need to be seen by multiple procedures in the same module. Aim for making most of your variables local to make your code modular and more bulletproof.

Public Variables

You can access public variables from any VBA code in your application. They’re usually limited to things such as login IDs, environment settings, and other variables that must be seen by your entire application. You can place declarations of public variables in the General Declarations section of a module. The declaration of a public variable looks like this:

Option Explicit
Public gintAge As Integer

Notice the prefix g (a relic of the old Global variables), the proper prefix for a public variable declared in a Standard module. This standard is used because public variables declared in a Standard module are visible not only to the module they were declared in, but also to other modules. The following code, placed in the Click event of the cmdPublic command button, increments the public variable gintAge by 1. You can run this code by opening the form frmScopeAndLifeTime and clicking the Public Age button.

Adding Comments to Your Code

You add comments, which have been color-coded since the release of Access 97 (prior to Access 97 they were the same color as the programming code), to modules by using an apostrophe ('). You can also use the keyword Rem, but the apostrophe is generally preferred. You can place the apostrophe at the beginning of the line of code or anywhere within it. Anything following the apostrophe is considered a comment. Figure 8.10 shows code containing comments.

Figure 8.10. Code containing comments that clarify what the subroutine is doing.

Using the Line Continuation Character

Access Basic code, used in Access 2.0, didn’t have a line continuation character. Therefore, you had to scroll a lot, as well as pull out a bag of tricks to simulate continuing a line of code. With VBA, Access 97 and higher solve this problem: The line continuation character is an underscore. Figure 8.11 illustrates the use of this character.

Figure 8.11. The line continuation character is used to improve the readability of a long line of code.

Using the VBA Control Structures

VBA gives the developer several different constructs for looping and decision processing. The most commonly used ones are covered in the following sections and are found in the form called frmControlStructures.

If...Then...Else

The If...Then...Else construct evaluates whether a condition is True. In the following example, anything between If and Else will occur if the statement evaluates to True, and any code between Else and End If will be executed if the statement evaluates to False. The Else is optional.

This code tests whether the text box called txtName or the text box txtAge contains a Null. A different message is displayed depending on whether one of the text boxes contains a Null value.

One-line If statements are also permitted; they look like this:

If IsNull(Me.txtvalue.Value) Then MsgBox "You must enter a value"

However, this format for an If statement isn’t recommended because it reduces readability.

Another useful form of an If statement is ElseIf, which enables you to evaluate an unlimited number of conditions in one If statement. The following code gives you an example: (This example is not included in CHAP8EX.)

The conditions in an If statement are evaluated in the order in which they appear. For this reason, it’s best to place the most common conditions first. After a condition is met, execution continues immediately after End If. If no conditions are met, and there’s no Else statement, execution will also continue immediately after End If.

The Immediate If (IIf)

An Immediate If (IIf) is a variation of an If statement. It’s actually a built-in function that returns one of two values, depending on whether the condition being tested is true or false. Here’s an example: (This code is not included in CHAP8EX.)

This function evaluates the curSales parameter to see whether its value is greater than or equal to $100,000. If it is, the function returns the string "Great Job"; otherwise, the function returns the string "Keep Plugging".

The IIf function is most often used in a calculated control on a form or report, or to create a new field in a query. Probably the most common example is an IIf expression that determines whether the value of a control is IsNull. If the value is IsNull, you can have the expression return a zero or an empty string; otherwise, you can have the expression return the value in the control. The following expression, for example, evaluates the value of a control on a form:

This expression displays either a zero or the value for freight in the control called txtFreight.

The Conditional If: Conditional Compilation

Conditional compilation enables you to selectively execute blocks of code. This feature is useful in several situations:

• When you want certain blocks of code to execute in the demo version of your product and other blocks to execute in your product’s retail version
• When you’re distributing your application in different countries and want certain blocks of code to apply to some countries but not to others
• When you want certain blocks of code to execute only during the testing of your application

Conditional compilation is done by using the #If...Then...#Else directive, as shown here and found under the Conditional Compilation command button on the frmControlStructures form:

You can declare the compiler constant, in this case, Language, in one of two places: in a module’s General Declarations section or in the Project Properties dialog box. A compiler constant declared in the General Declarations section of a module looks like this:

#Const Language = "Spanish"

The disadvantage of this constant is that you can’t declare it as public. It isn’t possible to create public compiler constants by using the #Const directive. This means that any compiler constants declared in a module’s Declarations section can be used only within that module. The major advantage of declaring this type of compiler constant is that it can contain a string. For example, the compiler constant Language, defined in the preceding paragraph, is given the value "Spanish".

Public compiler constants can be declared by modifying the Project Properties. Because they are public in scope, compiler constants declared in the Project Properties can be referred to from anywhere in your application. The major limitation on compiler directives set up in Project Properties is that they can contain only integers. For example, you would have to enter Language = 1.

To define compiler constants using the Project Properties dialog box, right-click within the Project window and select projectx Properties, where projectx is the name of the project you are working with. You can now enter the values you need into the text box labeled Conditional Compilation Arguments. You can enter several arguments by separating them with a colon, such as Language = 1 : Version = 2.

With the compiler directive Language=1, the code would look like this:

Notice that ConditionalIf now evaluates the constant Language against the integer of 1.

It’s important to understand that using conditional constants isn’t the same as using regular constants or variables with the standard If...Then...Else construct. Regular constants or variables are evaluated at runtime, which requires processing time each occasion the application is run. Conditional constants and conditional If...Then...Else statements control which sections of code are actually compiled. All resolution is completed at compile time; this eliminates the need for unnecessary processing at runtime.

Select Case

Rather than using multiple If...Then...Else statements, using a Select Case statement is often much clearer, as shown here. This Select Case statement is found under the Select Case command button of the frmControlStructures form.

This subroutine first uses the Nz function to convert a Null or empty value in the txtAge control to 0; otherwise, the value in txtAge is stored in intAge. The Select Case statement then evaluates intAge. If the value is 0, the code displays a message box with You Must Enter a Number. If the value is between 1 and 18 inclusive, the code displays a message box saying You Are Just a Kid. If the user enters 19, 20, or 21, the code displays the message You are Almost an Adult. If the user enters a value between 22 and 40 inclusive, the code displays the message Good Deal. If the user enters a value greater than 40, the code displays the message Getting Up There!; otherwise, the user gets a message indicating that she entered an invalid number.

Looping

Several looping structures are available in VBA; most are discussed in this section. Take a look at the following example of a looping structure (found under the Do While...Loop command button of the frmControlStructures form):

In this structure, if the value in the txtAge text box is greater than or equal to 35, the code in the loop is not executed. If you want the code to execute unconditionally at least one time, you need to use the following construct (found under the Do...Loop While command button of the frmControlStructures form):

This code will execute one time, even if the value in the txtAge text box is set to 35. The Do While...Loop in the previous example evaluates before the code is executed, so it doesn’t ensure code execution. The Do...Loop While is evaluated at the end of the loop and therefore guarantees execution.

Alternatives to the Do While...Loop and the Do...Loop While are Do Until...Loop and Do...Loop Until. Do Until...Loop (found under the Do Until...Loop command button of the frmControlStructures form) works like this:

This loop continues to execute until the value in the txtAge text box becomes equal to 35. The Do...Loop Until construct (found under the Do...Loop Until command button of the frmControlStructures form) is another variation:

As with the Do...Loop While construct, the Do...Loop Until construct doesn’t evaluate the condition until the end of the loop, so the code in the loop is guaranteed to execute at least once.

For...Next

The For...Next construct is used when you have an exact number of iterations you want to perform. It looks like this and is found under the For...Next command button of the frmControlStructures form:

Note that intCounter is self-incrementing. The start value and the stop value can both be variables. A For...Next construct can also be given a step value, as shown in the following. (The counter is incremented by the value of Step each time the loop is processed.)

With...End With

The With...End With statement executes a series of statements on a single object or user-defined type. Here’s an example (found under the With...End With command button of the frmControlStructures form:

This code performs four operations on the txtAge text box, found on the form it’s run on. The code modifies the BackColor, ForeColor, Value, and FontName properties of the txtAge text box.

For Each...Next

The For Each...Next statement executes a group of statements on each member of an array or collection. The following example (found under the For Each...Next command button of the frmControlStructures form) illustrates the use of this powerful construct:

This code loops through each control on the form, modifying the FontSize property of each control.

As in the following example, the With...End With construct is often used along with the For Each...Next construct:

This code loops through each control on a form; the ForeColor, FontName, and FontSize properties of each control on the form are modified.

Passing Parameters and Returning Values

Both subroutines and functions can receive arguments (parameters), but subroutines can return values only when you use the ByRef keyword. The following subroutine (found under the Pass Parameters command button of the frmParametersAndReturnValues form) receives two parameters: txtFirst and txtLast. It then displays a message box with the first character of each of the parameters that was passed.

Notice that the values in the controls txtFirstName and txtLastName from the current form (represented by the Me keyword) are passed to the subroutine called Initials. The parameters are received as strFirst and strLast. The code displays the first left character of each parameter in the message box.

The preceding code simply passes values and then operates on those values. This next example (found under the Return Values command button of the frmParametersAndReturnValues form) uses a function to return a value:

Notice that this example calls the function ReturnInit, sending values contained in the two text boxes as parameters. The function sets ReturnInit (the name of the function) equal to the first two characters of the strings. This returns the value to the calling routine (cmdReturnValues _Click) and sets strInitials equal to the return value.

Executing Procedures from the Module Window

You can easily test procedures from the Module window in Access 2007. Simply click anywhere inside the procedure you want to execute, and then press the F5 key or click the Run Sub/UserForm button on the toolbar. The procedure you’re in will execute as though you had called it from code or from the Immediate pane of the Debug window.

The DoCmd Object: Performing Macro Actions

The Access environment is rich with objects that have built-in properties and methods. By using VBA code, you can modify the properties and execute the methods. One of the objects available in Access is the DoCmd object, used to execute macro actions in Visual Basic procedures. The macro actions are executed as methods of the DoCmd object. The syntax looks like this:

DoCmd.ActionName [arguments]

Here’s a practical example:

DoCmd.OpenReport strReportName, acViewPreview

The OpenReport method is a method of the DoCmd object; it runs a report. The first two parameters that the OpenReport method receives are the name of the report you want to run and the view in which you want the report to appear (Preview, Normal, or Design). The name of the report and the view are both arguments of the OpenReport method.

Most macro actions have corresponding DoCmd methods that you can find in Help, but some don’t. They are AddMenu, MsgBox, RunApp, RunCode, SendKeys, SetValue, StopAllMacros, and StopMacro. The SendKeys method is the only one that has any significance to you as a VBA programmer. The remaining macro actions either have no application to VBA code, or you can perform them more efficiently by using VBA functions and commands. The VBA language includes a MsgBox function, for example, that’s far more robust than its macro action counterpart.

Many of the DoCmd methods have optional parameters. If you don’t supply an argument, its default value is assumed. You can use commas as place markers to designate the position of missing arguments, as shown here:

DoCmd.OpenForm "frmOrders", , ,"[OrderAmount] > 1000"

The OpenForm method of the DoCmd object receives seven parameters; the last six parameters are optional. In the example, two parameters are explicitly specified. The first is the name of the form ("FrmOrders"), a required parameter. The second and third parameters have been omitted, meaning that you’re accepting their default values. The commas, used as place markers for the second and third parameters, are necessary because one of the parameters following them is explicitly designated. The fourth parameter is the Where condition for the form, which has been designated as the record in which the OrderAmount is greater than 1,000. The remaining parameters haven’t been referred to, so default values are used for them.

If you prefer, you can use named parameters to designate the parameters that you are passing. Named parameters, covered later in this chapter, can greatly simplify the preceding syntax. With named parameters, you don’t need to place the arguments in a particular order, and you don’t need to worry about counting commas. The preceding syntax can be changed to the following:

DoCmd.OpenForm FormName:="frmOrders", WhereCondition:=
"[OrderAmount] > 1000"

Working with Built-In Functions

VBA has a rich and comprehensive function library as well as tools to assist in their use.

Built-In Functions

Some of the more commonly used VBA functions and examples are listed in the following sections. On some rainy day, go through the online Help to become familiar with the rest.

Format

The Format function formats expressions in the style specified. The first parameter is the expression you want to format; the second is the type of format you want to apply. Here’s an example of using the Format function:

Instr

The Instr function returns the position where one string begins within another string:

InStrRev

InStrRev begins searching at the end of a string and returns the position where one string is found within another string:

Notice that the InStr function returns 3 as the starting position for the backslash character within "c:my documentsmy file.txt", whereas the InStrRev function returns 16 as the starting position for the backslash character in the same string. The reason is that InStr starts searching at the beginning of the string, continuing until it finds a match, whereas InStrRev begins searching at the end of the string, continuing until it finds a match.

Left

Left returns the leftmost number of characters in a string:

Right

Right returns the rightmost number of characters in a string:

Mid

Mid returns a substring of a specified number of characters in a string. This example starts at the fourth character and returns five characters:

UCase

UCase returns a string that is all uppercase:

DatePart

DatePart returns the specified part of a date:

DateDiff

DateDiff returns the interval of time between two dates:

DateAdd

DateAdd returns the result of adding or subtracting a specified period of time to a date:

Replace

Replace replaces one string with another:

StrRev

StrRev reverses the order of text in a string:

MonthName

MonthName returns the text string associated with a month number:

Functions Made Easy with the Object Browser

With the Object Browser, you can view members of an ActiveX component’s type library. In plain English, the Object Browser enables you to easily browse through a component’s methods, properties, and constants. You can also copy information and add it to your code. It even adds a method’s parameters for you. The following steps let you browse among the available methods, copy the method you want, and paste it into your code:

1. With the VBE active, select View, Object Browser from the menu (note that the menu line also shows an icon that you can use from the toolbar), or press F2 to open the Object Browser window (see Figure 8.12).

   Figure 8.12. The Object Browser showing all the classes in the CHAP8EX database and all the members in the basUtils module.

   

2. The Object Browser window is divided into two parts: the upper part of the window and the lower part. The drop-down list at the upper left of the window is used to filter the items to be displayed in the lower part of the window. Use this drop-down list to select the project or library whose classes and members you want to view in the lower part of the window.
3. In the lower portion of the window, select the class from the left list box, which lists Class modules, templates for new objects, standard modules, and modules containing subroutines and functions.
4. Select a related property, method, event, constant, function, or statement from the Members Of list box. In Figure 8.12, the basUtils module is selected from the list box on the left. Notice that the subroutines and functions included in basUtils appear in the list box on the right.
5. Click the Copy to Clipboard button (third from the right in the upper toolbar within the Object Browser window) to copy the function name and its parameters to the Clipboard so that you can easily paste it into your code.

The example in Figure 8.12 shows choosing a user-defined function selected from a module in a database, but you can also select any built-in function. Figure 8.13 shows an example in which the DatePart function is selected from the VBA library. The Object Browser exposes all libraries referred to by the database and is covered in more detail in Chapters 9 and 24.

Figure 8.13. The Object Browser with the VBA library selected.

Working with Constants

A constant is a meaningful name given to a meaningless number or string. Constants can be used only for values that don’t change at runtime. A tax rate or commission rate, for example, might be constant throughout your application. There are three types of constants in Access:

• Symbolic
• Intrinsic
• System defined

Symbolic constants, created by using the Const keyword, are used to improve the readability of your code and make code maintenance easier. Instead of referring to the number .0875 every time you want to refer to the tax rate, you can refer to the constant mccurTaxRate. If the tax rate changes, and you need to modify the value in your code, you’ll make the change in only one place. Furthermore, unlike the number .0875, the name mccurTaxRate is self-documenting.

Intrinsic constants are built into Microsoft Access; they are part of the language itself. As an Access programmer, you can use constants supplied by Microsoft Access, Visual Basic, Data Access Objects (DAO), and ADO. You can also use constants provided by any object libraries you’re using in your application.

There are only three system-defined constants—True, False, and Null—and they are available to all applications on your computer.

Working with Symbolic Constants

As mentioned, you declare a symbolic constant by using the Const keyword. You can declare a constant in a subroutine or function, or in the General section of a Form or Report module. You can strong-type constants in Access 2000 and later. The declaration and use of a private constant looks like this:

Private Const TAXRATE As Currency = .0875

This code, when placed in a module’s Declarations section, creates a private constant called TAXRATE and sets it equal to .0875. Here’s how you use the constant in code:

This routine multiplies the curSaleAmount, received as a parameter, by the constant TAXRATE. It returns the result of the calculation by setting the function name equal to the product of the two values. The advantage of the constant in this example is that the code is more readable than TotalAmount = curSaleAmount * .0875 would be.

Scoping Symbolic Constants

Just as regular variables have scope, user-defined constants have scope. In the preceding example, you created a private constant. The following statement, when placed in a module’s General Declarations section, creates a public constant:

Public Const TAXRATE As Currency = .0875

Because this constant is declared as public, you can access it from any subroutine or function (including event routines) in your entire application. To better understand the benefits of a public constant, consider a case in which you have many functions and subroutines all making reference to the constant TAXRATE. Imagine what would happen if the tax rate were to change. If you hadn’t used a constant, you would need to search your entire application, replacing the old tax rate with the new tax rate. However, because your public constant is declared in one place, you can easily go in and modify the one line of code where this constant is declared.

By definition, the values of constants cannot be modified at runtime. If you try to modify the value of a constant, you get this VBA compiler error:

Assignment to constant not permitted

Figure 8.14 illustrates this message box. You can see that an attempt is made to modify the value of the constant TAXRATE, which results in a compile error.

Figure 8.14. Trying to modify the value of a constant.

If you need to change the value at runtime, you should consider storing the value in a table instead of declaring it as a constant. You can read the value into a variable when the application loads and then modify the variable if needed. If you choose, you can write the new value back to the table.

Working with Intrinsic Constants

Microsoft Access declares a number of intrinsic constants that you can use in Code, Form, and Report modules. Because they’re reserved by Microsoft Access, you can’t modify their values or reuse their names; however, you can use them at any time without declaring them.

You should use intrinsic constants whenever possible in your code. Besides making your code more readable, they make your code more portable to future releases of Microsoft Access. Microsoft might change the value associated with a constant, but Microsoft isn’t likely to change the constant’s name. All intrinsic constants appear in the Object Browser; to activate it, simply click the Object Browser tool on the Visual Basic toolbar. To view the constants that are part of the Access library, select Access from the Object Browser’s Project/Library drop-down list. Click Constants in the Classes list box, and a list of those constants is displayed in the Members Of ‘Constants’ list box (see Figure 8.15).

Figure 8.15. Using the Object Browser to view intrinsic constants.

In the list shown in Figure 8.15, all VBA constants are prefixed with vb, all Data Access Object constants are prefixed with db, and all constants that are part of the Access language are prefixed with ac. To view the Visual Basic language constants, select VBA from the Project/Library drop-down list and Constants from the Classes list box. If the project you are working with has a reference to the ADO library, you can view these constants by selecting ADODB from the Project/Library drop-down list. Click <globals>. A list of the ADODB constants appears. (These constants have the prefix ad.)

Another way to view constants is within the context of the parameter you’re working with in the Code window. Right-click the name of a parameter and select List Constants to display the constants associated with the parameter.

Working with the Visual Basic Editor Tools

Effectively using the tips and tricks of the trade, many of which are highlighted in this chapter, can save you hours of time. These tricks help you to navigate around the coding environment, as well as to modify your code quickly and easily. They include the capability to easily zoom to a user-defined procedure, search and replace within modules, get help on VBA functions and commands, and split the Code window so that two procedures can be viewed simultaneously.

Access 2007 offers a very rich development environment. It includes several features that make coding easier and more pleasant for you. These features include the capability to do the following:

• List properties and methods
• List constants
• Get quick information on a command or function
• Get parameter information
• Enable Access to finish a word for you
• Get a definition of a function

All these features that help you with coding are available with a right-click when you place your cursor within the Module window.

List Properties and Methods

With the List Properties and Methods feature, you can view all the objects, properties, and methods available for the current object. To invoke this feature, right-click after the name of the object and select List Properties, Methods. (You can also press Ctrl+J.) The applicable objects, properties, and methods appear in a list box (see Figure 8.16). To find the appropriate object, property, or method in the list, use one of these methods:

• Begin typing the name of the object, property, or method.
• Use the up- and down-arrow keys to move through the list.
• Scroll through the list and select your choice.

Figure 8.16. A list of properties and methods for the TextBox object.

Use one of these methods to insert your selection:

• Double-click the entry.
• Click to select the entry. Then press Tab to insert, or Enter to insert and move to the next line.

List Constants

The List Constants feature, which is part of IntelliSense, opens a drop-down list displaying valid constants for a property you have typed and for functions with arguments that are constants. It works in a similar manner to the List Properties and Methods feature. To invoke it, right-click after the name of the property or argument (in cases in which multiple arguments are available, the previous argument must be delimited with a comma) and select List Constants (or press Ctrl+Shift+J). A list of valid constants appears (see Figure 8.17). You can use any of the methods listed in the preceding section to select the constant you want.

Figure 8.17. A list of constants for the vbMsgBoxStyle parameter.

Quick Info

The Quick Info feature gives you the full syntax for a function, statement, procedure, method, or variable. To use this feature, right-click after the name of the function, statement, procedure, method, or variable, and then select Quick Info (or press Ctrl+I). A tip appears, showing the valid syntax for the item (see Figure 8.18). As you type each parameter in the item, it’s displayed in boldface type until you type the comma that delineates it from the next parameter.

Figure 8.18. The syntax for the MsgBox function.

Parameter Info

The Parameter Info feature gives you information about the parameters of a function, statement, or method. To use this feature, after the delimiter that denotes the end of the function, statement, or method name, right-click and select Parameter Info (or press Ctrl+Shift+I). A pop-up list appears with information about the parameters of the function or statement. This list doesn’t close until you enter all the required parameters, you complete the function without any optional parameters, or you press the Esc key.

Complete Word

The Complete Word feature completes a word you’re typing. To use this feature, you must first type enough characters for Visual Basic to recognize the word you want. Next, right-click and select Complete Word (or press Ctrl+Spacebar). Visual Basic then finishes the word you’re typing.

Definition

The Definition feature shows the place in the Code window where the selected variable or procedure is defined. To get a definition of a variable or procedure, right-click in the name of the variable or procedure of interest, and select Definition (or press Shift+F2). Your cursor is moved to the module and location where the variable or procedure was defined.

As you become more proficient with VBA, you can create libraries of VBA functions and subroutines. When you’re viewing a call to a particular subroutine or function, you often want to view the code behind that function. Fortunately, VBA gives you a quick and easy way to navigate from procedure to procedure. Assume that the following code appears in your application:

If you want to quickly jump to the procedure called EvaluateAge, all you need to do is place your cursor anywhere within the name, EvaluateAge, and then press Shift+F2. This procedure immediately moves you to the EvaluateAge procedure. Ctrl+Shift+F2 takes you back to the routine you came from (in this case, cmdOkay_Click). This procedure works for both functions and subroutines.

Mysteries of the Coding Environment Solved

If you’re a developer who’s new to VBA, you might be confused by the VBE. We will begin by talking about the Code window. The Code window has two combo boxes, shown in Figure 8.19. The combo box on the left lists objects. For a form or report, the list includes all its objects; for a standard module, which has no objects, only (General) appears.

Figure 8.19. The Code window with the Object combo box open.

The combo box on the right lists all the event procedures associated with a particular object. Figure 8.20 shows all the event procedures associated with a command button. Notice that the Click event is the only one that appears in bold because it’s the only event procedure that has been coded.

Figure 8.20. The Code window with the Procedure combo box open.

The Project Window

The Project window, shown in Figure 8.21, enables you to easily maneuver between the modules behind the objects within your database. The elements of your project are displayed hierarchically in a tree view within the Project window. All elements of the project are divided into Microsoft Access Classes and Modules. All Form, Report, and Class modules are found within the Microsoft Access Classes. All Standard modules are found within Modules. To view the code behind an object, simply double-click the object within the Project window. To view the object, such as a form, single-click the name of the form in the Project window and then click the View Object tool (the second icon from the left on the Project window toolbar). You are returned to Microsoft Access with the selected object active.

Figure 8.21. The Project window showing all the classes and modules contained within the Chap8Ex project.

The Properties Window

The Properties window, pictured in Figure 8.22, enables you to view and modify object properties from within the VBE. At the top of the Properties window is a combo box that allows you to select the object whose properties you want to modify. The objects listed in the combo box include the parent object selected in the Project window (for example, the form) and the objects contained within the parent object (for example, the controls). After an object is selected, its properties can be modified within the list of properties. The properties can be viewed either alphabetically or categorically. In the example, the command button cmdOK is selected. The properties of the command button are shown by category.

Figure 8.22. The Properties window showing the properties of a command button displayed categorically.

The View Microsoft Access Tool

If at any time you want to return to the Access application environment, simply click the View Microsoft Access icon (the left icon) on the toolbar. You can then return to the VBE by using the taskbar or using one of the methods covered earlier in this chapter.

Find and Replace

Often, you name a variable only to decide later that you want to change the name. VBA comes with an excellent find-and-replace feature to help you with this change. You can simply search for data, or you can search for a value and replace it with some other value. To invoke the Find dialog box, shown in Figure 8.23, choose Edit, Find or press Ctrl+F.

Figure 8.23. The Find dialog box is set up to search for strMessage in the current module.

Type the text you want to find in the Find What text box. Notice that you can search in the Current Procedure, Current Module, Current Project, or Selected Text. The option Find Whole Word Only doesn’t find the text if it’s part of another piece of text. For example, if you check Find Whole Word Only and then search for Count, VBA doesn’t find Counter. Other options include toggles for case sensitivity and pattern matching.

You can also use the Replace dialog box to search for text and replace it with another piece of text (see Figure 8.24). You can invoke this dialog box by selecting Edit, Replace from the menu or by pressing Ctrl+H (or Alt+E, E). It offers all the features of the Find dialog box but also enables you to enter Replace With text. In addition, you can select Replace or Replace All. Replace asks for confirmation before each replacement, but Replace All replaces text without this prompt. I recommend that you take the time to confirm each replacement because it’s all too easy to miscalculate the pervasive effects of a global find-and-replace.

Figure 8.24. The Replace dialog box is set to find strMessage and replace it with strNewMessage in the current project.

Help

A very useful but underutilized feature of VBA is the ability to get context-sensitive help while coding. With your cursor placed anywhere in a VBA command or function, press the F1 key to get context-sensitive help on that command or function. Most of the help topics let you view practical examples of the function or command within code. Figure 8.25 shows help on the With...End With construct. Notice that the Help window includes the syntax for the command, a detailed description of each parameter included in the command, and remarks about using the command. If you scroll down, examples of the construct appear that you can copy and place into a module (see Figure 8.26). This feature is a great way to learn about the various parts of the VBA language.

Figure 8.25. Help on With...End With.

Figure 8.26. An example of With...End With.

Splitting the Code Window

You can split the VBA Code window so that you can look at two routines in the same module at the same time. This option is useful if you’re trying to solve a problem involving two procedures or event routines in a large module. To split your Code window, as shown in Figure 8.27, choose Window, Split.

Figure 8.27. A split Code window lets you view two routines.

Notice the splitter. Place your mouse cursor on the gray splitter button just above the Code window’s vertical scrollbar. By clicking and dragging, you can size each half of the window. The window can be split into only two parts. After you have split it, you can use the Object and Procedure drop-down lists to navigate to the procedure of your choice. The drop-down lists will work for either of the two panes of the split window, depending on which pane was last selected.

Using Bookmarks to Save Your Place

The Access 2007 coding environment enables you to create place markers called bookmarks so that you can easily return to key locations in your modules. To add a bookmark, right-click on the line of code where you will place the bookmark and choose Toggle, Bookmark, or choose Bookmarks, Toggle Bookmark from the Edit menu. You can add as many bookmarks as you like.

To navigate between bookmarks, choose Edit, Bookmarks, Next Bookmark, or Edit, Bookmarks, Previous Bookmark. A bookmark is a toggle. To remove one, you simply choose Toggle, Bookmark from the shortcut menu or Bookmarks, Toggle Bookmark from the Edit menu. If you want to clear all bookmarks, choose Edit, Bookmarks, Clear All Bookmarks. Bookmarks are not saved when you close the database.

Customizing the VBE

Access 2007 provides Access programmers with significant opportunity to customize the look and behavior of the VBE. To view and customize the environment options, choose Tools, Options with the VBE active. Figure 8.28 shows the Options dialog box; its different aspects are discussed in detail in the following sections.

Figure 8.28. The Options dialog box.

Coding Options—The Editor Tab

The coding options available to you are found under the Editor tab of the Options dialog box. They include Auto Indent, Tab Width, Auto Syntax Check, Require Variable Declaration, Auto List Members, Auto Quick Info, and Auto Data Tips.

The Auto Indent feature invokes the automatic indenting of successive lines of code. This means that when you indent one line, all other lines are indented to the same position until you specify otherwise. I recommend that you use this feature.

The Tab Width feature determines the number of characters that Access indents each line. It’s important that you do not change this number in the middle of a project; otherwise, different parts of the program will be indented differently.

The Auto Syntax Check feature determines whether Access performs a syntax check each time you press Enter after typing a single line of code. Many developers find this option annoying. It’s not uncommon to type a line of code and notice a typo in a previous line of code. You want to rectify the error before you forget, so you move off the incomplete line of code you’re typing, only to get an error message that your syntax is incorrect. I recommend that you turn off this feature.

The Require Variable Declaration option is a must. If this option is turned on, all variables must be declared before they are used. This important feature, when set, places the Option Explicit line in the Declarations section of every module you create. You’re then forced to declare all variables before they’re used. The compiler identifies many innocent typos at compile time, rather than by your users at runtime. I strongly recommend that you use this feature.

The Auto List Members option determines whether the List Properties/Methods and List Constants features are automatically invoked as you type code in the Code window. They help you in your coding endeavors by presenting a valid list of properties, methods, and constants. I recommend that you use these features. For more about these features, see Chapter 9.

The Auto Quick Info feature determines whether the syntax of a procedure or method is automatically displayed. If this option is selected, the syntax information is displayed as soon as you type a procedure or method name followed by a space, period, or opening parenthesis. I recommend that you use this feature.

The Auto Data Tips feature is used when you’re debugging. It displays the current value of a selected value when you place your mouse pointer over the variable in Break mode. I recommend that you use this feature. This feature is discussed in Chapter 16.

Code Color, Fonts, and Sizes—The Editor Format Tab

In Access 2007, you can customize code colors, fonts, and sizes within the coding environment. You can also specify the foreground and background colors for the Code window text, selection text, syntax error text, comment text, keyword text, and more. You can select from any of the Windows fonts and sizes for the text in the Code window. For a more readable Code window, select the FixedSys font.

General Options—The General Tab

The General tab contains miscellaneous options that affect the behavior of the development environment. For example, the Show Grid option determines whether a form grid is displayed, and the Grid Units are used to designate the granularity of the gridlines. The other options on this tab are discussed in Chapter 13.

Docking Options—The Docking Tab

The Docking tab enables you to specify whether the windows within the VBE are dockable. A window is said to be dockable if you can lock it alongside and dock it to another window. It is not dockable when you can move it anywhere and leave it there. The windows you can dock include the Immediate, Locals, Watch, Project, Properties, and Object Browser windows.

Practical Examples: Using Event Routines, User-Defined Functions, and Subroutines

The CHAP8.ACCDB database includes two forms: frmClients and frmProjects. The frmClients form contains two command buttons. The first command button is used to save changes to the underlying record source (the tblClients table). The code looks like this:

The code, placed under the cmdSave command button on the frmClients form, executes the RunCommand method of the DoCmd object. The acCmdSaveRecord intrinsic constant, when used as a parameter to the RunCommand method, causes changes made to the form to be saved to the underlying data source.

The second command button is used to undo changes made to the current record. The code looks like this:

This code is found under the cmdUndo button on the frmClients form. It executes the RunCommand method of the DoCmd object. The acCmdUndo intrinsic constant, when used as a parameter to the RunCommand method, undoes changes made to the form.

The code originally located under the cmdViewProjects was generated by the command button wizard (as covered in Chapter 5, “What Every Developer Needs to Know About Forms”). It looked like this:

The code first declared two variables: one for the name of the form to be opened and the other to hold the criteria used to open the form. It then assigned a value to the stDocName variable as well as to the stLinkCriteria variable. Finally, it used the OpenForm method of the DoCmd object to open the frmProjects form, passing the value in stLinkCriteria as the WHERE clause for the OpenForm method. This wizard-generated code is inefficient. It uses variables that are not necessary. More importantly, it is difficult to read. To eliminate both problems, the code is changed as follows:

Although the RecordSource appears to be the entire tblProjects table, this is not the case. The key to the solution is found in the frmProjects form. The code in the Open event of the frmProjects form looks like this:

This code first uses a user-defined function called IsLoaded to determine whether the frmClients form is loaded. (The mechanics of the IsLoaded function are discussed in the following text.) The function returns True if the frmClients form is loaded, and False if it is not. If the frmClients form is not loaded, a message is displayed to the user, and the loading of the frmProjects form is canceled. If the frmClients form is loaded, the RecordSource property of the frmProjects form is determined by the WHERE clause passed as part of the OpenForm method. Even in a client/server environment, Access sends only the appropriate project records over the network wire.

The IsLoaded function looks like this:

The function declares two user-defined constants. These constants are intended to make the function more readable. The built-in SysCmd function is used to determine whether the form whose name is received as a parameter is loaded. The SysCmd function, when passed the intrinsic constant acSysCmdGetObjectState as the first argument and acForm as the second argument, attempts to determine the state of the form whose name is passed as the third argument. The IsLoaded function returns True to its caller if the form is loaded, and False if it is not. An alternative to this function is the following:

This function is much shorter and more efficient but is less readable. It simply places the return value from the SysCmd directly into the return value for the function.

In addition to the save and undo that are included in the frmClients form, this version of the frmProjects form contains one other routine. The BeforeUpdate event of the form, covered in Chapter 10, “Advanced Form Techniques,” executes before the data underlying the form is updated. The code in the BeforeUpdate event of the frmProjects form looks like this:

This code tests to see whether the project begin date falls after the project end date. If so, a message is displayed to the user, and the update is canceled.

Summary

A strong knowledge of the VBA language is imperative for the Access developer. This chapter has covered all the basics of the VBA language. You have learned the differences between Code, Form, and Report modules and how to effectively use each. You have also learned the difference between event procedures and user-defined subroutines and functions. To get the most mileage out of your subroutines and functions, you have learned how to pass parameters to, and receive return values from, procedures.

Variables are used throughout your application code. Declaring each variable with the proper scope and lifetime helps make your application bulletproof and easy to maintain. Furthermore, selecting an appropriate variable type ensures that the minimal amount of memory is consumed and that your application code protects itself. Effectively using control structures and built-in functions gives you the power, flexibility, and functionality required by even the most complex of applications. Finally, a strong command of the VBE is imperative to a successful development experience!