The Visual Basic Development Environment

In Access for Windows 95 (version 7.0), Visual Basic replaced the Access Basic programming language included with versions 1, and 2, of Access. The two languages are very similar because both Visual Basic and Access Basic evolved from a common design created before either product existed. (It’s called Visual Basic because it was the first version of Basic designed specifically for the Windows graphical environment.) In recent years, Visual Basic has become the common programming language for Microsoft Office applications, including Access, Excel, Word, and PowerPoint. Some of the 2007 Office system products (including Word and Excel) can work with an even newer variant of Visual Basic-Visual Basic .NET-but Access does not.

Having a common programming language across applications provides several advantages. You have to learn only one programming language, and you can easily share objects across applications by using Visual Basic with object automation. Office Access 2007 uses the Visual Basic Editor common to all Microsoft Office applications and to the Visual Basic programming product. The Visual Basic Editor provides color-coded syntax, an Object Browser, and other features. It also provides excellent tools for testing and confirming the proper execution of the code you write.

Modules

You save all Visual Basic code in your database in modules. Access 2007 provides two ways to create a module: as a module object or as part of a form or report object.

Module Objects

You can view the module objects in your database by clicking the top of the Navigation Pane and then clicking Object Type under Navigate To Category. Click the Navigation Pane menu again, and click Modules under Filter By Group. Figure 19–1 shows the standard and class modules in the Conrad Systems Contacts sample database. (We also right-clicked the top of the Navigation Pane, clicked View By on the shortcut menu, and then Details on the submenu so you can see the descriptions we’ve attached to all the modules.) You should use module objects to define procedures that you need to call from queries or from several forms or reports in your application. You can call a public procedure defined in a module from anywhere in your application.

Figure 19–1: To see all the modules in your database, click Modules under Filter By Group on the Navigation Pane menu when you have Navigate To Category set to Object Type. On the Create tab, in the Other group, click the arrow under the Macro command and then click Module to create a new standard module.

To create a new module, on the Create tab, in the Other group, click the arrow under the Macro command, and click either Module or Class Module, also shown in Figure 19–1. (This button remembers the last object type you created in your current Access session, so it might say Module or Class Module.) When you click Module, Access creates a new standard module. You use a standard module to define procedures that you can call from anywhere in your application. It’s a good idea to name modules based on their purpose. For example, you might name a module that contains procedures to perform custom calculations for queries modQueryFunctions, and you might name a module containing procedures to work directly with Windows functions modWindowsAPIFunctions.

Advanced developers might want to create a special type of module object called a class module. A class module is a specification for a user-defined object in your application, and the Visual Basic procedures you create in a class module define the properties and methods that your object supports. You create a new class module by clicking the arrow under the Macro command and then clicking Class Module. You’ll learn more about objects, methods, properties, and class modules later in this chapter.

Form and Report Modules

To make it easy to create Visual Basic procedures that respond to events on forms or reports, Access 2007 supports a class module associated with each form or report. (You can design forms and reports that do not have a related class module.) A module associated with a form or report is also a class module that allows you to respond to events defined for the form or report as well as define extended properties and methods of the form or report. Within a form or report class module, you can create specially named event procedures to respond to Access-defined events, private procedures that you can call only from within the scope of the class module, and public procedures that you can call as methods of the class. See “Collections, Objects, Properties, and Methods” on page 978 for more information about objects and methods. You can edit the module for a form or a report by opening the form or report in Design view and then clicking the View Code button in the Tools group on the Design contextual tab (located under Form Design Tools). As you’ll learn later, you can also open a form or a report by setting an object equal to a new instance of the form or report’s class module.

Using form and report modules offers three main advantages over module objects.

• All the code you need to automate a form or a report resides with that form or report. You don’t have to remember the name of a separate form-related or reportrelated module object.

• Access loads module objects into memory when you first reference any procedure or variable in the module and leaves them loaded as long as the database is open. Access loads the code for a form or a report only when the form or the report is opened. Access unloads a form or a report class module when the object is closed; therefore, form and report modules consume memory only when you’re using the form or the report to which they are attached.

