The Access Wizard
$Account.OrganizationName
The Access Wizard Newsletter Tips Tricks and Traps for Access Users and Developers
November 2006

When Everything That Can Go Wrong Does Go Wrong, How Do You Know? – User Alerts

O'Toole's Commentary on Murphy's Law: "Murphy was an Optimist"

As Mr. O'Toole aptly points out, the potential for things to go wrong is almost unlimited. A proficient Access programmer knows the importance of proactively avoiding problems, which is best accomplished by thinking through the database use cases and coming up with a solid design. The back up plan involves dealing with unexpected problems when they arise. Handling the unexpected has been our focus for the past few issues.

Our approach last month depended on our user noticing when there was a problem. This month, we'll take a different approach - proactively alerting the user to a problem so that s/he can take action immediately.

In this issue
  • Tip of the Month – How to Undo Mistakes in Designing Forms or Reports: Control-Z is your Friend
  • Error Handling - a More Assertive Approach
  • Caveats
  • Trap of the Month – Avoid Endless Loops in Error Handling

  • Error Handling - a More Assertive Approach

    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.


    Caveats

    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.


    Trap of the Month – Avoid Endless Loops in Error Handling

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


    Tip of the Month – How to Undo Mistakes in Designing Forms or Reports: Control-Z is your Friend

    How many times have you been designing a form or report in Access and discovered that you just made a mistake?

    Luckily, the Undo function (which you may be aware of if you use Word or Excel) is also available in Access. As expected, the Undo function reverses the last thing you did.

    The next time you make a mistake, try typing Control- Z (or Edit |Undo from the menu). You can type it more than once to step backwards and undo multiple keystrokes.

    The nice thing about this Control-Z is that it can be used even when the menu is not showing at the top of the screen. Frequently, developers will replace or remove the menu you usually see when you're working in Access. This does not prevent the use of the Control-Z keyboard shortcut. More than once I've found it to be a real-time saver and I'm sure you will too.

    Quick Links...

    Custom Software Home

    Access Wizard Archives

    Our Services



    Join our mailing list!

    Forward email

    This email was sent to jim@custom-software.biz, by jim@custom-software.biz
     

     


    Custom Software | © Copyright Custom Software All Rights Reserved | Westford | MA | 01886