Alternating Line Color in Access Reports


Change Background Color of Report Lines

Do you remember the good old days when you got fan-fold computer printouts that had alternating color lines?  These banded report lines made it easier for your eyes to follow across wide detail lines.Here’s a way for you to reproduce this alternating line color effect without having to buy the old fan-fold paper and you can choose any color – pastels only please.

Follow these steps:Create an ‘On Print’ event procedure in the Detail section of your report by clicking on the ‘Detail’ grey bar and viewing the properties sheet.In the VB code create a global variable just under the ‘Option Compare Database’ line. New! Download Access Report Banding Database Example

In this example we use ‘Dim gbl_shade As Boolean’.Create the event procedure code as show below.  This code will interrogate the gbl_shade variable and when it is True the report line background color gets changed (in this case the highlight color will be yellow-gold).  After the if statement the code switches the gbl_shade variable to the opposite boolean value.

Newly figured out simple method without all the complexity of the above alternating line color code:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Me.Detail.BackColor = 16777215 Then
Me.Detail.BackColor = 12582910
Me.Detail.BackColor = 16777215
End If
End Sub

Now, isn’t the above code much simpler?


Microsoft Office VBA, MS Access 2003, 2007, 2010, 2013, 2016