• If you export a form or report, all the code in the form or report module is exported with it.

However, form and report modules have one disadvantage: Because the code must be loaded each time you open the form or report, a form or report with a large supporting module opens noticeably more slowly than one that has little or no code. In addition, saving a form or report design can take longer if you have also opened the associated module and changed any of the code.

One enhancement that first appeared in Microsoft Access 97 (version 8.0)-the addition of the HasModule property-helps Access load forms and reports that have no code more rapidly. Access automatically sets this property to Yes if you try to view the code for a form or report, even if you don’t define any event procedures. If HasModule is No, Access doesn’t bother to look for an associated Visual Basic module, so the form or report loads more quickly.

Caution 

If you set the HasModule property to No in the Properties window, Access deletes the code module associated with the form or report. However, Access warns you and gives you a chance to change your mind if you set the HasModule property to No in error.

The Visual Basic Editor Window

When you open a module in Design view, Access 2007 opens the Visual Basic Editor and asks the editor to display your code. Open the Conrad Systems Contacts sample database (Contacts.accdb), view the Modules list in the Navigation Pane, and then either right-click the modExamples object and click Design View on the shortcut menu or double-click the modExamples object to see the code for this module opened in the Visual Basic Editor, as shown in Figure 19–2. Notice that the Visual Basic Editor in Access 2007 uses the older menu and toolbar technology from previous releases, not the Ribbon used in the main Access window.

Figure 19–2: Use the Visual Basic Editor to view and edit all Visual Basic code in your database.

What you see on your screen might differ from Figure 19–2, particularly if you have ^: opened the Visual Basic Editor previously and moved some windows around. In the upper-left corner of the figure, you can see the Visual Basic Project Explorer window docked in the workspace. (Click Project Explorer on the View menu or press Ctrl+R to see this window if it’s not visible.) In this window, you can discover all module objects and form and report class modules saved in the database. You can double-click any module to open it in the Code window, which you can see maximized in the upperright corner.

Docked in the lower-left corner is the Properties window. (Click Properties Window on the View menu or press F4 to see this window if it’s not visible.) When you have a form or report that has a Visual Basic module open in Design view in Access, you can click that object in the Project Explorer to see all its properties. If you modify a property in the Properties window, you’re changing it in Access. To open a form or report that is not open, you can select it in the Project Explorer and then click Object on the View menu.

In the lower-right corner you can see the Locals window docked. (Click Locals Window on the View menu to see this window if it’s not visible.) As you will see later, this window allows you to instantly see the values of any active variables or objects when you pause execution in a procedure. In the lower center you can see the Immediate window docked. (Click Immediate window on the View menu or press Ctrl+G to see this window if it’s not visible.) It’s called the Immediate Window because you can type any valid Visual Basic statement and press Enter to execute the statement immediately. You can also use a special “what is” command character (?) to find out the value of an expression or variable. For example, you can type ?5*20 and press Enter, and Visual Basic responds with the answer on the following line: 100.

You can undock any window by grabbing its title bar and dragging it away from its docked position on the edge toward the center of the screen. You can also undock a window by right-clicking anywhere in the window and clearing the Dockable property. As you will see later, you can set the Dockable property of any window by clicking Options on the Tools menu. When a window is set as Dockable but not docked along an edge, it becomes a pop-up window that floats on top of other windows-similar to the way an Access form works when its Pop Up property is set to Yes, as you learned in Chapter 12, “Customizing a Form.” When you make any window not Dockable, it shares the space occupied by the Code window.

You cannot set the Code window as Dockable. The Code window always appears in the part of the workspace that is not occupied by docked windows. You can maximize the Code window to fill this remaining space, as shown in Figure 19–2. You can also click the Restore button for this window and open multiple overlaid Code windows for different modules within the Code window space.

At the top of the Code window, just below the toolbar, you can see two drop-down lists.

