Last month we created an error handling routine that returned a nonsense value from a function in the event of a problem:

Function CubitsToInches(dblCubits As Double, strPerson As String) As Double
 Dim dblInchesPerCubit As Double
 On Error GoTo PROC_ERR
 dblInchesPerCubit = DLookup ("CubitLen", "tblCubit", "Person = '" & strPerson & "'")
 CubitsToInches = dblInchesPerCubit * dblCubits
PROC_EXIT:
 Exit Function
PROC_ERR:
 CubitsToInches = 0
 GoTo PROC_EXIT
End Function

The PROC_ERR section is called when our function encounters a problem.

If we make just a couple of changes to the above routine, we can radically alter our program to stop processing, alert the user, and invite him/her to fix the problem.

The revised function looks like this:

Function CubitsToInches(dblCubits As Double, strPerson As String) As Double
 Dim dblInchesPerCubit As Double
 On Error GoTo PROC_ERR
 dblInchesPerCubit = DLookup ("CubitLen", "tblCubit", "Person = '" & strPerson & "'")
 CubitsToInches = dblInchesPerCubit * dblCubits
PROC_EXIT:
 Exit Function
PROC_ERR:
  MsgBox "There is no person in the Cubits Table called " & strPerson &”. Please check the Cubits Table.”
  End
 GoTo PROC_EXIT
End Function

Our first change is that we've removed the line:
CubitsToInches = 0
This was the line that designated the nonsense value that was returned in the event of an error.

We've replaced it with:
MsgBox "There is no person in the Cubits table called " & strPerson & ". Please check the Cubits Table."

With these changes to the code, we’re directing Access to present the user with a message box (a box that pops up on the screen to give some information to the user) in the event of a problem, as opposed to returning the nonsense value of zero. Message boxes can get reasonably complex, but here we're using a plain vanilla approach.

This is how a message box works: the text in quotes following the term "msgbox " pops up on the user's screen. We've added just a couple of elements following the closing quotes

1. The ampersand symbol "&"
2. Our variable: strPerson

The ampersand is a concatenation device (a symbol that joins text strings). Our strPerson variable is passed to the function and used to look up that person's cubit length in our Cubit table. With this revision, if the person is not in the table our application will tell the user that the person is missing, and invite them to check the table.

The next line
End
stops all processing. It does nothing fancy---it simply ends the function--- it doesn't pass control to the calling function; it simply returns to the user interface.

Now we've strengthened our error handling to not only detect the error, but also to explicitly warn the user that there's a problem and invite them to fix the mistake.

Our error handling routine is now stronger than it was before. Last month, we expected our user to realize when there was a problem by returning a nonsense value (zero). However, a nonsense calculation can still slip by if the user is not looking at results. It also can be ignored. With this new error message we’ve implemented, the user will certainly know there's a problem. But certainly problems other than a missing person issue will arise – what then? Next month (and in future issues), we’ll make our error handling routine even more robust.

There's also the problem of abruptly ending the program with End statement. It could be that you want the program to continue where it left off; in that case you'd leave in the line:

GoTo PROC_EXIT rather than replace it with End.

When designing an application, you’ll want to seriously consider how you want your program to behave, especially if you're building a database that will be used for someone other than yourself.

Always test your error handling routines before you release your application for public use.

If you make a mistake in your error handling programming, you can get stuck in an endless loop. If the error handling is invoked and it comes across another error, it will call itself and continue to do so until your computer either runs out of memory, your user figures out how to stop the program, or the world implodes on itself because it has become confused : )