If you prefer the keyboard to the mouse (as I do), then you may want to use keyboard shortcuts. Here are some tips.

The simplest way to get help on any particular item is to place the cursor on that item and hit the F1 key. This works not only for VBA language keywords but also for portions of the VBA IDE.

Note that Microsoft provides multiple help files for Access, the VBA language, and the Access object model. While this is quite reasonable, occasionally the help system gets a bit confused and refuses to display the correct help file when I strike the F1 key.

Note also that a standard installation of Microsoft Office does not install the VBA help files for the various applications. Thus, you may need to run the Office setup program and install Access VBA help by selecting that option in the appropriate setup dialog box. (Do not confuse Access help with Access VBA help.)

There are two ways to create a new procedure (that is, a subroutine or a function) within a code module. First, after selecting the correct project in the Project Explorer, we can select the Procedure option from the Insert menu. This will produce the dialog box shown in Figure 9-1. Just type in the name of the procedure, and select Sub or Function (the Property choice is used with custom objects in a class module). We will discuss the issue of Public versus Private procedures and static variables later in this chapter.

Figure 9-1. The Add Procedure dialog box

A simpler alternative is to begin typing:

SubSubName

or:

FunctionFunctionName

in any code window (following the current End Sub or End Function statement, or in the general declarations section). As soon as the Enter key is struck, Access will move the line of code to a new location and thereby create a new subroutine. (It will even add the appropriate ending—End Sub or End Function.)

The VBA IDE can be in any one of three modes: run mode , break mode, or design mode. When the IDE is in design mode, we can write code.

Run mode occurs when a procedure is running. To run (or execute) a procedure, just place the cursor anywhere within the procedure code, and hit the F5 key (or select Run from the Run menu). If for some reason a running procedure seems to be hanging, we can usually stop the procedure by hitting Ctrl+Break (hold down the Control key and hit the Break key).

Break mode is entered when a running procedure stops because of either an error in the code or a deliberate act on our part (described a bit later). In particular, if an error occurs, Access will stop execution and display an error dialog box, an example of which is shown in Figure 9-2.

Figure 9-2. An error message

Error dialog boxes offer a few options: end the procedure, get help (such as it may be) with the problem, or enter break mode to debug the code. In the latter case, Access will stop execution of the procedure at the offending code and highlight that code in yellow. We will discuss the process of debugging code a bit later.

Aside from encountering an error, there are several ways we can deliberately enter break mode for debugging purposes:

To exit from break mode, choose Reset from the Run menu.

Note that the caption in the title bar of the VBA IDE indicates which mode is currently active. The caption contains the word “[running]” when in run mode and “[break]” when in break mode.

In computer jargon, an error is referred to as a bug . In case you are interested in the origin of this word, the story goes that when operating the first large-scale digital computer, called the Mark I, an error was traced to a moth that had found its way into the hardware. Incidentally, the Mark I (circa 1944) had 750,000 parts, was 51 feet long, and weighed over 5 tons. How about putting that on your desktop? It also executed about one instruction every 6 seconds, as compared to over 200 million instructions per second for a Pentium!

Errors can be grouped into three types based on when they occur — design time, compile time, or runtime.

Design-Time and Compile-Time Errors

As the name implies, a design-time error occurs during the writing of code. Perhaps the nicest feature of the Visual Basic Editor is that it can be instructed to watch as we type code and stop us when we make a syntax error. This automatic syntax checking can be enabled or disabled in the Options dialog box shown in Figure 9-3, but I strongly suggest that you keep it enabled.

Figure 9-3. The Options dialog box

Notice also that there are other settings related to the design-time environment, such as how far to indent code in response to the Tab key. We will discuss some of these other settings a bit later.

Illustrating automatic syntax checking, Figure 9-4 shows what happens when we deliberately enter the syntactically incorrect statement x == 5 and then attempt to move to another line. Note that Microsoft refers to this type of error as a compile error in the dialog box, and perhaps we should as well. However, it seems more descriptive to call it a design-time error or just a syntax error.

Figure 9-4. A syntax error message

Before a program can be executed, it must be compiled—or translated into a language that the computer can understand. The compilation process occurs automatically when we request that a program be executed. We can also specifically request compilation by choosing the Compile Project item under the Debug menu. If Access encounters an error while compiling code, it displays a compile-error message.

Set rs = CurrentDb.OpenRecordset("Namesx")

Logical Errors

There is one more type of error that we should discuss, since it is the most insidious type of all. A logical error can be defined as the production of an unexpected and incorrect result. As far as Access is concerned, there is no error, because Access has no way of knowing what we intend. (Thus, a logical error is not a runtime error in the traditional sense, even though it does occur at runtime.)

To illustrate, the following code purports to compute the average of some numbers:

Dim x(3) As Integer
Dim Ave As Single
x(0) = 1
x(1) = 3
x(2) = 8
x(3) = 5
Ave = (x(0) + x(1) + x(2) + x(3)) / 3
MsgBox "Average is: " & Ave

The result is the message box shown in Figure 9-5. Unfortunately, it is incorrect. The penultimate line in the preceding program should be:

Ave = (x(0) + x(1) + x(2) + x(3)) / 4

Note the 4 in the denominator, since there are 4 numbers to average. The correct average is 4.25. Of course, Access will not complain because it has no way of knowing whether we really want to divide by 3.

Figure 9-5. The result of a logical error

Precisely because Access cannot warn us about logical errors, they are the most dangerous, because we think that everything is correct.

Invariably, you will encounter errors in your code. Design-time and compile-time errors are relatively easy to deal with because Access helps us out with error messages and by indicating the offending code. Logical errors are much more difficult to detect and fix. This is where debugging plays a major role. The Access IDE provides some very powerful ways to find bugs.

Debugging can be quite involved, and we could include a whole chapter on the subject. There are even special software applications designed to assist in complex debugging tasks. However, for most purposes, a few simple techniques are sufficient. In particular, Access makes it easy to trace through a program, executing one line at a time, watching the effect of each line as it is executed.

Let us discuss some of the tools that Access provides for debugging code.

Sub ProcedureA(  )
   rs.AddNew              ' Add a new record
   Call ProcedureB
   rs.Update              ' Update recordset
End Sub

Sub ProcedureB(  )
   rs!LastName = "Smith"
   rs!FirstName = "John"
End Sub

Call ProcedureB

rs!LastName = "Smith"

rs.Update

Call ProcedureB

rs.Update