• Object list   When you’re editing a form or report class module, open this list on the left to select the form or the report, a section on the form or the report, or any control on the form or the report that can generate an event. The Procedure list then shows the available event procedures for the selected object. Select General to view the Declarations section of the module, where you can set options or declare variables shared by multiple procedures. In a form or a report class module, General is also where you’ll see any procedures you have coded that do not respond to events. When you’re editing a standard module object, this list displays only the General option. In a class module object, you can choose General or Class.

• Procedure list   Open this list on the right to select a procedure in the module and display that procedure in the Code window. When you’re editing a form or report module, this list shows the available event procedures for the selected object and displays in bold type the event procedures that you have coded and attached to the form or the report. When you’re editing a module object, the list displays in alphabetic order all the procedures you coded in the module. In a class module when you have selected Class in the Object list, you can choose the special Initialize or Terminate procedures for the class.

In Figure 19–2, we dragged the divider bar at the top of the scroll bar on the right of the Code window downward to open two edit windows. We clicked in the lower window and then clicked ShowTables in the Procedure list box. You might find a split window very handy when you’re tracing calls from one procedure to another. The Procedure list box always shows you the name of the procedure that currently has the focus. In the Code window, you can use the arrow keys to move horizontally and vertically. When you enter a new line of code and press Enter, Visual Basic optionally verifies the syntax of the line and warns you of any problems it finds.

If you want to create a new procedure in a module, you can type either a Function statement, a Sub statement, or a Property statement on any blank line above or below an existing procedure and then press Enter, or click anywhere in the module and click the arrow to the right of the Insert button on the toolbar and then click Procedure, or click Procedure on the Insert menu. (For details about the Function and Sub statements, see “Functions and Subroutines” on page 1005. For details about the Property statement, see “Understanding Class Modules,” page 1009.) Visual Basic creates a new procedure for you (it does not embed the new procedure in the procedure you were editing) and inserts an End Function, End Sub, or End Property statement. When you create a new procedure using the Insert button or the Insert menu, Visual Basic opens a dialog box where you can enter the name of the new procedure, select the type of the procedure (Sub, Function, or Property), and select the scope of the procedure (Public or Private). To help you organize your procedures, Visual Basic inserts the new procedure in alphabetical sequence within the existing procedures.

Caution 

If you type a Function, Sub, or Property statement in the middle of an existing procedure, Visual Basic accepts the statement if it’s syntactically correct, but your project won’t compile because you cannot place a Function, Sub, or Property procedure inside another Function, Sub, or Property procedure.

If you’re working in a form or report module, you can select an object in the Object list and then open the Procedure list to see all the available events for that object. An event name displayed in bold type means you have created a procedure to handle that event. Select an event whose name isn’t displayed in bold type to create a procedure to handle that event.

Visual Basic provides many options that you can set to customize how you work with modules. Click Options on the Tools menu, and then click the Editor tab to see the settings for these options, as shown in Figure 19–3.

Figure 19–3: You can customize the Visual Basic Editor by using the settings on the Editor tab in the Options dialog box.

On the Editor tab, some important options to consider are Auto Syntax Check, to check the syntax of lines of code as you enter them; and Require Variable Declaration, which forces you to declare all your variables. (Require Variable Declaration is not selected by default-you’ll see later why it’s important to select it.) If you want to see required and optional parameters as you type complex function calls, select the Auto List Members check box. Auto Quick Info provides drop-down lists where appropriate built-in constants are available to complete parameters in function or subroutine calls. When you’re debugging code, Auto Data Tips lets you discover the current value of a variable by pausing your mouse pointer on any usage of the variable in your code.

Drag-And-Drop Text Editing allows you to highlight code and drag it to a new location. Default To Full Module View shows all your code for multiple procedures in a module in a single scrollable view. If you clear that check box, you will see only one procedure at a time and must page up or down or select a different procedure in the Procedure list box to move to a different part of the module. When you’re in full module view, selecting the Procedure Separator check box asks Visual Basic to draw a line between procedures to make it easy to see where one procedure ends and another begins.

