ACC: Tips for Debugging Access Basic Code

ID: Q108438

The information in this article applies to:

Microsoft Access versions 1.0, 1.1, 2.0

SUMMARY

Moderate: Requires basic macro, coding, and interoperability skills.

This article contains tips to help you debug your Access Basic code. This article assumes that your code compiles correctly, but does not perform as you expect.

MORE INFORMATION

The general procedure for debugging Access Basic code is to break larger procedures apart into smaller sub-procedures, verify that the individual steps in each sub-procedure work correctly, and then combine the sub- procedures one by one until the code works correctly.

Using the Immediate Window

You can use the Immediate window to execute individual lines of Access Basic code, or to check the values of variables. Therefore, you can use the Immediate window to:

Test and debug Function and Sub procedures.

Check the value of a field, control, property setting, variable, or expression.

Display the result of an expression when the code is running.

The following steps demonstrate how to use the Immediate window to debug your Access Basic functions:

Open the module containing your function in Design view.

From the View menu, choose Immediate Window.

Run your function in the Immediate window by typing "?<FunctionName>()" (without the quotation marks) and then pressing ENTER. Substitute the name of your function for <FunctionName>, and be sure to place all arguments expected by your function inside the parentheses.

For more information on the Immediate window, search for "immediate window" then "Immediate Window" using the Microsoft Access Help menu.

Using Breakpoints with the Immediate Window

A breakpoint suspends execution of the code, allowing you to check the values of variables. You can use the Immediate window with breakpoints to pinpoint code problems quickly.

For example, if the first part of your code runs correctly, but other parts do not, you can follow the sample steps below to find the malfunctioning part:

Set a breakpoint right after the part that you know works correctly. To set a breakpoint:
1. Move your cursor to the line of code where you want to set the breakpoint.
2. Click the Breakpoint button (the five-fingered hand) on the toolbar. Or, press F9, or choose Toggle Breakpoint from the Run menu. The line will appear in bold type, indicating that there is a breakpoint set at that line.

Run the function in the Immediate window (see "Using The Immediate Window" above). When code execution reaches the breakpoint, processing is suspended and the breakpoint line is selected. You can then check the value of variables in the code.

For example, to check the value of a variable called MyName, type the following in the Immediate window and then press ENTER:

?MyName

The value of MyName is displayed in the Immediate window. If the value is correct, check other variables. If the value is incorrect, check the previous lines of code to see where this variable is assigned an incorrect value.

Using the Debug.Print Statement

You can use the Debug.Print statement in your code to display the values of your variables in the Immediate window. Good places to include Debug.Print statements include:

The beginnings of functions, to check the arguments passed to the function. For example, to check the values of two arguments passed to the DoSomeCalcs() function, place the following sample Debug.Print statement at the beginning of the function:
```
      Function DoSomeCalcs(Arg1, Arg2)
         Debug.Print "Function DoSomeCalcs " & Arg1 & " " & Arg2 
```
When the DoSomeCalcs() function is run, the text "Function DoSomeCalcs" and the values of the two arguments Arg1 and Arg2 will be displayed in the Immediate window. If the values of the arguments are not correct, the problem is occurring before the function is run.

You can also use the Debug.Print statement at the beginning of each function if you have a complex application and you are not sure which function might be causing the problem. This allows you to check the Immediate window to see the order in which functions were called, and determine which was the last function called. In Microsoft Access 2.0, you can also select Calls from the View menu to list the current function call stack, and show the order that functions were called.

Decision structures, to check that you are using the correct logic for the function. For example, the sample code on page 23 of the Microsoft Access "Introduction to Programming" manual uses a Select Case statement. You can add a Debug.Print statement to this sample code, as demonstrated below, to verify that the code is doing what you expect:


      Function DueDate (anyDate)
      Debug.Print "Function DueDate " & anyDate
      Dim Result
         If Not IsNull(anyDate) Then
            Result = DateSerial(Year(anyDate), Month(anyDate) + 1, 1)
            Debug.Print "Result: " & Result
            Debug.Print "Weekday(Result): " & Weekday(Result)
            Select Case Weekday(Result)
               Case 1:      'Sunday
                  Debug.Print "Case 1"
                  DueDate = Result + 1
               Case 7:      'Saturday
                  Debug.Print "Case 7"
                  DueDate = Result + 2
               Case 6:      'Friday
                  Debug.Print "Case 6"
                  DueDate = Result - 1
               Case Else
                  Debug.Print "Case Else"
                  DueDate = Result
            End Select
         Else
            Result = Null
         End If
      End Function

