We help IT Professionals succeed at work.

Set detail section alternating background color of report based on field value

caltrans
caltrans asked
on
I have a multiple use report that I want to distinquish the data in the detail section based on a field value that specifies the type of report.  In other words, in the Report Header, I have a field called description with a conditional statement so that if the description value is "Soil", then the field has a brown background color.  If the description value is "Water", the field background color is blue.  I was able to carry the color over to the detail section as a background color with some simple coding, but I haven't figured out how to change the row colors to alternate.  So if the description field value is "Soil", then the rows in the detail section might alternate between  light brown and light orange background colors.  Hopefully that makes sense.
Comment
Watch Question

CERTIFIED EXPERT

Commented:
This MS page shows the code of using vba in the Format Event of the Detail Section to alternate colors:

http://office.microsoft.com/en-us/access-help/shading-alternating-rows-in-reports-HA001219146.aspx

I'm thinking you can specify the color (shadedColor in code) using an If statement, depending on if description is Water then blue, else description is Soil then brown.

Is that where you're headed ?

Author

Commented:
DoveTails:

Yes, I had already looked at the link you provided, but I'm not really a programmer, so I haven't been able to successfully add an If statement for the description field value such that If Soil, then this set of alternating colors applies, if Water then this set of alternating colors applies, else default to a third set of alternating colors.
CERTIFIED EXPERT

Commented:
Okay...I modified the Microsoft example and included an If statement.
Text20.Value will be your description from your header.  I guessed at some colors.
Hope this helps...

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

Dim shadedcolor As String

If Text20.Value = "Water" Then
     shadedcolor = vbRed
     Else
     shadedcolor = vbBlue
     End If

Dim normalcolor As String

normalcolor = vbWhite

  On Error GoTo Detail_Format_Error

' Choose a color based on the shadeNextRow value
  If shadeNextRow = True Then
    Me.Section(acDetail).AlternateBackColor = shadedcolor
  Else
    Me.Section(acDetail).AlternateBackColor = shadedcolor
  End If

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

Detail_Format_Error:

Open in new window

CERTIFIED EXPERT
Commented:
Sorry, I should have pasted the following code ... give it a try and hopefully this is more applicable for your Water and Soil description.


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

Dim shadedcolor As String

If Text20.Value = "Soil" Then
     shadedcolor = vbRed
     Me.Section(acDetail).AlternateBackColor = shadedcolor
     ElseIf Text20.Value = "Water" Then
     shadedcolor = vbYellow
     Me.Section(acDetail).AlternateBackColor = shadedcolor
     Else
     shadecolor = vbWhite
     Me.Section(acDetail).AlternateBackColor = shadedcolor
     End If

Open in new window

CERTIFIED EXPERT
Commented:
Try adding this code to your report:
Dim darkShade As Long, lightShade As Long

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    Static isShaded As Boolean
    
    If isShaded Then
        Me.Section(acDetail).BackColor = darkShade
    Else
        Me.Section(acDetail).BackColor = lightShade
    End If
    isShaded = Not isShaded
    
End Sub

Private Sub Report_Load()
    Select Case Me.Description
        Case "Soil"
            darkShade = RGB(230, 161, 70)
            lightShade = RGB(241, 203, 153)
            
        Case "Water"
            darkShade = RGB(150, 181, 222)
            lightShade = RGB(200, 216, 238)

    End Select
    
End Sub

Open in new window

Author

Commented:
Many thanks to both Dovetail & Irogsinta.  I played around with both solutions and determined that Irogsinta's solution provided more flexibility.  Should I need to add additional material descriptions such as concrete, cement, etc. Irogsinta's solution appears easier to adapt.  Again many thanks to the excellent response from both Dovetail and Irogsinta.