MS-Access / Getting Started

---

Visual Basic Debugging Tools

You might have noticed that the debugging tools for data macros and user interface macros are limited. You can't do much more than run user interface macros in Single Step mode to try to find the source of an error. The debugging tools for Visual Basic are significantly more extensive. The following sections describe many of the tools available in Visual Basic. You might want to scan these sections first and then return after you have learned more about the Visual Basic language and have begun writing procedures that you need to debug.

Setting Breakpoints

If you still have the modExamples module open, scroll down until you can see the entire ShowTables function. This sample function examines all the table definitions in the current database and displays the table name, the names of any indexes defined for the table, and the names of columns in each index by printing to a special object called Debug (another name for the Immediate window).

One of the most common ways to test particularly complex code is to open the module you want to examine, set a stopping point in the code (called a breakpoint), and then run the code. Visual Basic halts before executing the statement on the line where you set the breakpoint. As you'll soon see, when Visual Basic stops at a breakpoint, you can examine all sorts of information to help you clean up potential problems. While a procedure is stopped, you can look at the values in variables-including all object variables you might have defined. In addition, you can also change the value of variables, single-step through the code, reset the code, or restart at a different statement.

To set a breakpoint, click anywhere on the line of code where you want Visual Basic execution to halt and either click the Toggle Breakpoint button on the Debug toolbar (open this toolbar by right-clicking any toolbar and clicking Debug on the shortcut menu), click Toggle Breakpoint on the Debug menu, or press F9 to set or clear a breakpoint. When a breakpoint is active, Access highlights the line of code (in red by default) where the breakpoint is established and displays a dot on the selection bar to the left of the line of code. Note that you can set as many breakpoints as you like, anywhere in any module. After you set a breakpoint, the breakpoint stays active until you close the current database, specifically clear the breakpoint, or click Clear All Breakpoints on the Debug menu (or press Ctrl+Shift+F9).

Using the Immediate Window

"Action central" for all troubleshooting in Visual Basic is a special edit window called the Immediate window. You can open the Immediate window while editing a module by clicking the Immediate Window button on the Debug toolbar or clicking Immediate Window on the View menu. Even when you do not have a Visual Basic module open, you can open the Immediate window from anywhere in Access by pressing Ctrl+G.

Executing Visual Basic Commands in the Immediate Window In the Immediate window, you can type any valid Visual Basic command and press Enter to have it executed immediately. You can also execute a procedure by typing the procedure name followed by any parameter values required by the procedure. You can ask Visual Basic to evaluate any expression by typing a question mark character (sometimes called the "what is" character) followed by the expression. Access displays the result of the evaluation on the line below. You might want to experiment by typing ?(5 * 4) / 10. You will see the answer 2 on the line below.

Because you can type any valid Visual Basic statement, you can enter an assignment statement (the name of a variable, an equals sign, and the value you want to assign to the variable) to set a variable that you might have forgotten to set correctly in your code. For example, there's a public variable (you'll learn more about variables later in this tutorial) called gintDontShowCompanyList that the Conrad Systems Contacts sample application uses to save whether the current user wants to see the Select Companies pop-up window when clicking Companies on the main switchboard. Some users may prefer to go directly to the Companies/Organizations form that edits all companies rather than select or filter the list. If you have been running the Conrad Systems Contacts application, you can find out the current value of the string by typing

?gintDontShowCompanyList

Visual Basic displays the value of this variable, which should be either 0 or -1. You can set the value of this string to False (0) by typing

gintDontShowCompanyList = 0

You can verify the value of the variable you just set by typing

?gintDontShowCompanyList

If you assigned 0 to the variable, you should see that value echoed in the Immediate window.

To have a sense of the power of what you're doing, go to the Database window in Access by clicking the View Microsoft Access button on the left end of the toolbar in the VBE window. Open the frmMain form in Form view. Click Companies to find out whether the Select Companies form or the Companies/Organizations form opens. If you go directly to the Select Companies form, then gintDontShowCompanyList must be False (0). Close the form that opens.