Selecting the Auto Indent check box asks Visual Basic to leave you at the same indent as the previous line of code when you press the Enter key to insert a new line. We wrote all of the sample code you’ll see in this book and in the sample databases with indents to make it easy to see related lines of code within a loop or an If...Then...Else construct. You can set the Tab Width to any value from 1, through 32. This setting tells Visual Basic how many spaces you want to indent when you press the Tab key while writing code.

On the Editor Format tab of the Options dialog box, you can set custom colors for various types of code elements and also choose a display font. We recommend using a monospaced font such as Courier New for all code editing.

On the General tab, shown in Figure 19–4, you can set some important options that dictate how Visual Basic acts as you enter new code and as you debug your code. You can ignore all the settings under Form Grid Settings because they apply to forms designed in Visual Basic, not Access.

Figure 19–4: You can modify settings to help you debug your code on the General tab in the Options dialog box.

If your code has halted, in many cases you can enter new code or correct problems in code before continuing to test. Some changes you make, however, will force Visual Basic to reset rather than let you continue to run from the halted point. If you select the Notify Before State Loss check box, Visual Basic will warn you before allowing you to make code changes that would cause it to reset.

In the Error Trapping section, you can select one of three ways to tell Visual Basic how to deal with errors. As you’ll discover later in this chapter, you can write statements in your code to attempt to catch errors. If you think you have a problem in your errortrapping code, you can select Break On All Errors. With this setting, Visual Basic ignores all error trapping and halts execution on any error. If you have written class modules that can be called from other modules, to catch an untrapped error that occurs within a class module, choose Break In Class Module to halt on the statement within the class module that failed. (We recommend this setting for most testing.) If you choose Break On Unhandled Errors, and an untrapped error occurs within a class module, Visual Basic halts on the statement that invoked the class module.

The last two important options on this tab are Compile On Demand and Background Compile. With the Compile On Demand check box selected, Visual Basic compiles any previously uncompiled new code whenever you run that code directly or run a procedure that calls that code. Background Compile lets Visual Basic use spare CPU cycles to compile new code as you are working in other areas.

Finally, on the Docking tab you can specify whether the Immediate window, Locals window, Watch window, Project Explorer, Properties window, or Object Browser can be docked. We will take a look at the Immediate window and Watch window in the next section. You can use the Object Browser to discover all the supported properties and methods of any object or function defined in Access, Visual Basic, or your database application.

Inside Out-Understanding the Relationship Between Access and Visual Basic 

Access 2007 and Visual Basic work as two separate but interlinked products in your Access application. Access handles the storage of the Visual Basic project (both the source code and the compiled code) in your desktop database (.accdb) or project (.adp) file, and it calls Visual Basic to manage the editing and execution of your code.

Because Access tightly links your forms and reports with class modules stored in the Visual Basic project, some complex synchronization must happen between the two products. For example, when you open a form module and enter a new event procedure in the Visual Basic Code window, Access must set the appropriate event property to [Event Procedure] so that both the form and the code are correctly linked. Likewise, when you delete all the code in an event procedure, Access must clear the related form or control property. So, when you open a form or report module from the Visual Basic Editor window, you’ll notice that Access also opens the related form or report object in the Access window.

When Access first began using Visual Basic (instead of Access Basic) in version 7.0 (Microsoft Access for Windows 95), it was possible to end up with a corrupted Visual Basic project or corrupted form or report object if you weren’t careful to always compile and save both the code and the form or report definition at the same time when you made changes to either. It was particularly easy to encounter corruption if multiple developers had the database open at the same time. This corruption most often occurred when Access failed to merge a changed module back into the Visual Basic project when the developer saved changes.

Microsoft greatly improved the reliability of this process when it switched in version 9.0 (Microsoft Access 2000) to saving the entire Visual Basic project whenever you save a change. However, this change means that two developers can no longer have the same database open and be working in the code at the same time. This also means that your Access file can grow rapidly if you’re making frequent changes to the code and saving your changes.

