Alternating row colors ignoring grouping (1 Viewer)

[tmyers]

On a report, I am trying to keep my alternating row colors consistent. Currently, it will restart a new white row when it moves to the next group on the report which can create unsightly white blocks. Is there a simple way to have it not do that? Or will have to hard code the formating?

 

[theDBguy]

Sounds like you may have to use code to do it.

 

[tmyers]

Sounds like you may have to use code to do it.

I was worried that would be the answer. Could you possibly provide a snippet, or a resource for me to look at to learn how to code it?

 

[arnelgp]

you can use the Format Event of the the Detail section to set the "alternate backcolor".
see "AlternatingColor" report.

 

[tmyers]

Ill take a look at it now. Thanks arnelgp!

 

[tmyers]

you can use the Format Event of the the Detail section to set the "alternate backcolor".
see "AlternatingColor" report.

I got it working, but am not quite getting the desired results. I am basing it off my "Catagory" field, which is numerical. This doesn't display ne the report itself, but it part of its source.

In my test, it is going 1,1,1,2,2,2,2,2,3,3,3,4,5 etc. So it only colored the background of the first row within the particular group. What do I need to change?


Edit:
Correction, it does not seem to be working at all. Here is what I am using:

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

Dim varGroup    As Variant
Dim lngCounter  As Long

    If varGroup <> Me.txtRunSum Then
        varGroup = Me.txtRunSum
        lngCounter = 0
    End If
   
    lngCounter = lngCounter + 1
   
    If (lngCounter Mod 2) Then
       
        Me.Section(0).BackColor = vbWhite
       
    Else
       
        Me.Section(0).BackColor = RGB(198, 217, 242)
   
    End If

   
End Sub

 

[tmyers]

I switched to using:

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

    Dim C       As Long
   
    C = Me.txtRunSum.Value
   
    Static OddEven As Boolean
   

    If OddEven Then
        Me.Section(0).AlternateBackColor = vbWhite
        Me.Section(0).BackColor = RGB(197, 217, 242)
    Else
        Me.Section(0).BackColor = vbWhite
        Me.Section(0).AlternateBackColor = RGB(197, 217, 242)
    End If
  
    OddEven = C Mod 2
   
End Sub

However, this is the result:

It is not quite working as I had hoped. The line for Building A should be white, the line for LDN6 should be blue etc etc.

 

[arnelgp]

it is better to "add" an unbound textbox to the Detail section, and add color to this textbox
rather than on the Section's backgrouond.

if you can share the report in question?

 

[tmyers]

I unfortunately can't share it currently. I will try to cut it out and share, but don't know when I will be able to.

It "appears" to be working now. I added a for loop to go line by line, and it seems to be giving me the desired results. Will test a little more.

 

[tmyers]

That iteration ended up not working either.
I have now moved to using

    If Me.RowNumTxtBx Mod 2 = 0 Then
        Me.Detail.BackColor = RGB(197, 217, 242)
    Else
        Me.Detail.BackColor = vbWhite
    End If

I added another control in my details section and used it to number my rows. The simple set the backcolor property based off of that using Mod 2. So far, after several different test, it appears to be working whereas the other broke after 2 or so attempts.

 

[tmyers]

Another issue has arisen (sigh). Now when I export the report as an email attachment, the formatting never gets applied. Is there something I need to change in the process of exporting to email? Below what I am using for that.

Private Sub ExportRptBtn_Click()

Dim ReportName As String
Dim AttachmentName As String

On Error GoTo errhandler

ReportName = "QuoteRpt"
AttachmentName = "Q" & Me.QuoteNumTxtbx & " " & Me.JobNameTxtbx

DoCmd.OpenReport ReportName, acViewReport, , , acHidden
Reports(ReportName).Caption = AttachmentName
DoCmd.SendObject acSendReport, ReportName, acFormatPDF, , , , Me.JobNameTxtbx, , , True

'clean up
Reports(ReportName).Caption = ReportName

Exit Sub

errhandler:
    If Err = 2501 Then
        'user canceled
    Else
        MsgBox Err.Description, vbExclamation
    End If
'clean up
Reports(ReportName).Caption = ReportName
Exit Sub

End Sub

 

[arnelgp]

use acViewPreview instead of acViewReport

 

[tmyers]

I tried switching that when it did not work, but I then get the error