HandsOn Writing the Form BeforeQuery and Form Query Event Procedures

1. In the Visual Basic Editor window, double-click Form_Sales Analysis Subform 1.

2. The Code window shows the Form_OnConnect event procedure you prepared in Hands-On 24-21. Enter the following Form_BeforeQuery and Form_Query event procedures below the last procedure code.

Private Sub Form_BeforeQuery() Dim strColor As String strColor = InputBox("Type 1 for Red, 2 for Yellow, " & _

"or 3 for Green", "Specify Background Color for " & _ "Detail Rows")

Select Case strColor Case 1

strColor = "Red" Case 2

strColor = "Yellow" Case 3

strColor = "Green" Case Else strColor = "White" End Select

DoCmd.Hourglass True

Me.PivotTable.ActiveView.DataAxis.FieldSets(0). _

Fields(0).DetailBackColor = strColor Debug.Print "Executing the BeforeQuery event" Debug.Print "(Changed detail color to: " & strColor & ")"

End Sub

Private Sub Form_Query()

Debug.Print "Executing the Query event" DoCmd.Hourglass False

Debug.Print "(Turned off the hourglass mouse pointer)" End Sub

3. To test the above event procedures, double-click the Sales Analysis Subform 1 form in the Database window.

4. You should first see the message as coded in the Form_OnConnect event procedure (see Hands-On 24-21). Click OK to the message to open the form. You should then be prompted for the color of the detail rows as coded in the Form_BeforeQuery event procedure. Type your selection and

Part IV

click OK. The hourglass should disappear when the form loading process is completed.

5. Open the Visual Basic Editor window and examine the Immediate window.

Let's examine the two event procedures that you wrote in this hands-on. Notice that to display the hourglass pointer, we used the statement DoCmd.Hourglass True in the Form_BeforeQuery event procedure. To turn off the hourglass, we put the statement DoCmd.Hourglass False in the Form_Query event procedure.

Also, notice the following statement that sets the background color for the PivotTable detail rows in the Form_BeforeQuery event procedure:

Me.PivotTable.ActiveView.DataAxis.FieldSets(0).Fields(0).DetailBackColor = strColor

In the above statement, the PivotTable property is used to access the PivotTable object exposed by a form. The ActiveView property represents the layout of the PivotTable report. The DataAxis property refers to one of the four areas of the PivotTable where you can drop fields of data. The DataAxis property contains the Totals and Details fields, which are displayed at the intersection of each row and column. Other areas of a PivotTable are used to display row fields (RowAxis), column fields (ColumnAxis), and filter fields (FilterAxis).

In addition to axes, a PivotTable contains a field list that shows which fields are available for use in the PivotTable report based on the form's RecordSource. The field list displays expandable nodes listing fields available on the form (see Figure 24-2).

The PivotTable field list contains two types of nodes: Totals and Fieldsets. A Totals node near the top of the field list shows all aggregate functions, or calculations used in the PivotTable report. Other expandable nodes in the PivotTable field list are known as Fieldsets, and can include one or more fields.

To manipulate a particular PivotField object in the PivotTable report via VBA code you need to access the Fieldset object in the Fieldsets collection of the ActiveView object. Each Fieldset object (a top-level node in the field list) contains a Fields collection, which contains PivotField objects. If you look at the expanded nodes in Figure 24-2 you will notice the Country Fieldset with one PivotField (Country), the Last Name Fieldset with a LastName PivotField, and the Shipped Date By Week Fieldset with six PivotField objects representing different time intervals (Years, Weeks, Days, Hours, Minutes, and Seconds).

Event Programming in Forms and Reports

IS Pli/al lable

PivotToble Field List

Crag items tp the Ph'OtTgblc list

Yeais "

El 199S




BQtri 0Qtr2 HQIrS 0Q!r4 Total :

BQIrl :

BQlr2 : Total Grand Total

Last Namo


Total Sales $3,058:92 El 4.EDS. 33 117,66/.ID 53,237.12 3B,G*I7.G7 (12,975.00 S8.S72.S7 831,433.16 ¡19.4B1.09 $210.00 510,691.89 S68,7S2.25

Callahan Total Safes (1 I D40.00 i0.12O.7O 1*19,1 tit! ./[I $19,992.23 $8pBG.72 $9,649.90 S1BJ62S.0B 156.954.1)2 J27.Q30.S5 120097.10 (17,727.95 $123,842.67

DayoliD Tot a Sales 18.092.« 321.969.28 S30.E61.76 317,835.32 S15.925.5S 932,394.91 S29.044.O7 395,850,36 326.351 00 334,214.23 S6Ü,565.23 S 187,277.35


Total Sales Country: [3 Country; Last Name: Pi] Last Name: Frst Name: 5hlppedDate:

0 Days

3 Minutes m Seconds

OrdefID: SaieAmount:

Grand Total

Total Sales 133.105.20 177,442.59 1110,637.79 $60,621.69 175.526.82 $36,710.4 B 190.775.77 1339.645.70 1101,016-99 1127,460.79 1203,477.70 1730,751.33

Figure 24-2: PivotTable report and its field list.

0 0

Post a comment