Question: How do I create a report that shades alternating rows in the Detail section?
You design and create your report as you normally would, by choosing a record source and placing controls and fields in the appropriate report sections. You then place some VBA code in the Format event of the report's Detail section. The code acts like a toggle switch, automatically switching the Detail section's background color between the default color and the alternate, shaded color. You'll find the required code in a following section. Before I discuss the code, let's briefly review the report's Detail section and the report's Format event.
Introducing the Detail section and the Format event
Access divides a report into sections, and then processes each section in turn, according to its type. The Report Header section, for example, is printed just once — at the beginning of the report. The Page Header section, on the other hand, is printed at the top of every single page. Because reports often have many pages, the Page Header section tends to be called (that is, displayed) often. Normally, the most often called section is the Detail section, because it is called once for every row in the report's record source. It's here that you place the fields and controls that make up the main body of your report. The seven report sections of an Access report are Report Header, Page Header, Group Header, Detail, Group Footer, Page Footer, and Report Footer. For more information about the report sections, see "Understand the report sections" in the article
Create a simple report.
When you run a report, Access processes the report sections, many of them repeatedly. While Access is processing report sections, it also triggers a series of events that can be responded to by VBA code. For example, a section's Format event occurs after Access has selected the data to appear in a section, but before the data is actually formatted or printed. You can therefore use the Format event to intervene when you want to change a section's layout or appearance on a page.
In this instance, we want to change the background color of the Detail section. If the background is currently white, we want to change it to the shaded color. If it's currently the shaded color, we want to change it to white. In this way, you cause the background color to alternate. To change a section's background color, you set the BackColor property of that section.
Set the BackColor property
Most controls, fields, and objects in Access have an associated series of properties. A property is a named value that defines some characteristic of the object — such as its position, size, or whether it is visible or not. The BackColor property makes it possible to specify the background color of a section or control. You set the property to a numeric value that corresponds to the color that you want. For instance, the color value for White is 16777215, and the color value for Gray is 12632256. Normally, when you want a fixed background color for a section, you set the BackColor property by using the section's property sheet, as shown in the following graphic. First, you select the section by clicking its top border. Then, you click Properties on the View menu to reveal the section property sheet. (Note that you can also display the property sheet by double-clicking the top border of the section.) Finally, in the property sheet, you change the BackColor property.
Click in the Back Color box to change the background color.
To cause the background color to alternate from row to row, however, you can place the following code in the Detail section's Format event — see the following section,
Create the Code, for step-by-step instructions on how to create the code. The code alternates the BackColor property programmatically.Option Compare Database
Option Explicit
Private shadeNextRow As Boolean
Const shadedColor = 12632256
' Const shadedColor = 15726583 ' alternative shade colors
' Const shadedColor = 14078404
' Const shadedColor = 13356495
' Const shadedColor = 14281974
Const normalColor = 16777215
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
On Error GoTo Detail_Format_Error
' Choose a color based on the shadeNextRow value
If shadeNextRow = True Then
Me.Section(acDetail).BackColor = shadedColor
Else
Me.Section(acDetail).BackColor = normalColor
End If
' Switch the color for the next row
shadeNextRow = Not shadeNextRow
Detail_Format_Exit:
Exit Sub
Detail_Format_Error:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume Detail_Format_Exit
End Sub
Let's take a look at the code.
First, we create a variable named shadeNextRow that we set to True when we want a gray background, and to False when we do not. Then, we create two constants to hold the color values. The first constant, shadedColor, holds the color value for the gray background. For a different, lighter shade of gray, you can change this value to 15726583. (You might also want to consider 14078404, 13356495, or 14281974.) The second constant, normalColor, holds the color value for a white background.
Next comes the code inside the Detail_Format procedure. When Access calls the Detail section's Format event, as it does for every row in the report's record source, this code is executed, and the BackColor property of the section is set to the shadedColor value if the value of the shadeNextRow variable is True, or to the normalColor value if the value of shadeNextRow is False. The Immediate If function (
IIf) makes this decision for us. The function returns the value of its second argument if the first argument is true. Otherwise, the function returns the third argument. Thus, the function returns the value shadedColor when shadeNextRow is True, and normalColor when shadeNextRow is False, which meets our needs perfectly.
Then, the shadeNextRow value is reversed by using the Not operator. If the value of shadeNextRow is True, it becomes False. If it is False, it becomes True. This sets up the value for the next time that Access calls the Format event. The result is that the background color toggles back and forth between gray and white.
Create the code
To add the code to a report, start Access, and then perform the steps in the following expandable section:
Create the code
To add the code to a report, start Access, and then perform the steps in the following expandable section:
Step-by-step: Adding code to shade alternate rows in a report
To open the report in Design view
In the Database window, under Objects, click Reports.
Click the report, and then click Design in the Database window.
To set the Detail section's Format property
Double-click the border of the Detail section.
The property sheet appears.
Make sure that the title bar of the property sheet includes "Detail." If it does not, click the All tab, click the Name box, click the arrow that appears, and then click Detail in the list.
Click the All tab, click the On Format box, and then click the ellipsis button (...) that appears.
In the Choose Builder dialog box, click Code Builder, and then click OK.
Edit the module and the Detail_Format procedure so that it appears exactly as shown in the preceding section.
On the File menu, click Close and Return to Microsoft Access.
Close the property sheet.
How to determine the numeric color value
You might not be fond of the color gray. To use another color for the shaded rows, change the value for the shadedColor constant to the color value that you prefer. To determine the value to use, take the following steps:
To determine the numeric color value
Click the top border of the Detail section to select the section. Then, on the View menu, click Properties to open the property sheet for the Detail section.
Click the Back Color box, and then click the ellipsis button (...) that appears.
In the Color palette, click the color that you want, and then click OK.
Note the color value that appears in the Back Color box. This is the value to assign to the shadedColor constant. To copy the value to the clipboard, select and right-click the value, and then click Copy on the shortcut menu.
Finally, before moving the insertion point from the Back Color box, press ESC to restore the original color value.
Close the property sheet.
Set the BackStyle property of your controls
The technique discussed here sets the background color of the entire Detail section. This works well, whether or not the section grows to accommodate controls that grow to fit their contents. The controls you place in the Detail section, however, must have their BackStyle property set to Transparent in order for this technique to work properly. Setting the BackStyle property of the control to Transparent lets the section's background color show through. Otherwise, for example, if a text box control has its BackStyle property set to Normal, the text box's background color will obscure the section's background color.
To set the BackStyle property of a control
Right-click the control, and then click Properties on the shortcut menu.
Click the Format tab.
To change the value of the BackStyle property, click in the Back Style box, and then select Transparent in the drop-down menu.
Close the property sheet.
On the File menu, click Save, and then close the report.
Set the starting value
It's good practice to set the shadeNextRow variable's True/False value at the beginning of your report, so that it starts with a known value. You can do this by placing some VBA code in the Report Header section's Format event. Set the shadeNextRow value to True if you want the first detail row to have the shaded background color, or to False if you want the first detail row to have the default background color.
To place code in the Report Header's Format event
Click the top border of the Report Header section to select the section. Then, on the View menu, click Properties to open the property sheet for the section.
Click the All tab, click the On Format box, and then click the ellipsis button (...) that appears.
In the Choose Builder dialog box, click Code Builder, and then click OK.
Edit the ReportHeader_Format procedure so that it appears like this:Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As Integer)
shadeNextRow = False
End Sub
On the File menu, click Close and Return to Microsoft Access.
Close the property sheet.
How to reset the starting value for each group or page
You might want to reset the background color at the start of each group or page. That way, each group or page starts with the first record being either shaded or not shaded — your preference. You can reset the background color by placing VBA code in the Format event of either the Group Header section or the Page Header section. If you choose to place code in both the Group Header and Page Header sections, remember to take into account the order in which the sections are processed, or you might be surprised by the results. For instance, you can set the RepeatSection property of the Group Header section to True to repeat the Group Header section at the top of a page when a group continues from a previous page. If you set the shadeNextRow value to True in the Page Header section and to False in the Group Header section — and if the Group Header carries over from a previous page — then your first detail row will not be shaded, because the Group Header section is repeated, sets the value to False, and comes after the Page Header section.
To reset the starting background color for each group or page
Click the top border of the Group Header or Page Header section to select the section. Then, on the View menu, click Properties to open the property sheet for the section.
Click the All tab, click the On Format box, and then click the ellipsis button (...) that appears.
In the Choose Builder dialog box, click Code Builder, and then click OK.
Do either or both of the following:
To reset the starting background color at the beginning of every group, edit the GroupHeader_Format procedure so that it appears as shown here:Private Sub GroupHeader_Format(Cancel As Integer, FormatCount As Integer)
shadeNextRow = False
End Sub
To reset the starting background color at the beginning of every page, edit the PageHeader_Format procedure so that it appears as shown here:Private Sub PageHeader_Format(Cancel As Integer, FormatCount As Integer)
shadeNextRow = False
End Sub
On the File menu, click Close and Return to Microsoft Access.
Close the property sheet