Now, go back to the VBE window. (An easy way to do this is to use the Windows Alt+Tab feature.) In the Visual Basic Immediate window, set the value to True by entering in the Immediate window

gintDontShowCompanyList = True

Go back to the main switchboard and click Companies again. Because you set the public variable to True, you should go directly to the Companies/Organizations form. Now that you have the form open to edit companies, you can set a filter directly from the Immediate window. Go back to that window and enter the expression

Forms!frmCompanies.Filter = "[StateOrProvince] = 'PA'"

If you want, you can ask what the filter property is to see if it is set correctly. Note that nothing has happened yet to the form. Next, turn on the form's FilterOn property by entering

Forms!frmCompanies.FilterOn = True

Return to the form, and you should now see the form filtered down to two rows-all the companies in the state of Pennsylvania. If you want to try another example, return to the Immediate window and enter

Forms!frmCompanies.Section(0).Backcolor = 255

The background of Section(0), the detail area of the form, should now appear red. Note that none of these changes affect the design of the form. You can close the form, and the next time you open it, the form will have a normal background color, and the records won't be filtered. Close the forms you have open to continue with the next section.

Using Breakpoints You saw earlier how to set a breakpoint within a module procedure. To see how a breakpoint works, open the modExamples module in the VBE window, find the ShowTables function, and be sure you have set a breakpoint on the Next tbl statement.

Because the ShowTables procedure is a function that might return a value, you have to ask Visual Basic to evaluate the function to run it. The function doesn't require any parameters, so you don't need to supply any. To run the function, type ?ShowTables() in the Immediate window and press Enter.

Note:
You can also ask Visual Basic to run any public procedure by clicking in the procedure and clicking the Run button on either the Standard or Debug toolbar.

Visual Basic runs the function you requested. Because you set a breakpoint, the code stops on the statement with the breakpoint. The first table in the database is actually a linked table (an Excel spreadsheet), so you won't see any output. Click the Continue button on the toolbar to run through the loop a second time to display the first table.

Note that we clicked Locals Window on the View menu to reveal the Locals window that you can see across the bottom. (We undocked the Immediate window so you can see more of the Locals window.) In the Locals window, Visual Basic shows you all the active variables. You can, for example, click the plus sign (+) next to the word cat (a variable set to the currently opened database catalog) to browse through all the property settings for the database and all the objects within the database. You can click on the tbl variable to explore the columns and properties in the table. See section "Collections, Objects, Properties, and Methods," for details about all the objects you see in the "tree" under the database catalog later in this tutorial.

The Immediate window displays the output of three Debug.Print statements within the function you're running.

The first line shows the name of the first table (Errorlog) that the function found in the database. The second (indented) line shows the name of the index for that table. The third line shows the name of the one column in the index.

If you want to see the results of executing the next loop in the code (examining the next table object in the catalog), click the Continue button on the toolbar. If you want to run the code a single statement at a time, click Step Into or Step Over on the Debug menu or open the Debug toolbar and click the Step Into or Step Over button. Step Into and Step Over work the same unless you're about to execute a statement that calls another procedure. If the next statement calls another procedure, Step Into literally steps into the called procedure so that you can step through the code in the called procedure one line at a time. Step Over calls the procedure without halting and stops at the next statement in the current procedure.

When you are finished studying the loop in the ShowTables function, be sure to click the Reset button on the toolbar to halt code execution.

Note:
The Tables collection in the catalog includes tables, linked tables, system tables, and queries. Because the ShowTables procedure only looks for tables, you will need to loop through the code several times until the procedure finds the next object that defines a table. You should quickly find the ErrorLog, ErrTable, and ErrTableSample tables, but the code must then loop through all the queries and linked tables (more than 40 of them) before finding the SwitchboardDriver table.