Shading alternate detail lines in a report (Access 2K)

Topic

New Reply

I would like to ba able to shade every second line in a report to make reading easier…does anyone know if this can be done.

Reply | Quote

Replies

Hi

Yes this can be done!

You can use a little bit of VBA to do this.

I have included a sample access 2K file, with a table and report that shades every second line.

You use two events of the Report:
-the On Open event of the report
-the On Format of the detail section of the report

In the On Open event of the report, set a module level variable to 0

The detail section of a report is ‘formatted’ once for each line of the report. This means that for every line of the report, we can make a bit of VBA run by using the ‘On Format’ event of the detail section of the report.

In the On Format event of the detail section, you need to:
-increment the Line Number variable by 1
-determine if the Line Number is odd or event

To determine if the line number is odd or even the mod operator is used. The mod operator returns any remainder of the difference between the division of two numbers. For example, 3 mod 2 = 1 (because 3 / 2 = 1 with 1 remaining). If we divide by 2 and there are no remainders (ie. line number divide by 2 has no remainders) then the line is even, else it must be odd.

We then use a if..then..else..end if statement to change the background colour to grey if the number is odd, or white if the line number is even.

There you have it, a report with alternate grey and white lines!

Hope this helps. Unzip the file and run the Acc 2K database, run the report and you will see the result.

Here is the VBA from the Report Module

Option Compare Database
Option Explicit
‘Declare the line number variable and make it available to all
‘ withing the report
Private lngLineNumber As Long

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
‘===========================================================’
‘ Author: Jayden MacRae ‘
‘ Created: 26/10/2001 ‘
‘ Purpose: To increment the line number by one each time ‘
‘ and then to determine if the line number is ‘
‘ odd or even and set the background colour ‘
‘ appropriately or not. ‘
‘===========================================================’

‘Increment line number by one
lngLineNumber = lngLineNumber + 1

‘Determine if the current line number is a odd or even
‘ number
If lngLineNumber Mod 2 > 0 Then
Me.Detail.BackColor = 12632256
Else
Me.Detail.BackColor = 16777215
End If
End Sub

Private Sub Report_Open(Cancel As Integer)
‘Initially Set the Line Number Variable
lngLineNumber = 0
End Sub

Cheers

Jayden

Reply | Quote

I’ve just realised as an alternative, you can do it without the module level variable.

In this example, I’ve used the same technique, but instead, just checked to see if the current colour of the detail section is white. If it is white, then the colour is set to grey. If it isn’t white, then it is set to white.

Option Compare Database
Option Explicit

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
‘===========================================================’
‘ Author: Jayden MacRae ‘
‘ Created: 26/10/2001 ‘
‘ Purpose: set the background colour appropriately or not. ‘ ‘
‘===========================================================’

‘Determine if the current line number is a odd or even
‘ number
If Me.Detail.BackColor = 16777215 Then
Me.Detail.BackColor = 12632256
Else
Me.Detail.BackColor = 16777215
End If
End Sub

Reply | Quote

Thanks for that…it looks quite easy. I have done quite a lot of programming in VBA but never to control the output of an Access report.

Reply | Quote

You can download a useful file from Microsoft called RptSmp00.exe, that contains a database of report samples, including one to shade every other line on a report. It has lots of other goodies in it as well, so I highly recommend getting it for reference.

Reply | Quote

I read about this topic in Ms. Feddema’s “Inside Out” book (p. 295). I got the code to do the trick on-screen, but I don’t get the same results on paper. Matter of fact, on-screen my report header and page footer both appear as “light gray,” but they print out as plain-vanilla white. I don’t want my report header and page footer to print with light gray backgrounds, but I would like to see the alternate line shading in the detail section. What have I overlooked?

Reply | Quote

Does the alternate shading of the detail section show on the screen, but not on the printout? You might have to make the dark color a bit darker, because many printers print light colors much lighter than they display on screen.
A mid grey, e.g. RGB(192,192,192) is a bit dark on my screen as background, but prints nicely. You can experiment with other values.

Reply | Quote

The “light gray” looks pretty dark on screen, but I can detect no trace of it on paper, not even the faintest hint. I was hoping the answer would be as embarrasingly simple as the one you gave me about fractions rounding up in boxes too small to display them. Well, I’m not at work right now, so this’ll have to wait till tomorrow. Thanks for the suggestion–I’ll check it out tomorrow morning!

Reply | Quote

Also make sure your printer will actually print a light gray. I’ve seen printers that wouldn’t, or at least not until you turned on half-tones.

Reply | Quote

Another thought. If your printer is black and white, try using a yellowfor the shading. It prints light gray better than the grays do … unless you’re using a color printer, of course.

Reply | Quote

I use an HP Deskjet 970 (color inkjet). The report I’m building in Access will replace a similar document we created years ago in Word. The Word table is autoformatted (“Contemporary”) for alternate line formatting, and said formatting prints just fine.

