Types of Errors | Beginning Access 2002 VBA (Programmer to Programmer)

However well you have designed and tested your application, there are bound to be errors. It's a fact of life. The important thing is to learn from them, fix them quickly, and never make the same error again.

At this stage it's important to distinguish between errors and bugs , although the two terms are often used interchangeably. A bug is generally something unforeseen, unexpected, a coding mistake. An error can be the same, but it can also be something expected, something that we have realized might happen and we've catered for in our code.

We are going to look at three types of errors:

Syntax Errors These errors that are the result of you incorrectly structuring your code. For example you may have forgotten to put in a closing bracket on an expression.
Compile Errors These occur when the syntax is correct, but Access cannot compile the code for some other reason.
Run-Time Errors These are only found once the program is running. In these cases the language is syntactically correct, and can be compiled, but something happens to cause the program to halt.

Syntax Errors

This is the simplest form of error, usually caused by typing mistakes. If you've graduated beyond the two-finger touch typing school, then you might find fewer typing errors, but the easiest way to handle these is to let VBA do it for you. Ensuring that variable declarations and syntax checking are on will save you the trouble of hunting these errors out.

You might also find times when you get a little confused between the various statements, but VBA will spot this too:

In this example, the Each has been missed out from the For ... Each statement, so VBA assumes it's a standard For ... Next loop, and is expecting the equals sign. If you give this a try, you'll find that the error message pops up when you press Return or Enter at the end of the line, and the line with the error turns red. You can click the OK button to cancel the error, and either fix the problem now or come back to it later.

Compile Errors

Another type of problem often encountered cannot be found immediately. For example:

Notice that the CurrentDatabase function is highlighted, because VBA cannot find this function. This could either be because you meant to use the CurrentDb function, or because CurrentDatabase is a function of your own, but isn't visible. Perhaps it's not written yet, or maybe it's in another module and has been declared as Private instead of Public , or maybe you haven't referenced the library or application.

This type of error is called a compile error because the syntax of the above example is correct, but it cannot be compiled. Once the program is compiled, you know that the syntax is correct, and all functions that you've used have been found. However, there could be plenty of other errors lurking around.

Another error that is often confusing is the 'Expected variable or procedure, not module' compilation error. For example, consider the screenshot above. Let's assume we've fixed the compilation error shown and have saved the module. If we saved this module as EnumerateContainers and then tried to run the procedure, we'd get the following error:

This is telling us that the name of the procedure we are trying to run is also the name of a module.

Run-Time Errors

As the name suggests, run-time errors are only found once the program is running. In these cases the language is syntactically correct, and can be compiled, but something happens to cause the program to halt. The Auto Quick Info feature has eased a few of the run-time error problems, because it meant that you could see the order of parameters as you were typing. This has meant (hopefully) the end of passing in parameters in the wrong order.

Another run-time error, which probably is the most common, is the Type Mismatch error, which can be very confusing to beginners . If you remember way back to Chapter 4, we talked about variable types, and a type mismatch is where you assign a value of one type to a variable of another, and there is no implicit conversion rule, (such as there is between integers and doubles) You'll see this message if you do:

Clicking the Debug button will highlight the offending line:

Here an integer variable is being assigned a string. Notice that although the variable is an Integer , its naming convention is wrong it should be intI . This has probably contributed to the error in the first place, and shows the importance of a naming standard. It's possible that someone changed the variable type without changing its name.

Semantic (or logic) Errors

Semantic errors are the hardest to find, because they represent errors in the logic of the program. This means that the code you've written is correct VBA code, but it's not doing what it's supposed to do. Sometimes these produce errors and other times there are no errors, but certain procedures in your application don't work as expected. If errors do occur then you may get an error dialog, and the opportunity to debug the program (note, however, that this is just the line where the error manifested itself, and may not actually be where the real problem lies) or worse still you may find that your code just produces incorrect results with no error at all.

Imagine a function using a variable that has been passed in as an argument. If the variable has the wrong value, then it's not the procedure that is using it that is wrong, but the calling routine. This sort of situation can leave you several layers deep, with functions calling other functions, so you often need to work backwards to find the root of it all. We'll show you how to track backwards later in the chapter.

Finding semantic errors is also a test on you. As a programmer, probably the person who wrote the code, you tend to test what you think the code should be doing, rather than what it actually is doing. This is not as odd as it seems, because we do tend to assume certain things. Don't get into the habit of skipping sections of code because you think you know what they do. When testing and debugging you should throw away your perceptions of the problem and start from scratch. Never think, "That can't be happening". It can, and does not always, but if you take this approach, you'll be better for it.

Let's take a look at an example of some errors, and start to see how to track them down. We're going to create an example just to show you how lucky you really have to be to win the UK lottery. This uses 49 numbers from which you have to pick 6, in any order. The calculation for this is:

Let's give this a go.

Try It OutRun-time and Semantic Errors

Create a new form, and set the Scroll Bars property to Neither , and the Record Selectors , Navigation Buttons , and Dividing Lines properties to No .
Put three textboxes and a command button on it, so it looks like this:
Name the textboxes txtPool , txtRequired , and txtCombinations . Call the command button cmdCalculate , and then save the form as frmCombinations .
Press the Code button on the toolbar to switch to the code editor and create a code module for this form.

