This article was previously published under Q108438
This article has been archived. It is offered "as is" and will no longer be updated.
Moderate: Requires basic macro, coding, and interoperability skills.
This article contains tips to help you debug your Access Basic code. Thisarticle assumes that your code compiles correctly, but does not perform asyou expect.
The general procedure for debugging Access Basic code is to break largerprocedures apart into smaller sub-procedures, verify that the individualsteps 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 AccessBasic code, or to check the values of variables. Therefore, you can use theImmediate 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 debugyour 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 thevalues of variables. You can use the Immediate window with breakpoints topinpoint code problems quickly.
For example, if the first part of your code runs correctly, but other partsdo not, you can follow the sample steps below to find the malfunctioningpart:
Set a breakpoint right after the part that you know works correctly. To set a breakpoint:
Move your cursor to the line of code where you want to set the breakpoint.
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:
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 ofyour variables in the Immediate window. Good places to include Debug.Printstatements 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:
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:
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:
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 ofstepping through code: single stepping, and procedure stepping.
Single stepping executes each step of a called procedure, stopping at eachline in the procedure. Procedure stepping executes a called procedure allat 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 stepthrough them as well. If you do not want to single step through these otherprocedures, 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 ofvariables in the Immediate window (see "Using the Immediate Window",above).
If you want to continue running your code normally (without single orprocedure stepping), click the Run button (exclamation point) on thetoolbar. Or, press F5, or choose Continue from the Run menu.
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"