Using VBA to Determine Group Properties

You might not realize that a group's header and footer have corresponding events, like other report sections, and that you can use these events to modify reports on the fly even adding new sections as needed.

Referring to Report Components in Code

Whether working with sections or groups, you need to know how to reference the appropriate area or level. To reference a report section, use the report's name or number. For instance, you can refer to a report's Detail section using the following syntax:

 Reports(reportname).Detail 

where reportname is a string that identifies the report by name. Detail is the actual name of the section. Or, you can use the following form:

 Reports(reportname).Section(0) 

or even

 Reports(reportname).Section(acDetail) 

When referencing group levels, use the same form as sections:

 Reports(reportname).GroupLevel(index).property 

A group level is actually an object, so you can use an object variable as follows:

 Dim glGroup As GroupLevel Set glGroup = Reports("BillingReport").GroupLevel(0) 

CAUTION

When referencing report sections and group levels, you must always explicitly identify the property being read or set. Neither report sections nor group levels offer a default property.

The GroupLevel object has a number of properties that you should already be familiar with if you've spent anytime building reports in Access:

• GroupFooter

• GroupHeader

• GroupInterval

• GroupOn

• KeepTogether

• SortOrder

• ControlSource

Table 14.4 lists the individual property settings for each of these properties.

Table 14.4. Grouping Property Settings

Property	Possible Settings
GroupFooter and GroupHeader	True or False
GroupOn	Each Value = 0 Prefix Characters = 1 Year = 2 Qtr = 3 Month = 4 Week = 5 Day = 6 Hour = 7 Minute = 8 Interval = 9
KeepTogether	No = 0 Whole Group = 1 With First Detail = 2
SortOrder	Ascending = False Descending = True

CAUTION

You can create new groups only in Design view, but you can set most group properties from the report's Open event.

CASE STUDY: Adding a Daily Report

TimeTrack only has one report and it deals with billing. Suppose, for example, that your developers need to see a revised schedule occasionally. A report is probably the best way to present this information, so you need to give users an easy way to print a schedule grouped by the day, the current week, and even the current month. Doing so requires a form that allows the users to choose one of the three possible schedules the Switchboard form is the best place for these options and a new report.

1. Open the Switchboard form in Design view and insert three command buttons. Name them cmdDaily, cmdWeekly, and cmdMonthly and enter the appropriate Caption properties.
   
   

2. In the form's module, enter the following event procedures:
   
   

   
   

    Private Sub cmdDaily_Click()  Call GenerateSchedule(6) End Sub Private Sub cmdMonthly_Click()  Call GenerateSchedule(4) End Sub Private Sub cmdWeekly_Click()  Call GenerateSchedule(5) End Sub Sub GenerateSchedule(rpt As String)  DoCmd.OpenReport "Schedule", acViewPreview, , , , rpt End Sub 

   
   

3. Each call to GenerateSchedule passes a value, which represents a GroupOn property setting (see Table 14.4). The OpenReport method passes that value to the report using the OpenArgs method. The report doesn't exist yet, but that's okay.
   
   

3. Save and close the Switchboard form shown in Figure 14.8.
   
   

   Figure 14.8. Add three command buttons to the switchboard.

   

   
   

4. Create the new query named Schedule shown in Figure 14.9.
   
   

   Figure 14.9. Base the scheduling report on this query.

   
   
   

5. Save and close the query.
   
   

6. Use the AutoReports: Tabular wizard to create a tabular report based on the Schedule query, and name it Schedule.
   
   

7. Open the new report in Design view and click the Sorting and Grouping tool. Using Figure 14.10 as a guide, set the appropriate group properties. Grouping the schedule by each day (shown as Each Value in the user interface) will be the report's default group. The first and third rows' properties are all defaults. Set the second row's Group Header property to Yes so you can visually tell where one group ends and the next begins.
   
   

   Figure 14.10. Setting the initial grouping properties.

   

   
   

8. In the report's module, enter the following code:
   
   

   
   

    Private Sub Report_Open(Cancel As Integer)  If IsNull(Me.OpenArgs) Then   Exit Sub  End If  Me.GroupLevel(1).GroupOn = CInt(Me.OpenArgs) End Sub 

   
   

9. Save and close the report.
   
   

Open the switchboard and click the new Daily Schedule button to view the schedule grouped by the EstimatedEndDate field, as shown in Figure 14.11. (The figure shows only a portion of the report.) The report is grouped by each value (that is, each day gets its own group), which is the same as the default you set earlier. It isn't even necessary to pass the daily value as the sample does, but it doesn't hurt to include it in case you modify the first group level, which is currently based on the Client field (0).

Figure 14.11. This report groups on the EstimatedEndDate field by the day.

Close the report and click the Monthly Schedule button to see the report shown in Figure 14.12. This report displays the same records as the first, but groups the EstimatedEndDate values by the month. As you can see, the records in December are grouped differently for each report. Because of the current dates, the weekly schedule looks the same as the daily schedule, but that won't always be the case.

Figure 14.12. Grouping projects by the month.