Remember that pressing Alt+F11 will also switch to the VBA IDE, but in this case the code module will not be created automatically.

Enter the following function:

   Private Function Factorial (intNumber As Integer) As Double     If intNumber < 0 Then     Factorial = 0     ElseIf intNumber = 0 Then     Factorial = 1     Else     Factorial = intNumber * Factorial(intNumber  1)     End If     End Function

Now add the next function:

   Private Function Combinations (intPool As Integer, _     intRequired As Integer) As Double     Combinations = Factorial(intPool) / _     Factorial(intRequired) / _     Factorial(intPool  intRequired)     End Function

Now in the Click event for the command button, add the following code:
```
   txtCombinations = Combinations(txtPool, txtRequired)   
```
Back in Access, switch the form back to form view and try the following numbers. Press Calculate to see what chance we in the UK have of winning our lottery.

Yes, that's right. 14 million to 1! Not very favorable odds, but it shows the functions work OK. You might like to try the numbers from your local lottery.
Now change the number of items in the pool from 49 to and try again.

You're thrown back into the VBE with a division by zero error.
Press the Debug button to get taken to the line where the error was generated.

We know what the problem is, a division by 0, but where is it being generated and why? If you look at the above function and think about the arguments: intPool is , and intRequired is 6 . This means that the final division here is the problem, as we're trying to get the factorial of a negative number, and our factorial program returns 0 if you try this.

Now for those of you with a math background, you might be starting to think about some problems we've got here. What do we correct? Should the Factorial function be corrected, because the factorial of a negative number isn't , it's infinity. So we've already introduced some error protection, but is it correct? Strictly speaking, no it's not, but what we've done is protect this function from raising errors that the user probably doesn't want to know about. The Factorial function could raise an error indicating a division by , but then we'd have to trap this error and act upon it (and we'll do this later in the chapter). For the moment a return value of -1 is fine. The division by 0 isn't acceptable though.
Press the Reset button on the toolbar (or select Reset from the Run menu) to stop the program running.

Change the code in the Combinations function to the following:

   If intRequired = 0 Or (intPool  intRequired) < 0 Then     Combinations = -1     Else     Combinations = Factorial(intPool) / _     Factorial(intRequired) / _     Factorial(intPool  intRequired)     End If

Back in Access, try the same numbers again:

This is much better (although -1 isn't the correct answer) because for combinations to work, the number of items in the pool can't be it must be larger than the number of items required. All we've done is move the problem somewhere else. We could change the code for the command button so that it checks the number of combinations, and if it is -1 it displays a more informative error message, but for the moment we'll leave it as it is. We'll be coming back to this problem a little later.

Locating Errors

One thing to remember when tracking down errors is that Access is event driven. That means code runs in response to events, so you might find that code corresponding to certain events is being run without you being aware of it. Consider opening a form. You've already seen that there's a Load event, generated when the form is loaded, but there are in fact four other events generated as well, in this order:

Open
Load
Resize
Activate
Current

Closing a form generates:

Unload
Deactivate
Close

So if opening the form appears to be a problem, don't forget to check the other events. You can see which events have code behind them because the events appear in bold in the event combo box in the code window:

Another good way not to miss code is to make sure you have the code window set to Full Module View this can be set from the buttons at the bottom left of the code window:

This shows all of the procedures for a single module together in the code window, with a line to separate them, and makes seeing all of the code much easier.

Other Errors

There are occasions when other types of errors crop up, but with a bit of advance planning you can probably circumvent them. There are times, however, when you've no choice but to delve into the code line by line. Of course, the number of errors that occur is always in proportion to the importance of the application, and in inverse proportion to the time you have to fix them!

The cause of these other errors is almost limitless, but can include Dynamic Link Libraries (DLLs) and ActiveX controls. If the programmers who wrote these did a good job in testing them, you shouldn't see any problems, but as we've already said writing bug-free software is very hard. Other problems, such as lack of memory, network problems, or registry problems, can cause some very odd things to happen.

Sometimes there is nothing you can do in these situations, but if you're stuck try the following steps:

Check your code thoroughly, line by line, without any assumptions. Write down the state of variables as you go through to see what's changing.
Try running the application on another machine. If it works, you could have a memory problem, or maybe an old version of a DLL or ActiveX control.
Get someone else to check it for you. Even sitting down with someone else, with the code, trying to explain what it is doing can lead that light bulb above your head to flicker on, as you realize what the problem is.
Buy a hat, fake glasses , and beard, and get those tickets to a technology-free sunny island!

The last of these shouldn't really be an option, however tempting it sounds. (Well, not the hat, glasses and beard part but the sunny island sounds good!). If you are really stuck, then the Internet is wide and responsive , and the chances are that someone has either had a similar problem, or will be willing to help. One great place to look is the Microsoft Developer Network, which can be found at http://msdn.microsoft.com/ . The newsgroups are also a great place, full of helpful people. The Access ones are named microsoft.public.access.* , and most ISPs support them.