Examining the Utility Functions Module

One recommended way to learn a new programming language is to examine simple examples of code and analyze the statements used in the example.

The sections that follow show how to open a module, display a function in the Module window, add a breakpoint to the code, and then use the Immediate window to execute the function.

Adding a Breakpoint to the IsLoaded() Function

When you examine the execution of VBA code written by others, and when you debug your own application, breakpoints are very useful. This section explains how to add a breakpoint to the IsLoaded() function so that the Suppliers form stops executing when the Suppliers form's On Current event calls the Form_Current event handler, which in turn calls the IsLoaded() function and Access displays the code in the Module window.

Tip

To see how form events call event handlers in a class module, change to Design view, open the Properties window, and click the Event tab. Click the Builder button of the On Current event, which has [Event Procedure] in its text box, to open the VBA editor with the corresponding event-handling subprocedure in the code window.

To add a breakpoint to the IsLoaded() function, follow these steps:

1. If you have the VBA editor open with Northwind as the current database, double-click Utility Functions in the Project Explorer. Otherwise, display the Database window, click the Modules shortcut, and double-click the Utility Functions module to open the VBA editor with Utility Functions active.

2. Place the cursor on the line that begins with If oAccessObject = ... and press F9. The breakpoint you create is indicated by changing the display of the line to reverse red and by the placement of a red dot in the margin indicator at the left of the window (see Figure 27.11, which has a line break added).

   Figure 27.11. Adding a breakpoint to an instruction with a CCC highlights the entire instruction and adds a red dot for each line of the instruction.

   

3. Click the View Microsoft Access button and open the Suppliers form to execute the Form_Current procedure attached to the On Current event of the form. When the Suppliers form's Form_Current procedure calls the IsLoaded() function, the execution of IsLoaded() begins with the Set oAccessObject = CurrentProject. AllForms(strFormName) line and halts at the line with the breakpoint. When execution encounters a breakpoint, the module containing the breakpoint opens automatically. The line with the breakpoint turns yellow (see Figure 27.12).

   Figure 27.12. When VBA execution encounters a breakpoint, the interpreter stops before executing the instruction. The code of the breakpoint line turns yellow. Passing the cursor over a variable displays its value in a Data Tips window.

   

4. Press F5 or click the Run Sub/UserForm button to resume execution of the VBA code. Alternatively, press F8 to step through the remaining lines of code. Access displays the Suppliers form.

5. Close the Suppliers form to execute the Form_Close procedure that's attached to the form's On Close event. When the Suppliers form's Form_Close procedure calls the IsLoaded() function, execution occurs as described in step 3, and the IsLoaded() function again halts at the line with the breakpoint. In this case, IsLoaded returns False.

6. Place the cursor on one of the highlighted lines, and press F9 to toggle the breakpoint off.

Tip

Data Tips, which are similar in appearance to ToolTips or ScreenTips, display the name and value of variables in break mode. When you pass the mouse pointer over the strFormName argument, a DataTips window displays the value.

The Set oAccessObject = CurrentProject.AllForms(strFormName) instruction returns a pointer to the member of the AllForms collection specified by strFormName. If strFormName isn't a member of the collection, you receive a runtime error, because the IsLoaded() function doesn't include error-handling code.

Printing to the Immediate Window with the Debug Object

Previous chapters of this book introduced you to the VBA editor's Immediate window and showed you how to obtain the values of variables with ?VarName statements. When you want to view the values of several variables, you can use the Print method of the Debug object to automate printing to the Immediate window. If you add the Debug object to a function that tests the names of each open form, you can create a list in the Immediate window of all the forms that are open.

The Forms and AllForms collections contain a Form or AccessObject member for each form in the project. You can use a For ... Next loop with a counter to obtain the form name or a For Each ObjectName In CollectionName ... Next loop to avoid declaring a counter variable and specify the counter value as the Item index. You must declare a Form or AccessObject variable, however, to use For Each.

To create a WhatsLoaded() function to list all open forms, follow these steps:

1. Load three or more forms. The Customers, Categories, Employees, and Main Switchboard forms are good choices because these forms load quickly.

2. In the Utility Functions module, type Private Sub WhatsLoaded() below the End Function line of the IsLoaded() function. The VBA interpreter adds the End Sub statement for you automatically.

3. Type the following code between the Private Sub... and End Sub lines:

    Dim intCtr As Integer For intCtr = 0 To Forms.Count - 1    Debug.Print intCtr & " = " & Forms(intCtr).FormName Next intCtr 

   The For...Next loop iterates the Forms collection. The Debug.Print statement prints the name of each open form in the Immediate window.

   Note

   The VBA editor includes a powerful feature called statement autocompletion to help you write VBA code. The interpreter monitors each line of code as you type it in. When you type variable declarations, use built-in Access and VBA functions, or use object methods and properties in your code, the interpreter displays a pop-up window to help you select appropriate values.

   Figure 27.13 shows the pop-up list window that appears after you type the As keyword in the first Dim statement of the code you enter in step 3. For procedures, functions, and methods, the pop-up help window lists all the arguments for the procedure, function, or method, so you don't have to remember all the possible arguments. You can turn this feature on and off by choosing Tools, Options and then selecting or clearing the Auto List Members check box on the Editor tab of the Options dialog.

   Figure 27.13. As you type VBA code in the editor window, an autocompletion list opens to provide a list of keywords, constants, or objects that are candidates for the following entry. As you add letters, the list displays items whose names start with those letters.

   

4. Place the cursor anywhere within the WhatsLoaded code you typed and press F5 or click the Run Sub/UserForm button. If the Immediate window isn't open, press Ctrl+G to open it. (The G shortcut comes from the windows prior name Debug.) The name of each form is added to the Immediate window by the Debug.Print statement (see Figure 27.14).

   Figure 27.14. Executing the WhatsLoaded subprocedure prints a list of the forms open in Access.

   

5. Close and don't save changes to the Utility Function module. Then close the other forms you opened for this example.

The Debug.Print statement is particularly useful for displaying the values of variables that change when you execute a loop. When you've completed the testing of your procedure, you delete the Debug statements.

To create a list of all forms and their loaded state, add the following code to the module:

 Private Sub FormState()   Dim accForm As AccessObject   For Each accForm In CurrentProject.AllForms     Debug.Print accForm.Name & _       "Open = " & accForm.IsLoaded   Next accForm End Sub 

With the cursor on a line in the subprocedure, press F5 to execute the function. The Immediate window displays a list of all forms in the current project (see Figure 27.15).

Figure 27.15. The FormState subprocedure lists all forms in the database and indicates if the form is open.