You can use several techniques to apply special formatting to reports: conditional formatting done both from the interface and from VBA code, and watermarks.
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.
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.
Open Word.
Select Insert|Picture|Word Art.
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
Enter “Company Confidential” (or other text) as the WordArt text.
To make the WordArt image slanted, for diagonal display on the report, select it and click the Format WordArt button on the WordArt toolbar.
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
Press Alt-PrintScreen to capture the WordArt image on the clipboard.
Open Paint from the Accessories program group (or another image editing program of your choice), and paste in the image with Ctrl-V.
Use the Paint Select tool to outline the WordArt image, as shown in Figure 6.33.
Figure 6.33
Save the cropped image as Company Confidential Watermark.bmp.
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.
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