May 27, 2011, 10:30 p.m.
posted by osi
Shade Alternating Lines on a Report
A quick way to make reports easier to read is to shade every other line. Although no direct property or method provides this feature, you can achieve the look with a little planning. To accomplish this, use an unbound text box to keep an incremental value. As line items are processed, the incrementing value toggles between even and odd. You can then use this toggle's values to your advantage.
The background color property of the report's details section is changed, depending on the value of the incremental running sum. When the value is odd, one color is applied. When the value is even, another color is applied.
You have to set a few properties for this to work:
In the report's details section, an unbound text box is included. Its Control Source property is set to =1.Its Visible property is set to No. Set its name to txTRunningSum.
In the details section's Format event, place this code:
Dim even_odd As Integer Me.Detail.BackColor = vbWhite even_odd = Me.txtRunningSum Mod 2 If even_odd = 0 Then Me.Detail.BackColor = vbYellow End If
You use the Mod operator to determine whether the current running sum value is even or odd. Mod returns the remainder of a division operation. When an even number is divided by 2, the remainder is 0. The even_odd variable holds the result of the Mod operation.
The routine starts out by defaulting the background color to white. If the even_odd variable isn't 0, the background color is changed to yellow.
Figure shows how the report looks when run.
Hacking the Hack
A couple of alternatives are available. If, for example, you have to shade every third line, you can test whether the running sum is a multiple of 3. Any multiple of 3 divided by 3 has no remainder.
Alternatively, you can use the RGB function to control the color. RGB is an acronym for red, green, blue. The function works by blending the three colors, each as a number between 0 and 255. Look up the RGB function in the Access Help system; it's a great function to get familiar with. To use it in this hack, just change the BackColor property, like this:
A report with alternate row shading
Me.Detail.BackColor = RGB(200, 200, 200)
You will have to experiment with different settings, but here's a guide you can follow:
Setting all three RGB argument functions to 0 creates black.
Setting all three RGB argument functions to 255 creates white.
All other colors are available by applying varying values to the arguments.