Run this function in the Immediate window by typing the following and then pressing ENTER:

?DueDate(#10/1/93#)

The following results will be displayed in the Immediate window:


      Function DueDate 10/1/93
      Result: 11/1/93
      Weekday(Result): 2
      Case Else
      11/1/93

These results show that you are using the correct logic for this function. If you had received different results (the wrong case, for example) you could then check the values of other values and expressions to determine where the problem is.

In loops, to check the values of variables. If you use a breakpoint to check the value of a variable in a loop, you cannot see how the value of the variable changes as the loop executes. However, if you include the statement "Debug.Print MyName" (without the quotation marks) in your loop structure, the value of variable MyName will be displayed in the Immediate window each time the function cycles through the loop.

This method is also a good way to verify that the loop is executing the number of times you expect. If the loop executes four times, you should see four values for the value in the Immediate window.

Where clauses, to check the values of criteria in SQL statements. For example, the following sample function creates a dynaset from a SQL statement. If there is a problem in a SQL statement (such as in this function), it can be difficult to locate the problem. However, this function uses the Debug.Print statement to display the SQL statement as Microsoft Access sees it and uses it:
```
      Function TestMe()
         Dim db As Database, ds As Dynaset
         Dim empnum As Long
         Dim sql As String
         Set db = CurrentDB()
         empnum = 5
         sql = "select * from orders where [employee id]=empnum;"
         Debug.Print sql
         Set ds = db.CreateDynaset(sql)
      End Function 
```
Run this function in the Immediate window by typing the following and then pressing ENTER:

?TestMe()

The following result is displayed:
```
      select * from orders where [employee id]=empnum; 
```
Note that the Where condition shows [employee id] = empnum, not [employee id] = 5, as you assigned it. To fix this particular problem, change the Where condition to concatenate the empnum variable, as follows:
```
      Function testme()
         Dim db As Database, ds As Dynaset
         Dim empnum As Long
         Dim sql As String
         Set db = CurrentDB()
         empnum = 5
         sql = "select * from orders where [employee id]=" & empnum & ";"
         Debug.Print sql
         Set ds = db.CreateDynaset(sql)
      End Function 
```
When you run the corrected function, the following statement will be displayed:
```
      select * from orders where [employee id]=5; 
```

Stepping Through the Code

Stepping through your code means executing the code one line at a time. This is sometimes referred to as tracing. Access Basic has two ways of stepping through code: single stepping, and procedure stepping.

Single stepping executes each step of a called procedure, stopping at each line in the procedure. Procedure stepping executes a called procedure all at once, then returns to the next line in the current procedure.

To step through your code:

Set a breakpoint in your code at the line where you want to begin stepping. (See "Using Breakpoints with the Immediate Window", above). To step through an entire function, set the breakpoint on the first line of the function.

Start the function. When program execution reaches the breakpoint, the line with the breakpoint will be selected in the function's module.

To single step, click the Single Step button (single foot) on the toolbar. Or, press F8, or choose Single Step from the Run menu. The selected line of code will be executed, and then the next line of code will be selected. You can continue through your code one line at at time in this fashion.

If your code calls other functions or procedures you will single step through them as well. If you do not want to single step through these other procedures, click the Procedure Step button (double foot) on the toolbar. Or, press SHIFT+F8, or choose Procedure Step from the Run menu.

Note that as you step through your code, you can check the values of variables in the Immediate window (see "Using the Immediate Window", above).

If you want to continue running your code normally (without single or procedure stepping), click the Run button (exclamation point) on the toolbar. Or, press F5, or choose Continue from the Run menu.

REFERENCES

Microsoft Access "Introduction to Programming," version 1.x, Chapter 4, "Debugging Your Access Basic Code"

Microsoft Access "Building Applications," version 2.0, Chapter 9, "Debugging"

Additional query words: kbtshoot trouble shoot


Keywords          : kbprg PgmErr 
Version           : 1.0 1.1 2.0
Platform          : WINDOWS 
Issue type        : kbinfo

Last Reviewed: March 30, 1999