Section 17.2. Dealing with Trouble | Access 2007[c] The Missing Manual

17.2. Dealing with Trouble

It would be nice to pretend that Access always sails through your code without the slightest hiccup. But the truth is, errors do occur, and they occur often. This fact shouldn't frighten you. After all, one of the reasons you're using Visual Basic code instead of ordinary macros is so that you can detect and respond to errors gracefully.

Figure 17-3. This form shows the ValidateCard function at work on the AddCreditCard form in the Boutique Fudge database. Whenever the CardNumber field's changed, a subroutine checks if it's valid, and cancels the change if it isn't.

You'll face two types of errors with your code:

Mistakes . These errors are coding errors that you introduce accidentally . Usually, you catch these while testing your database. (If you're lucky, the Visual Basic editor spots the problem as soon as you type it in, and then warns you with a message.)
Unexpected limitations . These errors arise under specific circumstances that you may not have anticipated. Say you create two forms: Order, and Order_ Subform. Order_Subform's designed to be used as a subform in Order, and it includes code that accesses the controls in Order. However, if someone opens Order_Subform directly, the Order form isn't available, and this code fails.

As a conscientious programmer, it's your job to correct all your mistakes and deal with unforeseen limitations in the best possible way. Visual Basic gives you two tools to help out. You can use debugging to diagnose bizarre problems and fix them, and you can use error handling code to catch unexpected problems, and alert other people.

17.2.1. Debugging

Debugging's a nifty feature that lets you walk through your code, watch what it does, and spot errors. Code debugging's similar to macro debugging (Section 15.1.3) in that it lets you run your logic one statement at a time. However, code debugging's much more powerful, because it lets you make your way through complex routines, loops , and conditional statements. It even lets you see what's currently stored in your variables .

Tip: Debugging's real benefit is that it helps you test your assumptions . Every programmer has assumptions about how a piece of code works. However, if code did exactly what you expected, you wouldn't ever have an error. With debugging, you can find the exact point where code does something that you don't expectwhen a calculation provides a strange result, a conditional statement sends you the wrong way, a loop's repeated one time too many, and so on. Then you can correct the mistake.

The easiest way to perform debugging's to set a breakpoint a special marker that tells Access where you want to start debugging. When Access reaches a line of code that has a breakpoint, it pauses your code. Access then lets you step through the code at your own pace, one line at a time.

Here's how to use a breakpoint:

Find the first line in your code that you want to debug .

If you want to debug an entire routine, start with the opening Sub or Function statement. If you want to look at a specific section of your code, go there.

Click the margin on the left to place a breakpoint on this line (Figure 17-4) .

Each breakpoint's a signal that tells Access you want to start debugging here .

Some lines can't accommodate a breakpoint. These lines don't contain executable code, line blank spaces, comments, and variable declarations. Everything else is fair game.

Figure 17-4. Every breakpoint looks like a red circle. You can remove a breakpoint by clicking it. In this example, the breakpoint (circled) is placed at the beginning of the ValidateCard function.

Note: When you close your database and open it later, all your breakpoints disappear.

Trigger your code .

You can get your code to run in the normal way. If you're debugging an event handler for a button click, open the appropriate form, and then click the button.

When Access reaches your breakpoint, it pauses and switches into break mode . Everything in your application's put on hold.

Once you're in break mode, you have several options:

You can single-step through your code . That means you run one statement at a time, pausing after each statement. To try this out, press the F8 key. This action runs the current statement (which is highlighted with the yellow arrow), moves to the next executable statement, and then pauses again (Figure 17-5). You can continue for as long as you want, pressing F8 to run each line of code.

Tip: Single-step debugging lets you follow how your code works. If you try it with the ValidateCard function shown earlier, you'll see how Access moves through the loop several times, and how it branches into different conditional sections depending on whether it's processing a number in an odd or even position.

Figure 17-5. In this example, the breakpoint stopped the code at the beginning of the ValidationCard function. Then, the person debugging this code hit F8 a few times to move on through the code. Right now, the code's paused at the beginning of the For/Next loop (circled).

You can stop running your code . Press the Stop button (it looks like a square) in the Visual Basic toolbar to shut your code down.

You can make changes . If you find what's wrong, you can edit your code, and then keep running with the new changes. Of course, there are certain types of edits that force Access to stop debugging. If you make one of these changes, then you see a Message box that warns you that "This action will reset your project." If you click OK, then Access stops your code just as if you had clicked the Visual Basic toolbar's Stop button.

You can see what's stored inside a variable . To do so, just hover over the variable name somewhere in your code (Figure 17-6).

Figure 17-6. By hovering over the CurrentNumber variable, you can see that it's currently storing the number 4. You can hover over variables on any line in your code, not just the current line. However, you'll see only the current contents of the variable. If you use F8 to single-step through your code, then you can watch a value change as you perform operations.

You can resume normal execution . If you've found the source of your problem and you don't want to keep debugging, just hit F5 (or click the Visual Basic toolbar's Play button). Access runs the current line, and then continues on its merry way (at least until it meets another breakpoint).

Tip: You can pull off a wacky trick with the yellow arrow. You can use it to run code in a completely different place. Just drag the yellow arrow to the line you want to run next, and then hit F5 to switch out of debug mode, and resume running your code normally.

The Visual Basic editor has many more debugging tools. However, breakpoints are really all you need to start exploring what's taking place under the hood when you run your code.

17.2.2. Error Handling

Some errors occur through no fault of your own. Perhaps you're trying to perform a task with information someone else gave you, and that information just isn't valid. Imagine what happens if someone calls ValidateCard and passes in a credit card number that contains letters and punctuation!

Although this sort of error can occur as a result of somebody else's carelessness, it's up to you to deal with it in the best way possible. You need to explain the problem with a helpful Message box, and end the current task (or jump ahead to the next step). You can take care of this job with error handling code.

Tip: The best way to perfect a piece of code's to use debugging to find and fix all its problems. Once you've finished this process, you can add error handling code to deal with the unexpected problems. If you add error handling code earlier, then you may find it a bit harder to debug your application.

Ordinarily, when Access encounters an error, it jumps to the offending code, enters break mode, and shows you an error message. This behavior's helpful if you're planning to debug the problem, but it's a bit traumatic for the ordinary people who may be using your database. Not only have they never seen code before, they're in danger of changing it and introducing a new problem.

Instead, you need a way to deal with the error the way you want, using code. Visual Basic has a special statement that tells Access how to deal with errors. It's the On Error statement.

The On Error gives you several options. You can tell Access to skip over any errors and try to run the next line of code like this:

 On Error Resume Next

This option's almost always a bad idea. If one error's occurred, more are likely to follow. At worst, this could cause your program to do something you don't intend it to do.

You can also tell Access to jump to a specific place in your code. Here's an example:

 On Error Goto ErrorHandlingCode

This example tells Access to jump to the section named ErrorHandlingCode as soon as it encounters any problem. You need to identify this section by adding the section name, followed by a colon (:) on a separate line, like this:

 ErrorHandlingCode:     ' If an error occurs, Access starts running your code here.

You can most easily understand how this error handling system works when you consider how you can use it in the ValidateCard function:

 Function ValidateCard(CardNumber As String)     On Error Goto ErrorHandlingCode     ' (The code for Luhn's algorithm goes here.)     Exit Function ErrorHandlingCode:     MsgBox "Oops. Did your credit card number have letters?"     ValidateCard = False End Function

Here are several important details. First, the On Error statement's placed at the very beginning of the code routine, so you can catch mistakes in any of the code that follows . Second, notice that after the number-checking code finishes, an Exit Function statement ends the routine. That statement prevents Access from drifting into the error handling code that follows if an error hasn't happened . Finally, the error handling code shows a Message box that explains that something went wrong, and returns a result that clearly indicates the problem. People most often handle errors this way. Just remember to always use an Exit Sub or Exit Function statement to make sure you don't run your error handling code by accident .

Note: As written, the person using the AddCreditCard form may get two error messagesone explaining the letters-or-punctuation problem, and the second stating the obvious fact that validation failed. If this message seems like unnecessary punishment , then you can move the error-handling code out of the ValidateCard function and into the On Update event handler code, which is where it really belongs. That way, the On Update event handler can choose exactly how to deal with the problem. To see the slightly rear-ranged code, check out the downloadable samples for this chapter.

You have only one other option for handling errors. You can tell Access to stop immediately and enter debug mode using this statement:

 On Error Goto 0

Of course, this behavior's already the standard error handling behavior. You need to use this statement only if you're switching back and forth between different error handling approaches in the same routine.