The Access Wizard Newsletter Tips Tricks and Traps for Access Users and Developers
October 2006

Murphy's Law of VBA Coding: When Writing Code, Something Will Go Wrong

Murphy's Law: Anything that can go wrong, will.

Murphy's Laws of computers: A computer is capable of making as many mistakes in two seconds as twenty men working twenty years. If a program has not crashed yet, it is waiting for a critical moment before it crashes.

Murphy's Addendum: You never run out of things that can go wrong.

Any time you write VBA code within an Access application, the likelihood of something going wrong is very high, and typically when it does, things will fall apart. Sometimes the problem may be minor and might not even be noticeable. At other times, things can go spectacularly wrong and cause Access to shut down. I've even experienced problems so severe that they crippled Access so that it wouldn't run properly.

But perhaps the worst case is when nothing appears to be wrong on the surface, while behind the scenes bad data is entered into the database as a result of an undetected problem.

I think we’d all agree that it's much better to be proactive and avoid problems rather than reacting to them when they occur. This month, we’ll discuss error handling, best described as anticipating problems and heading them off before they can take root.

In this issue
  • Tip of the Month – Copying Tables from Access to Excel
  • A Recap of our Cubit Function
  • A Generic Solution
  • Conclusion and a Look Ahead
  • Reader Feedback – Let the Debate Begin!

  • A Recap of our Cubit Function

    If you remember from last month we had a function that translated cubits to inches as follows:

    Function CubitsToInches(dblCubits As Double, strPerson As String) As Double
       Dim dblInchesPerCubit As Double
       dblInchesPerCubit = DLookup
    ("CubitLen", "tblCubit", "Person = '" & strPerson & "'") CubitsToInches = dblInchesPerCubit * dblCubits
    End Function

    If you need details about how the function works, see last month's issue Conversion Functions 102. Now this function is quite nice but there are a few flaws. The first is that it relies on a table that may not contain the cubit conversion factor for our specific person of interest.

    As a reminder here is our table:

    Under the current scenario, if you type:

    ? CubitsToInches(2,"Jim")
    into the immediate window, the function returns the value 60. You get that result because Jim exists in the tblCubit table with a value of 30 inches per cubit. It will also work for Andre and Mildred because their conversion factors are also listed in the table.

    What happens if you type in:? CubitsToInches (2,"Paul") – you get back the following very ugly message:

    What's happening here is that Access is going to the table and looking for a record where the person is equal to Paul. Since our table doesn't have Paul, a null value (or nothing!) is returned. When Access tries to multiply null times 2 it ends up with nonsense, so it stops and annoys the user with a not very helpful message.

    A Generic Solution

    There are multiple ways to deal with this, and we'll use the most common one for this month and expand with other methods as we go forward.

    We'll change our code ever so slightly and make it much more flexible.

    Our new 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
     Exit Function

     CubitsToInches = 0
    End Function

    We've added four lines of code and two labels (the labels are the lines that end with a colon).

    Let's take a look at our new additions.

    On Error GoTo PROC_ERR

    This line says if there is any problem at all, jump to the line that says "Proc_Err." I intentionally use the term Proc_Err because it has meaning; this section is a procedures error handling section.

    Our next two new lines appear to be out of order

      Exit Function

    This comes before the end of the function for a good reason. We'll leave this for the time being and come back to it because I want to explain how the error handling part of the routine works.

      CubitsToInches = 0
      GoTo PROC_EXIT

    Here's where we jump if there is a problem. The first thing to note is that PROC_ERR: has a colon at the end of it. This is a signal to Access that this line is not a line of code, but rather a label. This means that it will never be executed, but rather it is a point to jump to from other parts of the code. Because of the earlier statement On Error GoTo PROC_ERR, if there is a problem in the code the program jumps directly to this line to handle problems

    Our function name is CubitsToInches; the line CubitsToInches = 0 says that the function should return a value of zero if an error occurs. A value of zero should certainly alert our user that there is a problem.

    The final new line is GoTo PROC_EXIT. This says we're done here-jump to the line label that says PROC_EXIT. This part of the code, if it is ever reached (and in most cases it won't be), will send the code back the Proc_Exit part of the function so that no matter what happens the code will always exit from the same spot. Although in our case it's not strictly required, it's still a good habit to get into. The basic law of any procedure is that it should have just one exit point. By following this convention your life will be easier when it comes time for debugging.

    At this point we end up back at


       Exit Function

    The Proc_Exit line is just a label so there is no code executed there – the next line "Exit Function" pretty much does what it says-it exits the function. Now we will have reached this point in one of two ways: either everything has gone according to plan and we are returning a reasonable number, or there has been a problem and the code jumped to the line Proc_Err and from there will have set our CubitsToInches to 0 and then sent the code back to Proc_Exit.

    Once this new code is in place, you can test it with a variety of names and measures. If you give it the name of someone who is already in our table, you'll get a valid response; if you ask it to convert cubits to inches for someone who is not in out table, you'll get back a zero. Type the following code into the immediate window:

    ? CubitsToInches(2,"Paul")

    Based on the discussion above, the value returned will be zero because the code detected that Paul was not in the table. The function worked by returning a zero for an invalid value, thus alerting the user that there was a problem.

    Conclusion and a Look Ahead

    This month we took our first step in preparing for Murphy's Law. Now when something goes wrong our code will replace the cryptic and unhelpful messages with a value that should alert the user to a problem. This assumes, of course, that the user is paying attention, which may not be the case. Next month we'll look at ways to further strengthen our function by enhancing our error handling procedure so that it gives the user more feedback to help both identify and solve the problem.

    Reader Feedback – Let the Debate Begin!

    Dan Knight of Knight Information Services in Alberta, Canada reacted to last month's trap:Don't use Queries as the Source for Reports or Forms

    Jim; I just finished reading your latest Wizard newsletter and have to disagree with your "Trap of the Month" ... Don't use Queries as the Source for Reports or Forms

    In this Trap, you summarize that, "The problem is two fold":
    Excessive numbers of queries, you admit that's not the big problem
    Deletion of source query.

    You recommend using the SQL stmt (sic) as your data source; But this too is fraught with "problems"

    To edit the "query" (SQL stmt) you have to edit the form/report. As you stated, using the SQL is not as intuitive (I wouldn't classify this as a problem other than lack of experience) It "hides" the data source; which depending on the report/form, may be a good thing.

    I work around the two problems you stated by naming any form/report data source that is not directly a table as:
    qryfrmFormName or qryrptReportName
    This allows me to at a glance know if any query is needed for a report/form, so I can confirm if the report/form is needed before deleting the underlying query. It also allows me to sort thru my queries easily. As an added feature, if I'm creating multiple nested queries to generate the data source, I make a note of the other queries &/or the logic in the query description property.

    Just my thoughts,

    Dan Knight

    I applaud Dan's approach to solving the problem with a naming convention, something that I whole-heartedly support. At one point in my Access career I would have agreed with him, however after having been burned with mistakenly changing or deleting a query that was used in a report and then having to debug the problems that resulted, sometimes not immediately apparent, I stand by my recommendation.

    This is even more important if there are multiple users of a database. A conscientious user like Dan can manage his way if he is the only user, however if you have multiple users in the same application it becomes very difficult to control the actions of other people.

    So my recommendation firmly stands:

    Don't use Queries as the Source for Reports or Forms

    Tip of the Month – Copying Tables from Access to Excel

    In the past I've given you tips on how to Copy tables from Excel to Access. But what about going in the other direction? Let's say that you have a table in Access that you want to import into Excel.

    Here are the steps for the easiest way to import an Access table into Excel:

    1. Right click on the Access table and choose “copy” from the pop-up menu.
    2. Open a new workbook in Excel
    3. Put your cursor in the cell where you’d like the top left corner of the table to be placed
    4. Select Edit | Paste from the menu at the top (or use the keyboard shortcut Control-V)

    Your Access table will be copied into Excel and you can analyze the data to your heart’s content.

    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
    Powered by

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