When you’re making multiple changes in an Access application, we recommend that you always compile your project when you have finished changing a section of code. (Click Compile on the Debug menu in the Visual Basic Editor.) You should also save all at once multiple objects that you have changed by clicking the Save button in the Visual Basic Editor window and always responding Yes to the Save dialog box that Access shows you when you have multiple changed objects open.

Working with Visual Basic Debugging Tools

You might have noticed that the debugging tools for macros are very primitive. You can’t do much more than run 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 all of the ShowTables function, as shown in Figure 19–5. 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).

Figure 19–5: You can set a breakpoint in a Visual Basic module to help you debug your code.

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, singlestep 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). In the example shown in Figure 19–5, we set a breakpoint to halt the procedure at the bottom of the loop that examines each table. When you run the procedure later, you’ll see that Visual Basic will halt on this statement just before it executes the statement.

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 (shown earlier in Figure 19–2), 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 chapter) 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

 ?gi ntDontShowCompanyLi st

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

 ?gintDontShowCompanyLi st

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 Visual Basic Editor window. Open the frmMain form in Form view. Click the Companies button to find out whether the Select Companies form or the Companies/Organizations form opens. If you go directly to the Select Companies form, then gintDontShowCom-panyList must be False (0). Close the form that opens.

Now, go back to the Visual Basic Editor 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 try the Companies button 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.

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 Visual Basic Editor window, find the ShowTables function, and be sure you have set a breakpoint on the Next tbl statement as shown in Figure 19–6.

Figure 19–6: You can execute a module function from the Immediate window.

