Highlighting Alternate Rows In a Report

Posted on 2008-11-14
Last Modified: 2013-11-28
I have tried several different options for highlighting alternate rows in my reports.  I can't seem to make any stick.  Then, I came accross the following code on Microsofts site:
Option Compare Database
Option Explicit

Private shadeNextRow As Boolean
Const shadedColor = 12632256
' Const shadedColor = 15726583 ' alternative shade colors
' Const shadedColor = 14078404
' Const shadedColor = 13356495
' Const shadedColor = 14281974
Const normalColor = 16777215

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

  On Error GoTo Detail_Format_Error

' Choose a color based on the shadeNextRow value
  If shadeNextRow = True Then
    Me.Section(acDetail).BackColor = shadedColor
    Me.Section(acDetail).BackColor = normalColor
  End If

' Switch the color for the next row
  shadeNextRow = Not shadeNextRow
  Exit Sub

  MsgBox "Error " & Err.Number & ": " & Err.Description
  Resume Detail_Format_Exit

End Sub

Problem is, with this code added, my report will NOT open in print preview, and after I try, I can no longer get into the design view of my report without either deleting the code or closing/reopening the database.   Any ideas on either making the code above work, or an alternative?

Thanks in Advance.
Question by:cebaker75

    Author Comment

    Forgot Something-  The code I attached does work when the report is printed, but my reports need to open to print preview.  
    LVL 11

    Accepted Solution

    One simple way is to use the record counter

    you can use a number of schemes to alternate...  including  
    If Me.detail.backcolor = vbwhite then
         Me.detail.backcolor = vbwhite
    End if

    Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
        If Me.RecordCounter Mod 2 Then
            Me.Detail.BackColor = 14540253
            Me.Detail.BackColor = vbWhite
        End If
    End Sub

    Open in new window


    Author Closing Comment

    Wow, that was so much easier and better.  Thank RgGray3!
    LVL 11

    Expert Comment

    Glad to be of assistance...

    Sometimes things LOOK or SEEM more difficult than they really are...

    Sometimes it takes more time to write 1 line of code than it does to write a page....

    Always ask yourself ....   there has got to be a simple way to do this......  and often there is!

    Good luck
    LVL 11

    Expert Comment


    Any release of the Ken Getz "Access Cookbook" books have Lots of these types of examples...
    They are well structured and organized with sample databases to illustrate each concept...

    Well worth the price of admission

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Join & Write a Comment

    This article is a continuation or rather an extension from Cascading Combos ( and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
    In the previous article, Using a Critera Form to Filter Records (, the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
    Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
    In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now