In fact, my Access report includes lines and rectangles set to print some shade of gray, and they print OK! Do I need to do something with the “on print” event procedure?

(For Helen:) This discussion brings up another question. The VBA code you provided in your book defines “vbLightGrey” as 12632256. Where does one find the “catalog” or whatever to get that string of eight digits to define specific colors? The More Colors dialog box displays numbers for Red/Green/Blue and for hue, saturation and luminescence, but…how does all that translate into that eight-digit number? I guess I need to understand this in order to change my code to specify yellow as the alternate-line shade.

And oh, by the way, what does “vb” (as in vbLightGrey) stand for? THANKS!

Reply | Quote

1. The code to alternate the background color should be in the OnPrint or OnFormat event of the Detail section (not in any of the headers or footers).
The controls in the detail section should have their background style set to transparent.
If you still have problems, can you post the code you’re using?

2. Colors are defined by their RGB (Red/Green/Blue) values. Each can be 0 … 255 (hexadecimal 0 .. FF).

A color is stored as a long integer. The number representing a color is RedValue + 256 * (GreenValue + 256 * BlueValue).

For example:
Black has Red = 0, Green = 0, Blue = 0, so it is stored as 0 (hex 000000)
White has Red = 255, Green = 255, Blue = 255, so it is stored as 255 + 256 * (255 + 256 * 255) = 16,777,215 (hex 00FFFFFF).
Pure Green has Red = 0, Green = 255, Blue = 0, so it is stored as 0 + 256 * (255 + 256 * 0) = 65,280 (hex 0000FF00).
Cyan has Red = 0, Green = 255, Blue = 255, so it is stored as 0 + 256 * (255 + 256 * 255) = 16,776,960 (hex 00FFFF00).

A light grey could be Red = 192, Green =192, Blue = 192, so it is stored as 192 + 256 * (192 + 256 * 192) = 12,632,256 (hex 00C0C0C0).

As you see, the hexadecimal notation is far more transparent than the decimal notation.

If you look up RGB in the help index, you’ll find more info.

3. Microsoft uses a naming convention for constants: the first two letters are always lower case and represent the application; the part after that starts with an upper case letter. Examples:

vbWhite: Visual Basic constant
acQuery: Access constant
xlCalculation: Excel constant
wdCharacter: Word constant

In VBA, there are 8 predefined constants for the basic colors, so you don’t need to know the numbers for these colors:
vbWhite, vbRed, vbGreen, vbBlue, vbCyan, vbMagenta, vbYellow and vbBlack.

If a programmer wants to define another color constant, it is convenient to use the same prefix for consistency, like vbOrange. This is only a matter of consistency though, there is nothing to stop you from naming it conColorOrange or something like that.

Reply | Quote

What a thorough response! (Where do you guys learn this stuff!?)

The formatting is in the Detail section. (On the property sheet for the Detail section: Event tab, On Format…). The code I’m using came straight outta Helen’s book (page 295, “Alternate Line Shading”). Thus:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Const vbLightGrey = 12632256

If Me.CurrentRecord Mod 2 = 0 Then
Me.Section(acDetail).BackColor = vbLightGrey
Else
Me.Section(acDetail).BackColor = vbWhite
End If

End Sub

The formatting shows up on the screen exactly like I want it. Just doesn’t print!

Oh, yes: The controls in the Detail section all have their background set to transparent.

Reply | Quote

I’m reduced to incoherent rambling…

I can’t see anything wrong with the code. I copied it straight out of your post into a report, and it printed as desired…

And since you mention that controls with a grey background are printed correctly, it seems not to be a printer setting.

When I check Data only in the Page Setup dialog, the alternating bands disappear from both the screen display and the printout.

What happens if you replace vbLightGrey by vbBlack? You should get horrible black bars.

Reply | Quote

Yeah…”horrible black bars” is correct! But…they won’t print!

I tried changing the border properties of all my Detail rectangles and text boxes to transparent/color White, but to no avail.

So…next I’m gonna try printing this thing at a different system: Different computer, different printer, same OS.

Thanks for all your time and advice.

Reply | Quote

HP told me to install the DeskJet 550 driver. Did that, and now the alternate-line shading prints!! That’s the good news. The bad news is that the 550C driver doesn’t recognize the double-sided printing feature of the 970Cxi. I can have sausage, but I can’t have bacon, too. Nuts!

Reply | Quote

OK, this isn’t the last word, but….

I tried printing this report on another system: Same make and model printer and same results. But then I used that second computer to print the report using an HP LaserJet 4. Results: the shading printed exactly as I wanted! The problem was the inkjet printer all along.

Sounds like I need to ask Hewlett Packard a question or two….

Thanks again!

Reply | Quote