Microsoft Access Database Development With VBA

Error Handling

 

Introduction to Errors

 

Overview

Probably no matter how careful and meticulous you are, some time to time, there will be problems with your code or your application. Some problems will come from you. Some problems will be caused by users. And some problems will be caused by neither you nor your users. This means that there are things you can fix. Those you can avoid as much as possible. And there are situations beyond your control. Still, as much as you can, try anticipating any type of problem you imagine may occur when a user is using your application, and take action as much as possible to avoid bad situations.

Practical LearningPractical Learning: Introducing Error Handling

  1. Start Microsoft Access
  2. Open the Exercise3 database from Lesson 9
  3. In the Navigation Pane, double-click the Compound Interest form to open it
  4. Click the Calculate button:
     
    Compound Interest
  5. Click End
  6. Right-click the title bar of the form and click Design View

Error Categories

There are three main types of problems that you will deal with, directly or indirectly:

  1. Syntax: A syntax error comes from your mistyping a word or forming a bad expression in your code. It could be that you misspelled a keyword such as ByVel instead of ByVal. It could also be a bad expression such as 524+ + 62.55. It could be a "grammar" error such as providing the name of a variable before its data type when declaring a variable (quite common for those who regularly transition from different languages (C/C++, Pascal, C#, Java))

    When you use Microsoft Visual Basic to write your code, it would point out the errors while you are writing your code, giving up time to fix them. When your database runs, it can let you know about other syntax errors. For this reason, syntax errors are almost the easiest to fix because, most of the time, the problem would be pointed out and you can fix it
  2. Run-Time: After all syntax errors have been fixed, the program may be ready for the user. There are different types of problems that a user may face when interacting with your program. Imagine that, in your code, you indicate that a picture would be loaded and displayed to the user but you forget to ship the picture or the directory of the picture indicated in your code becomes different when a user opens your application. In this case, when you tested your database in your machine, everything was fine. This is a type of run-time error.
    Run-time errors are mostly easy to fix because you will know what problem is occurring and why
  3. Logic: These are errors that don't fit in any of the above categories. They could be caused by the user misusing your application, a problem with the computer on which the application is running while the same application is working fine in another computer. Because logic errors can be vague, they can also be difficult to fix

One of the best qualities of an effective programmer is to anticipate as many problems as possible and to deal with them in the early stages. Some problems can be easy to fix. With some others, you will simply need to build more experience to know how to fix them. Unfortunately, it will not be unusual to have users asking you to fix your application when a problem may not come from it.

Handling Errors

 

Introduction

Most or early errors occur in your code. The Microsoft Visual Basic Code Editor can help you detect syntax errors and fix them. For example, when a certain error occurs while you are writing your code, a message box would display, prompting you to fix the problem:

Error Detection

Error Detection

If there is a syntax error that that the IDE didn't signal or that you ignored when writing your code, you would find it out when the form or report is previewed.

A run-time error is one that occurs when using your application. Consider the following form:

Calculation

Private Sub cmdCalculate_Click()
    Dim Number#
    Dim Twice#
    
    Number = [txtNumber]
    Twice = Number * 2
    [txtResult] = Twice
End Sub

Here is an example of executing it:

Calculation

The first aspect your should take into consider is to imagine what could cause a problem. If you think there is such a possibility, you can create a label that could be used to transfer code if a problem occurs. Here is an example:

Private Sub cmdCalculate_Click()
    Dim Number#
    Dim Twice#
    
    Number = [txtNumber]
    Twice = Number * 2
    [txtResult] = Twice
    
ThereWasAProblem:
    MsgBox "There was a problem when executing your instructions."
End Sub

If you create such a label, you should specify when to jump to that label. Otherwise, as in this case, the label section would always execute. Here is an example of running the above version:

Calculation

In this case, we want the label section to execute only when we want it to. To prevent the execution from reaching this section if not directed so, you can add an Exit Sub line above the label section:

Private Sub cmdCalculate_Click()
    Dim Number#
    Dim Twice#
    
    Number = [txtNumber]
    Twice = Number * 2
    [txtResult] = Twice
    
    Exit Sub
    
ThereWasAProblem:
        MsgBox "There was a problem when executing your instructions"
End Sub

This time if you execute the program with an appropriate value, the label section would not be reached.

Practical LearningPractical Learning: Introducing Errors

  • Change the code of the Calculate button as follows:
    Private Sub cmdCalculate_Click()
        Dim Principal As Currency
        Dim InterestRate As Double
        Dim InterestEarned As Currency
        Dim FutureValue As Currency
        Dim RatePerPeriod As Double
        Dim Periods As Integer
        Dim CompoundType As Integer
        Dim i As Double
        Dim n As Integer
        
        Principal = CCur(txtPrincipal)
        InterestRate = CDbl(txtInterestRate)
        
        CompoundType = Choose([fraFrequency], 12, 4, 2, 1)
        
        Periods = CInt(txtPeriods)
        i = InterestRate / CompoundType
        n = CompoundType * Periods
        RatePerPeriod = InterestRate / Periods
        FutureValue = Principal * ((1 + i) ^ n)
        InterestEarned = FutureValue - Principal
        
        txtInterestEarned = CStr(InterestEarned)
        txtAmountEarned = CStr(FutureValue)
        
        Exit Sub
        
    cmdCalculate_ClickError:
    
    End Sub

In Case Of Error, Jump To Label

The above code will work fine if the right value is provided. When you preview the form, imagine that the user types an inappropriate value such as 24$.58 instead of 244.58. In this case, the value is not a number. You would like the program to let the user know that there was a problem:

Error

With some experience, you would know what the problem was, otherwise, you would face a vague explanation. If a problem occurs when a person is using your database, the computer may display an insignificant message to the user who would not know what to do with it. Therefore, you can start by creating an appropriate label as introduced above. An error normally occurs in a procedure. Therefore, to make your code easier to read, you should create a label that shows that it is made for an error instead of being a regular label. The label should also reflect the name of the procedure. Here is an example:

Private Sub cmdCalculate_Click()
    Dim Number#
    Dim Twice#
    
    Number = [txtNumber]
    Twice = Number * 2
    [txtResult] = Twice
    
    Exit Sub
    
cmdCalculate_Click_Error:
    MsgBox "There was a problem when executing your instructions"
End Sub

When you think there will be a problem in your code, somewhere in the lines under the name of the procedure but before the line that could cause the problem, type On Error GoTo followed by the name of the label that would deal with the error. Here is an example:

Private Sub cmdCalculate_Click()
    On Error GoTo cmdCalculate_Click_Error
    Dim Number#
    Dim Twice#
    
    Number = [txtNumber]
    Twice = Number * 2
    [txtResult] = Twice
    
    Exit Sub
    
cmdCalculate_Click_Error:
    MsgBox "There was a problem when executing your instructions"
End Sub

This informs the compiler that, if there is a problem when this code executes, jump to the indicated label. When the On Error GoTo statement is used, this indicates that if any type of error occurs while the code of this procedure is executed, transfer the compilation to the label. In this case, as soon as something bad happens, the compiler marks the area where the problem occurred, skips the normal code and jumps to the label indicated by the On Error GoTo line. After the section of that label is executed, the compiler returns where the error occurred. If there is nothing to solve the problem, the compiler continues down but without executing the lines of code involved. In this case, it would encounter the Exit Sub line and get out of the procedure.

Practical LearningPractical Learning: Jumping to a Label

  • Change the code of the Calculate button as follows:
    Private Sub cmdCalculate_Click()
    On Error GoTo cmdCalculate_ClickError
        
        Dim Principal As Currency
        Dim InterestRate As Double
        Dim InterestEarned As Currency
        Dim FutureValue As Currency
        Dim RatePerPeriod As Double
        Dim Periods As Integer
        Dim CompoundType As Integer
        Dim i As Double
        Dim n As Integer
        
        Principal = CCur(txtPrincipal)
        InterestRate = CDbl(txtInterestRate)
        
        CompoundType = Choose([fraFrequency], 12, 4, 2, 1)
        
        Periods = CInt(txtPeriods)
        i = InterestRate / CompoundType
        n = CompoundType * Periods
        RatePerPeriod = InterestRate / Periods
        FutureValue = Principal * ((1 + i) ^ n)
        InterestEarned = FutureValue - Principal
        
        txtInterestEarned = CStr(InterestEarned)
        txtAmountEarned = CStr(FutureValue)
        
        Exit Sub
        
    cmdCalculate_ClickError:
        MsgBox "There was a problem when trying to perform the calculation.", _
               vbOKCancel Or vbInformation, _
               "Compound Interest"
    End Sub

In Case Of Error, Jump To Line #

Although the label is more explicit, it only indicates to the compiler what line to jump to in case of a problem. The alternative is to specify a line number instead of a label.

Resume

If a problem occurs in your code and you provide a label to display a friendly message as done above, the compiler would display the message and exit from the procedure. If this happens, as mentioned above, when the compiler returns where the problem occurred, you can provide an alternative. For example, in our program, if the user provides an inappropriate value that causes the error, you can provide an alternate value and ask the compiler to continue as if nothing happened. In this case, you want to compiler to "resume" its activity.

To indicate that the program should continue, you can use the Resume keyword. Here is an example:

Private Sub cmdCalculate_Click()
    On Error GoTo cmdCalculate_Click_Error
    Dim Number#
    Dim Twice#
    
    Number = [txtNumber]
    
    Resume
    
    Twice = Number * 2
    [txtResult] = Twice
    
    Exit Sub
    
cmdCalculate_Click_Error:
    MsgBox "There was a problem when executing your instructions"
End Sub

When an error occurs, if you want the program to continue with with an alternate value than the one that caused the problem, in the label section, type Resume Next. Here is an example:

Private Sub cmdCalculate_Click()
    On Error GoTo cmdCalculate_Click_Error
    Dim Number#
    Dim Twice#
    
    Number = [txtNumber]
    Twice = Number * 2
    [txtResult] = Twice
    
    Exit Sub
    
cmdCalculate_Click_Error:
    MsgBox "There was a problem when executing your instructions"
    Resume Next
End Sub

In this case, since any numeric variable is initialized with 0, when the compiler returns to the line of code that caused the problem, it would use 0 as a substitute to the inappropriate value. Based on this, you can provide a new value to use in case of error. Here is an example:

Private Sub cmdCalculate_Click()
    On Error GoTo cmdCalculate_Click_Error
    Dim Number#
    Dim Twice#
    
    Number = [txtNumber]
    Twice = Number * 2
    [txtResult] = Twice
    
    Exit Sub
    
cmdCalculate_Click_Error:
    MsgBox "There was a problem when executing your instructions"
    Number = 16
    Resume Next
End Sub

Here is one example of running the program:

Calculation

Calculation

Calculation

 

Practical LearningPractical Learning: Jumping to a Label

  • Change the code of the Calculate button as follows:
    Private Sub cmdCalculate_Click()
    On Error GoTo cmdCalculate_ClickError
        
        Dim Principal As Currency
        Dim InterestRate As Double
        Dim InterestEarned As Currency
        Dim FutureValue As Currency
        Dim RatePerPeriod As Double
        Dim Periods As Integer
        Dim CompoundType As Integer
        Dim i As Double
        Dim n As Integer
        
        Principal = CCur(txtPrincipal)
        InterestRate = CDbl(txtInterestRate)
        
        CompoundType = Choose([fraFrequency], 12, 4, 2, 1)
        
        Periods = CInt(txtPeriods)
        i = InterestRate / CompoundType
        n = CompoundType * Periods
        RatePerPeriod = InterestRate / Periods
        FutureValue = Principal * ((1 + i) ^ n)
        InterestEarned = FutureValue - Principal
        
        txtInterestEarned = CStr(InterestEarned)
        txtAmountEarned = CStr(FutureValue)
    
    cmdCalculate_Click_Exit:
        Exit Sub
        
    cmdCalculate_ClickError:
        MsgBox "There was a problem when trying to perform the calculation.", _
               vbOKCancel Or vbInformation, _
               "Compound Interest"
        
        Resume cmdCalculate_Click_Exit
    End Sub
 
 
 

The Err Object

 

Introduction

To support error handling, Visual Basic provides a global variable named Err. This allows you to identify the error and its description. Because an error depends on what caused it and why, the values of the Err variable also depend and are not always the same.

The Error Number

When developing Visual Basic for Applications (VBA), those who worked on the language also tried to anticipate as many problems as possible. This allowed them to assist you with the types of errors that your code or your applications may encounter. To make it easier, they assigned a specific number to each type of error. Consequently, when a run-time error occurs in your database, a dialog box would come up and display a number that corresponds to the error. Here is an example of a run-time error number 2489:

Error

The Err object is equipped with a Number property that allows you to identify an error by its number. The Number property is a constant integer. Most of the times, when a run-time error occurs, the above dialog box would show you the error number that occurred. Consider the following example:

Private Sub cmdCalculate_Click()
    Dim Number#
    Dim Twice#
    
    Number = [txtNumber]
    Twice = Number * 2
    [txtResult] = Twice
End Sub

Imagine that the user enters an inappropriate value in the requesting text box:

Calculation

After clicking the button, an error would be produced as follows:

Error 13

If the user clicks Debug, the line that caused the error would be highlighted:

Code Editor

Notice that, in the previous screenshot, we get the error number.

Practical LearningPractical Learning: Using the Error Number

  1. Change the code of the Calculate button as follows:
    Private Sub cmdCalculate_Click()
    On Error GoTo cmdCalculate_ClickError
        
        Dim Principal As Currency
        Dim InterestRate As Double
        Dim InterestEarned As Currency
        Dim FutureValue As Currency
        Dim RatePerPeriod As Double
        Dim Periods As Integer
        Dim CompoundType As Integer
        Dim i As Double
        Dim n As Integer
        
        Principal = CCur(txtPrincipal)
        InterestRate = CDbl(txtInterestRate)
        
        CompoundType = Choose([fraFrequency], 12, 4, 2, 1)
        
        Periods = CInt(txtPeriods)
        i = InterestRate / CompoundType
        n = CompoundType * Periods
        RatePerPeriod = InterestRate / Periods
        FutureValue = Principal * ((1 + i) ^ n)
        InterestEarned = FutureValue - Principal
        
        txtInterestEarned = CStr(InterestEarned)
        txtAmountEarned = CStr(FutureValue)
    
    cmdCalculate_Click_Exit:
        Exit Sub
        
    cmdCalculate_ClickError:
        If Err.Number = 11 Then
            MsgBox "Make sure you provide a value for the period and that value must be different from 0.", _
               vbOKCancel Or vbInformation, _
               "Compound Interest"
        Else
            MsgBox "There was a problem when trying to perform the calculation.", _
               vbOKCancel Or vbInformation, _
               "Compound Interest"
        End If
        
        Resume cmdCalculate_Click_Exit
    End Sub
    
    
  2. Return to Microsoft Access
  3. Click the Calculate button:
     
    Compound Interest
  4. Click OK
  5. Close the form

The Error Message

Obviously an error number does not mean much. To indicate what each error number refers to, the Err object is equipped with a Description property, which is a string. To display this message, you can create an On Error GoTo expression and indicate where to jump if an error occurs. Here is an example:

Private Sub cmdCalculate_Click()
    On Error GoTo cmbCalculate_Error
    Dim Number#
    Dim Twice#
    
    Number = [txtNumber]
    Twice = Number * 2
    [txtResult] = Twice
    
    Exit Sub
    
cmbCalculate_Error:
    MsgBox "Error Message: " & Err.Description
End Sub

This time, if the type of error you are anticipating occurs, you can rightfully display the description;

Calculation

Once again, notice that the type of message of the Err.Description string may not mean much to a regular user. For this reason, you should make it a habit to anticipate as many types of errors that may occur in your application and display more meaningful messages. You can do this in the section where the code would jump when an error occurs.

Practical LearningPractical Learning: Using the Error Message

  1. In the Navigation Pane, double-click the Payroll form to open it
  2. Click Process It
     
    Georgetown Cleaning Services - Employee Payroll Design
     
  3. Click Debug
  4. Click the Break button Break
  5. Change the code of the Process It button as follows:
    Private Sub cmdProcessIt_Click()
    On Error GoTo cmdProcessIt_ClickError
        
        Dim monday1 As Double
        Dim tuesday1 As Double
        Dim wednesday1 As Double
        Dim thursday1 As Double
        Dim friday1 As Double
        Dim saturday1 As Double
        Dim sunday1 As Double
        Dim monday2 As Double
        Dim tuesday2 As Double
        Dim wednesday2 As Double
        Dim thursday2 As Double
        Dim friday2 As Double
        Dim saturday2 As Double
        Dim sunday2 As Double
        Dim totalHoursWeek1 As Double
        Dim totalHoursWeek2 As Double
    
        Dim regHours1 As Double
        Dim regHours2 As Double
        Dim ovtHours1 As Double
        Dim ovtHours2 As Double
        Dim regAmount1 As Currency
        Dim regAmount2 As Currency
        Dim ovtAmount1 As Currency
        Dim ovtAmount2 As Currency
        
        Dim regularHours As Double
        Dim overtimeHours As Double
        Dim regularAmount As Currency
        Dim overtimeAmount As Currency
        Dim totalEarnings As Currency
    
        Dim hourlySalary As Currency
    
        ' Retrieve the hourly salary
        hourlySalary = CDbl(Me.txtHourlySalary)
        ' Retrieve the time for each day
        ' First Week
        monday1 = CDbl(Me.txtMonday1)
        tuesday1 = CDbl(Me.txtTuesday1)
        wednesday1 = CDbl(Me.txtWednesday1)
        thursday1 = CDbl(Me.txtThursday1)
        friday1 = CDbl(Me.txtFriday1)
        saturday1 = CDbl(Me.txtSaturday1)
        sunday1 = CDbl(Me.txtSunday1)
        
        ' Second Week
        monday2 = CDbl(Me.txtMonday2)
        tuesday2 = CDbl(Me.txtTuesday2)
        wednesday2 = CDbl(Me.txtWednesday2)
        thursday2 = CDbl(Me.txtThursday2)
        friday2 = CDbl(Me.txtFriday2)
        saturday2 = CDbl(Me.txtSaturday2)
        sunday2 = CDbl(Me.txtSunday2)
        
        ' Calculate the total number of hours for each week
        totalHoursWeek1 = monday1 + tuesday1 + wednesday1 + thursday1 + _
                          friday1 + saturday1 + sunday1
        totalHoursWeek2 = monday2 + tuesday2 + wednesday2 + thursday2 + _
                          friday2 + saturday2 + sunday2
    
        ' The overtime is paid time and half
        Dim ovtSalary As Double
        ovtSalary = hourlySalary * 1.5
    
        ' If the employee worked under 40 hours, there is no overtime
        If totalHoursWeek1 < 40 Then
            regHours1 = totalHoursWeek1
            regAmount1 = hourlySalary * regHours1
            ovtHours1 = 0
            ovtAmount1 = 0
        ' If the employee worked over 40 hours, calculate the overtime
        ElseIf totalHoursWeek1 >= 40 Then
            regHours1 = 40
            regAmount1 = hourlySalary * 40
            ovtHours1 = totalHoursWeek1 - 40
            ovtAmount1 = ovtHours1 * ovtSalary
        End If
        
        If totalHoursWeek2 < 40 Then
            regHours2 = totalHoursWeek2
            regAmount2 = hourlySalary * regHours2
            ovtHours2 = 0
            ovtAmount2 = 0
        ElseIf totalHoursWeek2 >= 40 Then
            regHours2 = 40
            regAmount2 = hourlySalary * 40
            ovtHours2 = totalHoursWeek2 - 40
            ovtAmount2 = ovtHours2 * ovtSalary
        End If
        
        regularHours = regHours1 + regHours2
        overtimeHours = ovtHours1 + ovtHours2
        regularAmount = regAmount1 + regAmount2
        overtimeAmount = ovtAmount1 + ovtAmount2
        totalEarnings = regularAmount + overtimeAmount
    
        Me.txtRegularHours = regularHours
        Me.txtOvertimeHours = overtimeHours
        Me.txtRegularAmount = CCur(regularAmount)
        Me.txtOvertimeAmount = CCur(overtimeAmount)
    
        Me.txtNetPay = CCur(totalEarnings)
    
    cmdProcessIt_ClickExit:
        Exit Sub
        
    cmdProcessIt_ClickError:
        MsgBox "Error #:" & Err.Number & vbCrLf & _
               "Error Description: " & Err.Description, _
               vbOKCancel Or vbInformation, _
               "Compound Interest"
        
        Resume cmdProcessIt_ClickExit
    End Sub
  6. Return to Microsoft Access
  7. Click the Process It button:
     
    Payroll
  8. Click OK
  9. Right-click the title bar of the form and click Design View

The Source of the Error 

It is assumed that an error would be caused when using your application. In fact, the database on which the user is working when the error occurred is considered as the source of the error. This information can be valuable at times. The application that caused an error is recognized as the Source property of the Err object. Most of the time, you will know this. Still, if you want to get this information, you can access the Source property of the Err object and get this as a string.

Debugging and the Immediate Window

 

The Immediate Window

Debugging consists of examining and testing portions of your code or parts of your application to identify problems that may occur when somebody is using your database. Microsoft Visual Basic provides as many tools as possible to assist you with this task.

The Immediate window is an object you can use to test functions and expressions. It is available only in Visual Basic. To display use the Immediate window, on the main menu of Microsoft Visual Basic, you can click View -> Immediate Window. It's a habit to keep the Immediate window in the bottom section of the Code Editor but you can move it from there by dragging its title bar:

The Immediate Window

Probably the simplest action you can perform in the Immediate window consists of testing an expression. For example, you can write an arithmetic operation and examine its result. To do this, in the Immediate window, type the question mark "?" followed by the expression and press Enter. Here is an example that tests the result of 275.85 + 88.26:

Immediate Window

One of the most basic actions you can perform in the Immediate window consists of testing a built-in function. To do this, type ? followed by the name of the function and its arguments, if any. For example, to test the UCase$ function we reviewed in Lesson 5, in the Immediate window, you could type:

? UCase("République d'Afrique du Sud")

After typing the function and pressing Enter, the result would display in the next line:

Immediate Window

The Debug Object

The Immediate window is recognized in code as the Debug object. To programmatically display something, such as a string, in the Immediate window, the Debug object provides the Print method. The simplest way to use it consist of passing it a string. For example, imagine you create a button on a form, you name it cmdTestFullName and initialize it with a string. Here is an example of how you can display that string in the Immediate window:

Private Sub cmdTestFullName_Click()
    Dim strFullName$
    
    strFullName$ = "Daniel Ambassa"
    Debug.Print strFullName$
End Sub

When you click the button, the Immediate window would display the passed string:

Immediate Window

In the same way, you can create a more elaborate expression and test its value in the Immediate window:

Immediate Window

You can also pass a value, such as a date, that can easily be converted to a string.

 
 
   
 

Previous Copyright © 2005-2013 FunctionX, Inc. Next