Alternating row colors ignoring grouping (1 Viewer)

tmyers

Well-known member
Local time
Today, 10:02
Joined
Sep 8, 2020
Messages
1,090
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

I’m here to help
Staff member
Local time
Today, 07:02
Joined
Oct 29, 2018
Messages
21,358
Sounds like you may have to use code to do it.
 

tmyers

Well-known member
Local time
Today, 10:02
Joined
Sep 8, 2020
Messages
1,090
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

..forever waiting... waiting for jellybean!
Local time
Today, 22:02
Joined
May 7, 2009
Messages
19,175
you can use the Format Event of the the Detail section to set the "alternate backcolor".
see "AlternatingColor" report.
 

Attachments

  • Group Numbering On Report.accdb
    1.1 MB · Views: 184

tmyers

Well-known member
Local time
Today, 10:02
Joined
Sep 8, 2020
Messages
1,090
Ill take a look at it now. Thanks arnelgp!
 

tmyers

Well-known member
Local time
Today, 10:02
Joined
Sep 8, 2020
Messages
1,090
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:
Code:
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
 
Last edited:

tmyers

Well-known member
Local time
Today, 10:02
Joined
Sep 8, 2020
Messages
1,090
I switched to using:
Code:
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:
Capture.PNG


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

..forever waiting... waiting for jellybean!
Local time
Today, 22:02
Joined
May 7, 2009
Messages
19,175
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

Well-known member
Local time
Today, 10:02
Joined
Sep 8, 2020
Messages
1,090
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

Well-known member
Local time
Today, 10:02
Joined
Sep 8, 2020
Messages
1,090
That iteration ended up not working either.
I have now moved to using
Code:
    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

Well-known member
Local time
Today, 10:02
Joined
Sep 8, 2020
Messages
1,090
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.
Code:
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

..forever waiting... waiting for jellybean!
Local time
Today, 22:02
Joined
May 7, 2009
Messages
19,175
use acViewPreview instead of acViewReport
 

tmyers

Well-known member
Local time
Today, 10:02
Joined
Sep 8, 2020
Messages
1,090
I tried switching that when it did not work, but I then get the error

Capture.PNG
 

Users who are viewing this thread

Top Bottom