Changing Background Color for each 5 rows

[Ken Kazinski]

How do you set the background color for each 5 rows of data in a group?

I think I might be able to use the on print event, but that did not seem to
work correctly.

Thanks in advance.

 

[Allen Browne]

You want 5 rows with a white background, and then 5 rows with a grey
background?

1. Add a text box to the report, and give it these properties:
Control Source =1
Running Sum Over All
Format General Number
Name txtCount

2. Set the On Format property of the Detail section to:
[Event Procedure]

3. Click the Build button (...) beside this property.
Between the Private Sub... and End Sub lines enter something like this:

Dim lngColor As Long
Const lngcNormal = &HFFFFFF
Const lngcHighlight = &HCCCCCC
Const lngcRowSwap As Long = 5

If (((Me.txtCount - 1&) \ lngcRowSwap) Mod 2 = 0 Then
lngColor = lngcNormal
Else
lngColor = lngcHighlight
End If

With Me.Section(acDetail)
If .BackColor <> lngColor Then
.BackColor = lngColor
End If
End With

You may also need to change the BackStyle property of the text boxes to
transparent.

Adjust the colors to what you need.

 

[Ken]

Hi Allen,

Thank you - looks like it will work. Is there a way to count the rows on
the detail without creating a field?

Thanks.

You want 5 rows with a white background, and then 5 rows with a grey
background?

1. Add a text box to the report, and give it these properties:
Control Source =1
Running Sum Over All
Format General Number
Name txtCount

2. Set the On Format property of the Detail section to:
[Event Procedure]

3. Click the Build button (...) beside this property.
Between the Private Sub... and End Sub lines enter something like this:

Dim lngColor As Long
Const lngcNormal = &HFFFFFF
Const lngcHighlight = &HCCCCCC
Const lngcRowSwap As Long = 5

If (((Me.txtCount - 1&) \ lngcRowSwap) Mod 2 = 0 Then
lngColor = lngcNormal
Else
lngColor = lngcHighlight
End If

With Me.Section(acDetail)
If .BackColor <> lngColor Then
.BackColor = lngColor
End If
End With

You may also need to change the BackStyle property of the text boxes to
transparent.

Adjust the colors to what you need.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

How do you set the background color for each 5 rows of data in a group?

I think I might be able to use the on print event, but that did not seem
to
work correctly.

Thanks in advance.

 

[Allen Browne]

Set the Visible property of the text box to No if you don't wish to see it.

The text box accumulating the value is the only reliable way to get a count
that spreads over multiple pages. If you use code in the report events to
assign a value to a variable, the events may not fire for pages that are not
previewed/printed, so the numbers are wrong.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen,

Thank you - looks like it will work. Is there a way to count the rows on
the detail without creating a field?

Thanks.

You want 5 rows with a white background, and then 5 rows with a grey
background?

1. Add a text box to the report, and give it these properties:
Control Source =1
Running Sum Over All
Format General Number
Name txtCount

2. Set the On Format property of the Detail section to:
[Event Procedure]

3. Click the Build button (...) beside this property.
Between the Private Sub... and End Sub lines enter something like this:

Dim lngColor As Long
Const lngcNormal = &HFFFFFF
Const lngcHighlight = &HCCCCCC
Const lngcRowSwap As Long = 5

If (((Me.txtCount - 1&) \ lngcRowSwap) Mod 2 = 0 Then
lngColor = lngcNormal
Else
lngColor = lngcHighlight
End If

With Me.Section(acDetail)
If .BackColor <> lngColor Then
.BackColor = lngColor
End If
End With

You may also need to change the BackStyle property of the text boxes to
transparent.

Adjust the colors to what you need.

How do you set the background color for each 5 rows of data in a group?

I think I might be able to use the on print event, but that did not
seem
to
work correctly.

Thanks in advance.

 

[Ken]

Hi Allen,

I tried the code you provided and I am not getting the results I expected.

I took the code and tried to modifiy it. My goal was to have the 5th row of
each group highlighted.

I am curious what the "- 1&) \" does?

Also the code does not change the color of any of the rows.

 

[Allen Browne]

The backslash does integer division (losing the remainder.)

The counter starts at 1. You need it to to start from zero, hence -1.

The ampersand is optional. It just specifies that the 1 is a Long.