Because the ShowTables procedure is a function that might return a value, you have to ask Visual Basic to evaluate the function in order 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, as shown in Figure 19–6, 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, as shown in Figure 19–7. 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 you can see across the bottom of Figure 19–7. (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 “Collections, Objects, Properties, and Methods” on page 978 for details about all the objects you see in the “tree” under the database catalog.

Figure 19–7: When your Visual Basic code stops at a breakpoint, you can use the Locals window to examine variable and object values.

The Immediate window displays the output of three Debug.Print statements within the function you’re running, as also shown in Figure 19–7.

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 on 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.

Working with the Watch Window

Sometimes setting a breakpoint isn’t enough to catch an error. You might have a variable that you know is being changed somewhere by your code (perhaps incorrectly). By using the Watch window, you can examine a variable as your code runs, ask Visual Basic to halt when an expression that uses the variable becomes true, or ask Visual Basic to halt when the variable changes.

An interesting set of variables in the Conrad Systems Contacts sample database are gintDontShowCompanyList, gintDontShowContactList, and gintDontShowInvoiceList (all defined in the modGlobals module). When any of these variables are set to True, the main switchboard bypasses the intermediate list/search form for companies, contacts, and invoices, respectively. You played with one of these variables earlier, but it would be interesting to trap when these are set or reset.

Caution 

There are a couple of known issues with setting breakpoints in Access 2007. First, code will not halt if you have cleared the Use Access Special Keys check box in the Application Options section of the Current Database category of the Access Options dialog box (click the Microsoft Office Button and then click Access Options). Second, the Break When Value Is True and Break When Value Changes options in the Add Watch dialog box will not work if the value or expression you’re watching is changed in a form or report module that is not already open in the Visual Basic Editor. For this example to work, the form modules for frmMain, frmSignon, and frmUsers must be open. You can verify that these modules are open by opening the Windows menu in the Visual Basic Editor window. The Contacts.accdb sample file should have modules open, but these modules might not be open in your copy if you have closed them and compiled and saved the project You can find these modules in the Project Explorer window. Open the list of objects in the Microsoft Class Objects category and then double-click the form modules that you need to open them.

To set a watch for when the value changes, open the Watch window by clicking it on the View menu, right-click in the Watch window, and click Add Watch on the shortcut menu. You can also click Add Watch on the Debug menu. You should see the Add Watch dialog box, as shown in Figure 19–8.

Figure 19–8: You can set a watch for when a variable’s value changes.

In the Expression box, enter the name of the variable you want the code to watch. In this case, you want to watch when the gintDontShowContactList variable changes. You don’t know where the variable is set, so set the Procedure and Module selections to (All Procedures) and (All Modules), respectively. Under Watch Type, select the Break When Value Changes option, and click OK to set the watch. Go to the Immediate window and set gintDontShowContactList to True by entering gintDontShowContactList=True and pressing Enter. Now return to the Navigation Pane and start the application by opening the frmSplash form. (Code in the Load event of this form hides the Navigation Pane and then opens the Conrad Systems Contacts Sign On form.) Because you set a watch to halt when gintDontShowContactList changes, the code execution should halt in the module for the frmSignOn form as shown in Figure 19–9.

Figure 19–9: Visual Basic code halts immediately after a watch variable has changed.

Note that the code halts on the statement immediately after the one that reset the watched variable. If you didn’t set the variable to True before you started the application, Visual Basic won’t halt because the value won’t be changing.

Click the Continue button (or press F5) to let the code continue executing. Return to the Access window, and in the Conrad Systems Contacts Sign On dialog box, select my name (John Viescas) and press Enter or click the Sign On button. The sign on dialog box will close, and the main switchboard form opens. In the main switchboard, click the Users button to open the user edit form. The second record should be my record unless you’ve created other users. Select the Don’t Show Contact List check box in my record and click the Save button. The procedure halts again, as shown in Figure 19–10.

Figure 19–10: The gintDontShowContactList variable is set to the value of a form control.

It appears that this code is setting the gintDontShowContactList variable to some value on the user edit form. (As you’ll learn later, Me is a shorthand way to reference the form object where your code is running, so Me.DontShowContactList references a control on the form.) Click the Continue button again to let the code finish execution. Return to the Access window and click the Close button on the Users form to return to the main switchboard.

If you open frmUsers in Design view (you can’t do this while the procedure is still halted) and examine the names of the check box controls on the form, you’ll find that the check box you selected is named DontShowContactList. When the code behind frmUsers detects a change to the options for the currently signed-on user, it makes sure the option variables in modGlobals get changed as well. Be sure to close the frmUsers form when you’re finished looking at it.

Examining the Procedure Call Sequence (Call Stack)

After stopping code that you’re trying to debug, it’s useful sometimes to find out what started the current sequence of code execution and what procedures have been called by Visual Basic. For this example, you can continue with the watch on the gintDont-ShowContactList variable.

You should now be at the main switchboard form (frmMain) in the application. Click the Exit button to close the application and return to the Navigation Pane. (You’ll see a prompt asking you if you’re sure you want to exit-click Yes. You might also see a prompt offering to back up the data file-click No.) The code should halt again in the Close event of the frmMain form. Click the Call Stack button on the toolbar or click Call Stack on the View menu to see the call sequence shown in Figure 19–11.

Figure 19–11: When your code is halted, you can see the chain of code executed to the point of the halt in the Call Stack dialog box.

The Call Stack dialog box shows the procedures that have executed, with the most recent procedure at the top of the list, and the first procedure at the bottom. You can see that the code started executing in the cmdExit_Click procedure of the frmMain form. This happens to be the Visual Basic event procedure that runs when you click the Exit button. If you click that line and then click the Show button, you should see the cmdExit_Click procedure in the module for the frmMain form (the switchboard) with the cursor on the line that executes the DoCmd.Close command to close the form. This line calls the Access built-in Close command (the <Non-Basic Code> you see in the call stack list), which in turn triggered the Close event procedure for the form. It’s the Close event procedure code that sets the gintDontShowContactList variable back to False (0). Be sure that the Call Stack dialog box is closed and click Continue on the toolbar to let the code finish running.

Note 

Be sure to delete the watch after you are finished seeing how it works by right-clicking it in the Watch window and clicking Delete on the shortcut menu.