Special Formatting for Reports

You can use several techniques to apply special formatting to reports: conditional formatting done both from the interface and from VBA code, and watermarks.

Conditional Formatting of Report Controls and Sections

You can use conditional formatting with both form and report controls, but it is more useful on reports. One typical use is to emphasize records that have a Currency value over (or under) a certain number. As an example, let’s use conditional formatting on a Sales Results report to make records where the sales total per salesperson is over $300 bold, and those with sales under $25 italicized. Other records are in plain text. In design view, select the txtSumTotalPriceGF textbox in the Salesperson footer, and select Format|Conditional Formatting from the menu. Set the criteria for the formatting in the Conditional Formatting dialog, as shown in Figure 6.28.

Figure 6.28

With reports you also have another option: formatting controls or making sections visible or invisible from the Format event of a report section, depending on the values of certain fields. Version 2 of the Sales Results report shown in Figure 6.29 uses an event procedure on the Salesperson group footer’s Format event to make records where the sales total per salesperson is over $300 bold, and those with sales under $25 italicized. Other records are in plain text. The code uses an If . . . ElseIf . . . Else . . . End If structure to cover all the cases. This is necessary, because otherwise the formatting applied to one row will be carried over to the next record, unless that record is specifically formatted. (Version 1 of this report, using conditional formatting, looks exactly the same.)

If you want to make a section visible or invisible, you have to use code, as conditional formatting is only available for controls. To make an entire report section visible or invisible, reference the section by its named constant, using the following syntax:

Me.Section(acDetail).Visible

The named constants for various report sections are listed in the table below:

Named Constant	Report Section
acDetail	Detail
acHeader	Report Header
acFooter	Report Footer
acPageHeader	Page Header
acPageFooter	Page Footer
acGroupLevel1Header	Group 1 Header
acGroupLevel1Footer	Group 1 Footer
acGroupLevel2Header	Group 2 Header
acGroupLevel2Footer	Group 2 Footer

(and so forth for further group headers and footers).

Figure 6.29

 Private Sub SalespersonFooter_Format(Cancel As Integer, FormatCount As Integer) On Error GoTo ErrorHandler        Dim curSales As Currency        curSales = Nz(Me![txtSumTotalPriceGF])    If curSales > 300 Then       Me![txtSalespersonGF].FontWeight = 700       Me![txtSumTotalPriceGF].FontWeight = 700       Me![txtSalespersonGF].FontItalic = False       Me![txtSumTotalPriceGF].FontItalic = False    ElseIf curSales < 25 Then       Me![txtSalespersonGF].FontWeight = 500       Me![txtSumTotalPriceGF].FontWeight = 500       Me![txtSalespersonGF].FontItalic = True       Me![txtSumTotalPriceGF].FontItalic = True    Else       Me![txtSalespersonGF].FontWeight = 500       Me![txtSumTotalPriceGF].FontWeight = 500       Me![txtSalespersonGF].FontItalic = False       Me![txtSumTotalPriceGF].FontItalic = False    End If     ErrorHandlerExit:    Exit Sub ErrorHandler:    MsgBox "Error No: " & Err.Number & "; Description: " &        Err.Description    Resume ErrorHandlerExit End Sub 

Another type of special formatting uses the row count, rather than the value of a field. If you want to shade alternate rows of a financial report, for example, you can use the following code in the Detail section’s Format event procedure:

 Private Sub Detail1_Format(Cancel As Integer, FormatCount As Integer) On Error GoTo ErrorHandler    Const vbLightGrey = 12632256    If Me.CurrentRecord Mod 2 = 0 Then       Me.Section(acDetail).BackColor = vbLightGrey    Else       Me.Section(acDetail).BackColor = vbWhite    End If        ErrorHandlerExit:    Exit Sub ErrorHandler:    MsgBox "Error No: " & Err.Number & "; Description: " &        Err.Description    Resume ErrorHandlerExit End Sub 

The code uses the Mod operator to determine whether the current record (row) is even or not, and makes even rows gray and odd rows white. The shaded report is shown in Figure 6.30.

Figure 6.30

When creating a report with alternate line shading, make sure that the textboxes in the Detail section have their BackStyle property set to Transparent so that the gray section back color can show through them.

Placing a Watermark on a Report

To print an image as a report background (sometimes called a watermark), select the image for the report’s Picture property, using the MS Picture Builder to browse for it. To keep the report readable, the image should be light in tone. Some versions of Office include a Confidential.wmf or Confidential.bmp file you can use as a report watermark, but you don’t have to limit yourself to that image: whatever text you want for use as a report watermark can be created using WordArt and Paint. Here is how I made a diagonal “Company Confidential” watermark for use on reports.

1. Open Word.

2. Select Insert|Picture|Word Art.

3. I like the second image down in the left column from the WordArt Gallery (shown in Figure 6.31) for watermarks (of course, you can select any style you prefer).

   
   Figure 6.31

4. Enter “Company Confidential” (or other text) as the WordArt text.

5. To make the WordArt image slanted, for diagonal display on the report, select it and click the Format WordArt button on the WordArt toolbar.

6. In the Format WordArt dialog, enter 300 in the Rotation box. Figure 6.32 shows the rotated WordArt, with its properties sheet and the WordArt toolbar.

   
   Figure 6.32

7. Press Alt-PrintScreen to capture the WordArt image on the clipboard.

8. Open Paint from the Accessories program group (or another image editing program of your choice), and paste in the image with Ctrl-V.

9. Use the Paint Select tool to outline the WordArt image, as shown in Figure 6.33.

   
   Figure 6.33

10. Save the cropped image as Company Confidential Watermark.bmp.

11. Back in Access, open a report in Design view and set the BackStyle property of all the textboxes and labels (except shaded ones) to Transparent.

12. Select the new Company Confidential Watermark.bmp image for the Picture property of a report. Figure 6.34 shows a version of the Orders grouped report with this watermark.

    
    Figure 6.34