Making a Report Dynamic

Three report section events—Format, Retreat, and Print—let you build dynamic formatting and content into a report. Other report events that can help you build smart reports include Page, NoData, Close, and Open. These events can also help you manage the application's behavior before, during, and after the opening of a form. You can use combinations of report events to create report formatting and special effects.

You use the Open event to programmatically set properties for reports and their controls. This is the first event for a report. If your application can have more than one report open at the same time, you can use the Activate and Deactivate events to monitor the flow of focus into and away from a report. Use the Close event to perform special actions just before a report closes, such as opening a form or presenting a message box.

You use the NoData event to detect a report that has no data in its record source. This event occurs after Access formats a report for printing. Your application can use it to cancel a report that is about to print with no data. You can also program event procedures that prompt a user to make data available by entering records or by going to another record source for the report.

Formatting and Adding Content

The following samples format and add content to a report dynamically using the Print and Format events. Figure 6-14 shows a report that uses the Print event for three sections to add red rectangles around the Report Header section and the Page Footer section. Note the different thicknesses of the rectangle borders. The Detail section shows an oval around all monthly order totals greater than or equal to 30.

While the Report Header Print event occurs a single time per report and the Page Footer Print event occurs just once per page, the Detail section Print event occurs once for each row on a page. This means that the page in Figure 614 has 16 Detail section Print events. With each event, your application can examine control values for the current record. This means that you can selectively display ovals around some monthly order totals.

Figure 6-14. The Print event in this report draws rectangles around the Report Header and Page Footer sections. Another event selectively draws ovals around the monthly orders totals in the report.

The following three event procedures are the code behind the report in Figure 6-14. Your applications can apply the Line method (as in the Report Header and Page Footer event procedures) to draw a rectangle around the report section. Four Single variables accept the top, left, width, and height values of the section. A Long variable accepts the color number for the rectangle (red in the sample). Just before invoking the Line method to draw the rectangle, the ReportHeader_Print procedure sets the line width to 25 pixels. Two pairs of coordinates denote points for the Line method. The value in lngColor specifies the line's color. The Line method's closing argument, B, instructs the method to draw a rectangle, or box, using the two coordinates as diagonally opposite end points.

 Private Sub ReportHeader_Print(Cancel As Integer, _     PrintCount As Integer) Dim sngTop As Single, sngLeft As Single Dim sngWidth As Single, sngHeight As Single Dim lngColor as Long 'Set top, left, width, & height.     sngTop = Me.ScaleTop     sngLeft = Me.ScaleLeft     sngWidth = Me.ScaleWidth     sngHeight = Me.ScaleHeight      'Set color.     lngColor = RGB(255, 0, 0) 'Draw line as a box.  Me.DrawWidth = 25     Me.Line (sngTop, sngLeft)-(sngWidth, sngHeight), lngColor, B End Sub Private Sub PageFooterSection_Print(Cancel As Integer, _     PrintCount As Integer) Dim sngTop As Single, sngLeft As Single Dim sngWidth As Single, sngHeight As Single Dim lngColor as Long      'Set top, left, width, & height.     sngTop = Me.ScaleTop     sngLeft = Me.ScaleLeft     sngWidth = Me.ScaleWidth     sngHeight = Me.ScaleHeight      'Set color.     lngColor = RGB(255, 0, 0) 'Draw line as a box.     Me.Line (sngTop, sngLeft)-(sngWidth, sngHeight), lngColor, B End Sub Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer) Dim sngHCtr As Single, sngVCtr As Single Dim sngRadius As Single 'Position and size circle.     sngHCtr = (Me.ScaleWidth / 2) - 3670     sngVCtr = (Me.ScaleHeight / 2) - 20     sngRadius = Me.ScaleHeight / 1.5 'Conditionally draw circle; last argument sets aspect ratio.     If Me.CountOfOrderID.Value >= 30 Then         Me.Circle (sngHCtr, sngVCtr), sngRadius, , , , 0.5     End If End Sub

The only difference between the ReportHeader_Print and PageFooter_Print procedures is the line setting the width of the rectangle's border. The Report Header section uses a width of 25 pixels, but the Page Footer section draws a rectangle with the default width of 1 pixel. Both procedures draw a rectangle on a layer in front of the standard report layer. You can tell this because the red border from the Line method appears over the background shading for the report title.

The Detail section event procedure relies on the Circle method to draw an oval around the order totals for each row in the Detail section. You must empirically determine horizontal and vertical centers as well as the radius of your circle. You use an aspect ratio argument in the Circle method to transform a circle to a long or narrow oval. Like the Line method, the Circle method draws its output on a layer in front of the standard report layer. Embedding the Circle method in an If...Then statement allows the procedure to draw the oval conditionally around some but not all Orders field values.

Summing Page Values

If your report requires the sum of the entries on a report page, you must program these page sums using event procedures because Access offers no built-in way to accomplish this task. The book's sample for this task uses a text box with its Running Sum property set to Over All. This causes the text box to sum its Control Source field over the whole report. Figure 6-15 on the next page shows the first and second pages of a report with the extra column for computing the running sum. The far right column appears for tutorial purposes, but in practice you set the Visible property for the control with the running sum to False.

Figure 6-15. This report uses two event procedures and a text box in the Detail section with its Running Sum property set to Over All to compute the sum of the orders on a page.

You can compute page sums with as few as two event procedures (shown below). The PageFooterSection_Format event procedure only requires two lines. First it copies the value for the control with the running sum (pagesum) set to lngCurrentRSum. Then it sets another text box in the Page Footer section (txtpagesum) to the difference between lngCurrentRSum and lngLastRSum. The value of lngLastRSum is initially 0. After every page finishes formatting, a procedure firing with the report's Page event copies the current value of lngCurrentRSum into lngLastRSum, so the difference between lngLastRSum and lngCurrentRSum in the Report_Page event procedure is the page sum for the current page.

 Public lngLastRSum As Long, lngCurrentRSum As Long Public lngPageRSum As Long Private Sub PageFooterSection_Format _     (Cancel As Integer, FormatCount As Integer)     lngCurrentRSum = Me.pagesum     Me.txtpagesum = lngCurrentRSum _ lngLastRSum End Sub Private Sub Report_Page()     lngLastRSum = lngCurrentRSum End Sub

Notice that the PageFooterSection_Format event procedure in the sample computes and displays page sums by writing a value into a text box within the Page Footer section. Print event procedures do not enable this kind of manipulation because the Print event fires after the report is already formatted. The Format event fires as your application is formatting the report.