Using Breakpoints

If you know more or less where there may be a problem in your procedure code, you should suspend code execution at that location (on a given line). Set a breakpoint by pressing F9 when the cursor is on the desired line of code. When VBA gets to that line while running your procedure, it will display the Code window immediately. At this point you can step through the procedure code line by line by pressing F8 or choosing Debug | Step Into.

To see how this works, let's look at the following scenario. Assume that during the execution of the ListEndDates function procedure (see Custom Project 9-1) the following line of code could get you into trouble:

ListEndDates = Format(((Now() + intOffset) - 35) + 7 * row, "MM/DD/YYYY")

Introduction to Access 2003 VBA Programming

® Custom Project 9-1: Debugging a Function Procedure

This chapter's hands-on exercises and custom project are provided in the Acc2003_Chap09.mdb file included in the book's downloadable files.

1. Open Acc2003_Chap09.mdb or, if you'd like to start from scratch, create a new Microsoft Office Access 2003 database.

2. Prepare the form as shown in Figure 9-3.

3. Set the following control properties:

Control Name

Property Name

Property Setting

combo box

Name

cboEndDate

Row Source Type

ListEndDates

Column Count

1

text box controls

Name

txt1

txt2

txt3

txt4

txt5

txt6

txt7

Figure 9-3: The combo box control shown on this form will be filled with the result of the ListEndDates function.

4. Save the form as frmTimeSheet.

5. Press Ctrl+R to select the form. Activate the Properties window and click the Event tab. Choose [Event Procedure] from the drop-down list next to the On Load property, then click the Build button (...). Type the following Form_Load procedure when the Code window appears:

Private Sub Form_Load() With Me.cboEndDate .SetFocus

.ListIndex = 5 ' Select current ending date End With End Sub

Part I

6. Choose the combo box control (cboEndDate) on the form, and activate the Properties window. Choose [Event Procedure] from the drop-down list next to the On Change property, then click the Build button (...). Enter the following code:

Private Sub cboEndDate_Change() Dim endDate As Date endDate = Me.cboEndDate.Value With Me

.txt1 = Format(endDate - 6, .txt2 = Format(endDate - 5, .txt3 = Format(endDate - 4, .txt4 = Format(endDate - 3, .txt5 = Format(endDate - 2, .txt6 = Format(endDate - 1, .txt7 = Format(endDate - 0, End With End Sub

7. Choose Insert | Module to add a new standard module. In the properties sheet, change the Name property of Modulel to TimeSheetProc.

8. Enter the following function procedure in the TimeSheetProc module:

Function ListEndDates(fld As Control, id As Variant, _

row As Variant, col As Variant, code As Variant) As Variant Dim intOffset As Integer

Select Case code

Case acLBInitialize

ListEndDates = True Case acLBOpen

ListEndDates = Timer Case acLBGetRowCount ListEndDates = 11 Case acLBGetColumnCount

ListEndDates = 1 Case acLBGetColumnWidth

ListEndDates = -1 Case acLBGetValue

' days till ending date intOffset = Abs((8 - Weekday(Now)) Mod 7)

' start 5 weeks prior to current week ending date ' (7 days * 5 weeks = 35 days before next ending date) ' and show 11 dates

ListEndDates = Format(((Now() + intOffset) - 35) _ + 7 * row, "MM/DD/YYYY")

End Select End Function

9. In the ListEndDates procedure, click anywhere on the line containing the following statement:

ListEndDates = Format(((Now() + intOffset) - 35) + 7 * row, "MM/DD/YYYY")

"mm/dd") "mm/dd") "mm/dd") "mm/dd") "mm/dd") "mm/dd") "mm/dd")

Introduction to Access 2003 VBA Programming

10. Press F9 (or choose Debug | Toggle Breakpoint) to set a breakpoint on the line where the cursor is located.

When you set the breakpoint, Visual Basic displays a red dot in the margin. At the same time, the line that has the breakpoint will change to white text on red (see Figure 9-4). The color of the breakpoint can be changed on the Editor Format tab in the Options dialog box (Tools menu).

Another way of setting a breakpoint is to click in the margin indicator to the left of the line on which you want to stop the procedure.

Microsoft Visual Basic Acc2003 ChapO? [TimeSheetProc (Code)]

i- mm

ï File Edt View Insert Debug Run Tools ftdd-Ins Window Help

m Type a question Ft* help - „ S X

[email protected],îï- A i *> >-U-J WJ^S si

mi 1

!.. jfjëll -ïju. ; l 1

