• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1165
  • Last Modified:

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

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.
0
caltrans
Asked:
caltrans
  • 3
  • 2
2 Solutions
 
DoveTailsCommented:
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 ?
0
 
caltransAuthor 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.
0
 
DoveTailsCommented:
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

0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
DoveTailsCommented:
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

0
 
IrogSintaCommented:
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

0
 
caltransAuthor 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.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now