Easy-Read Reports ~ LEARN MS-ACCESS TIPS AND TRICKS

Introduction.

When computer reports, accounting ledger statements, or purchase invoices are printed with closely spaced lines, readability improves if alternate lines are shaded with a light background color. Traditionally, pre-printed stationery was used for this purpose. Line printers typically print six lines of data within a one-inch vertical space. To emphasize headlines, the double-strike method was used, where the hammer struck each character twice—this being the only enhanced printing style available in line printers.

Line-printer-based reports were designed on graph-paper-like sheets, with carefully written code to position headings, data lines, and summaries precisely on the pre-printed stationery.

An A4 sheet (8.5 × 11 inches) provides 66 print lines vertically. Of these, one inch at the top and half an inch at the bottom are reserved as margins, while the alternate data lines are shaded in green—similar to the sample image shown below.

Right-click to open Large Image in New Tab/Window.

A4-size paper has 80 print positions across when a 10-character-per-inch (pitch) font size is used. This can be increased to 96 characters if a 12-character-per-inch character size is used.

The above details provide a general background about computer stationery. Reports designed for line printers were created with these specifications in mind. When necessary, you can design reports for plain paper by setting the section height and the text box control height to one-sixth of an inch, matching the line spacing used by traditional line printers.

Light Shading of Alternate Report Data Lines

We will use a simple trick to print alternate lines with a shaded background, making the report easier to read. There is no need for pre-printed stationery, as the shading will be applied dynamically during printing.

You can design a Quick Report and add a few lines of VBA code in the report’s class module. If you already have a report with closely spaced detail lines, you may skip directly to step 3 below.

Import Products Table from MS-Access sample database Northwind.mdb
Use the Report Wizard to design a Tabular Report with the Products Table, like the sample image given below:

Right-click to open Large Image in New Tab/Window.
Open the Report in Design View.
Select all the controls on the Detail Section of the Report and drag them to the right to get enough space to draw a Text Box on the left side to display Serial Numbers on the Report Lines. Drag the heading lines and position them to the right.
Draw a Text Box on the left side and write the expression =1 in the Control Source Property. Change the Name Property Value to SRL.
Change the Running Sum Property Value to Over All.
Create a Label control at the Page Header above the Text Box and change the Caption to SRL (for Serial Number).
Select all the controls in the Detail Section together and display the Property Sheet (F4 or Alt+Enter).
Change the Top Property Value to 0. All the controls will be shifted and positioned at the top edge of the Detail Section.
Select the Rectangle Tool from the Toolbox and draw a rectangle around all the text boxes in the Detail Section (see the design view image above).
Display the Property Sheet of Rectangle (F4) and change the Name Property Value to Box1.
Select the Send-to-Back option from the Arrange Menu to position the rectangle behind the text box controls.
Reduce the Detail Section height so that there is no empty space below the TextBox controls.
Display the VBA Code Module of the Report (ALT+F11).

Copy and paste the following lines of VBA Code into the Code Module and save the Report:

The Report Module VBA Code.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim xSrl
xSrl = [SRL]
If xSrl / 2 = Int(xSrl / 2) Then
   [Box1].BackColor = &HCCC8C2
Else
   [Box1].BackColor = &HFFFFFF
End If
End Sub