Prolect - CuslomProjetlS _ 1

Jgj CustomPro}ect9_i (Acc2003_Clwp09)

E Nficrosoft Of Fice Access Class Objects

SI FormJrmTimeSheet E SI Modiies Breaks conditional tij TimeSheetProc Traps j(General)

ListEndDates

Option Compare Database

Prolect - CuslomProjetlS _ 1

Jgj CustomPro}ect9_i (Acc2003_Clwp09)

E Nficrosoft Of Fice Access Class Objects

SI FormJrmTimeSheet E SI Modiies Breaks conditional tij TimeSheetProc Traps

Properties - TimeSheetProc

TimeSheetProc ModJe Alphabetic j Categorized

Properties - TimeSheetProc

TimeSheetProc ModJe Alphabetic j Categorized

j(General)

ListEndDates

Option Compare Database

Function Li stEnd Dates (fid As Control, id As Variant, _ row As Variant, col As Variant, code As Variant) _ As Vanant

Dim intOffset As Integer Select Case code Case acLBInitiabze

LislEndDates = True Case acLBOpen

LislEndDates = Timer Case aeLBGetRowCount

ListEndDates = 11 Case acLBGetColumnCount

ListEndDates = 1 Case acLBGet ColumnWidth

LislEndDates = -t Case acLBGetValue

"days till ending date intOffset = Abs((8 - Weekday (Now)) Mod 7)

start 5 weeks prior to current week ending date " (7 days * 5 weeks = 35 days before next ending date) _' and show 11 dates_^^

ListEndDeles - FunnaifflNiiwO + ¡I'tOITssi + 7* row, ■MMiLlDiYVYY"

End Select End Function : 4 i I

Figure 9-4: The line of code where the breakpoint is set is displayed in the color specified on the Editor Format tab in the Options dialog box.

11. Switch to the Microsoft Access Application window and open the form frmTimeSheet that you prepared earlier in this project.

When you open the form, Visual Basic will call the ListEndDates function to fill the combo box, executing all the statements until it encounters the breakpoint you set in step 10 above. Once the breakpoint is reached, the code is suspended and the screen displays the Code window (Figure 9-5) in break mode (notice the word "break" surrounded by the square brackets in the Code window's title bar). Visual Basic displays a yellow arrow in the margin to the left of the statement at which the procedure was suspended. At the same time, the statement appears inside a box with a yellow background. The arrow and the box indicate the current statement, or the statement that is about to be executed. If the current statement also contains a

Part I

breakpoint, the margin displays both indicators overlapping one another (the circle and the arrow).

Margin Indicator Bar Vba

Figure 9-5: When Visual Basic encounters a breakpoint, it displays the Code window. A yellow arrow appears in the margin to the left of the statement at which the procedure was suspended. Because the current statement also contains a breakpoint (indicated by a red circle), the margin displays both indicators overlapping one another (the circle and the arrow).

Figure 9-5: When Visual Basic encounters a breakpoint, it displays the Code window. A yellow arrow appears in the margin to the left of the statement at which the procedure was suspended. Because the current statement also contains a breakpoint (indicated by a red circle), the margin displays both indicators overlapping one another (the circle and the arrow).

12. Finish running your procedure by pressing F5 to continue without stopping, or press F8 to execute the procedure line by line.

When you step through your procedure code line by line by pressing F8, you can use the Immediate window to further test your procedure (see the section titled "Using the Immediate Window in Break Mode"). To learn more about stepping through a procedure, refer to the section titled "Stepping Through VBA Procedures" later in this chapter.

Visual Basic allows you to set any number of breakpoints in a procedure. This way you can suspend and continue the execution of your procedure as you please. You can press F5 to quickly move between the breakpoints. You can analyze the code of your procedure and check the values of variables while code execution is suspended. You can also perform various tests by typing statements in the Immediate window.

Consider setting a breakpoint if you suspect that your procedure never executes a certain block of code.

Introduction to Access 2003 VBA Programming

Removing Breakpoints

When you finish running the procedure in which you had set breakpoints, Visual Basic does not automatically remove them. To remove the breakpoint, choose Debug | Clear All Breakpoints or press Ctrl+Shift+F9. All the breakpoints are removed. If you had set several breakpoints in a given procedure and would like to remove only some of them, click on the line containing the breakpoint you want to remove and press F9 (or choose Debug | Clear Breakpoint). You should clear the breakpoints when they are no longer needed. The breakpoints are automatically removed when you exit Microsoft Access.

0 0

Post a comment