Match sub-report backcolor to alternating row color in main report

[postman]

In Access 2007, I have a report with a sub-report in the Detail section. The Detail section of the main report has alternating colors for each row displayed.

Is there a way to get the sub-report to automatically synchronize its background color with the Detail section in the parent report for each row?

Thanks in advance!

[puppydogbuddy]

Off the top of my head (not tested), try this in the print or format events of the subreport:

Me.Section(acDetail).BackColor = Me.Parent.Section(acDetail).BackColor

If the above does not work, then try the following, which has been adapted from a tip provided on the web site of www.aadconsulting.com

1. Placing the subreport in design view, the BackStyle of all record Textboxes in the detail section should be set to Transparent.

2. Declare constants for your colors in the Declarations of the Subreport's module:
As an example, using the color constants below, the Subreport will display a white background for odd records and a light gray background color for even records.

3. Add the following code to the Subreport's Detail section OnFormat Event:

Const vbLightGrey = 12632256 
 
 
If Me.CurrentRecord Mod 2 = 0 Then
Me.Section(acDetail).BackColor = vbLightGrey   'for even records
Else
Me.Section(acDetail).BackColor = vbWhite         'for odd records
End If 

[postman]

Thanks for replying! Unfortunately, I could not get either suggestion to work.

The On Format or On Print events wouldn't even trigger when the report was opened.

When referring to the "BackColor" property of the parent Detail section, will it retrieve the actual BackColor, or the preset BackColor (in the Detail section's properties page)?

[puppydogbuddy]

Please provide details of what "didn't work" means...error messages,etc. so that I can adjust the code accordingly. Thanks.

[puppydogbuddy]

PS:
Try changing line 4 of the above code from:

If Me.CurrentRecor d Mod 2 = 0 Then

to this:

If Me.Parent.Curre ntRecord Mod 2 = 0 Then


Let me know if there is any different result.

[postman]

I didn't get a different result, but I think it's because the OnFormat event isn't firing when I open the form. There's no error, just nothing is changing with the colors.

I tried putting the code into the OnPaint event--to make sure it was working--and it did work, sort of. The first row of the sub-report list shows gray on every record, then white for the rest of it. I attached an example showing an alternate row with the subform displayed (next to "Additional ").

Any idea why the OnFormat isn't firing?

Thanks!

[puppydogbuddy]

As to why the OnFormat is not firing, check the property sheet and verify that the words "Event Procedure" are shown for the FormatEvent.

Here is something else to try. The above code was placed behind the subreport, and attempted to reference the parent report property. Try placing the code below behind the Main report and, as you can see, it references the subreport. Replace "YourSubrep ort" with the actual name of your Subreport and see if it works. If you get an error on the reference to .Report, leave it out of the expression:

Const vbLightGrey = 12632256  
 
 
If Me.CurrentRecord Mod 2 = 0 Then            'me used here is the Main report 
Me.YourSubreport.Report.Section(acDetail).BackColor = vbLightGrey   'for even records 
Else 
Me.YourSubreport.Report.Section(acDetail).BackColor = vbWhite         'for odd records 
End If  
 

[postman]

As to why the OnFormat is not firing, check the property sheet and verify that the words "Event Procedure" are shown for the FormatEvent.

I double-checked and "Event Procedure" is showing there.

I tried your code modification in the OnPaint event to make sure it fired and got the same results as shown before.

If it did work though, would it pull the ACTUAL backcolor of the row, or just the backcolor specified in the detail section's property sheet?

I'm open to more ideas. Too bad there wasn't just a "Transparen t" option for the subreport....

Thanks!

[puppydogbuddy]

I double-checked and "Event Procedure" is showing there.

I tried your code modification in the OnPaint event to make sure it fired and got the same results as shown before.

If it did work though, would it pull the ACTUAL backcolor of the row, or just the backcolor specified in the detail section's property sheet?

I'm open to more ideas. Too bad there wasn't just a "Transparen t" option for the subreport....

Thanks!

It should pull the actual backColor of the row. Let's try this:
Place an unbound textbox in the detail section of the main report. Name it txtRunningSum and set its control source to: = 1 and set its RunningSum property to "OverAll". Set its visible property to no. Finally, set its BacKStyle property to transparent as was done for the other textboxes and labels in the detail section.

Use this code in the detail section's onFormat event in place of what you had before:

Dim x as Integer
Me.Detail.BackColor = vbWhite
x = Me.txtRunningSum mod 2
If x = 0 Then
    Me.Detail.BackColor = vbLightGrey     'or whatever color you want
End If

_______________ ________

now place this code in the subreport's OnFormat event:

Dim z As Integer
Me.Detail.BackColor = vbWhite
z= Me.Parent.txtRunningSum mod 2
If z = 0 Then
    Me.Detail.BackColor = vbLightGrey     'or whatever color you want
End If

Whether the above code works or not depends on the whether the main report's or the subreport's onFormat event fires first. If the main fires first, it should work. If the subreport fires first, let me know and I will modify accordingly.

[postman]

Well, it worked, but only in Print Preview mode.

Your previous code worked also in Print Preview mode:

If Me.Parent.CurrentRecord Mod 2 = 0 Then
    Me.Section(acDetail).BackColor = vbLightGrey   'for even records
Else
    Me.Section(acDetail).BackColor = vbWhite         'for odd records
End If

(in the subreport Detail section's onFormat event)

The OnFormat event would not fire if the report was opened in the "Report View" mode--which is often how the report is opened in the app I'm working on. It would only fire if I opened the report in Print Preview mode.

I tested it by putting a MsgBox in both sets of code with a message identifying whether it was the main or the sub report. Both events would fire when opened in Print Preview mode.


So now, I need to figure that out...but that's probably a whole separate issue.