By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,491 Members | 879 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,491 IT Pros & Developers. It's quick & easy.

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

P: 63
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!
Nov 3 '08 #1
Share this Question
Share on Google+
11 Replies


puppydogbuddy
Expert 100+
P: 1,923
Off the top of my head (not tested), try this in the print or format events of the subreport:

Expand|Select|Wrap|Line Numbers
  1. 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:

Expand|Select|Wrap|Line Numbers
  1. Const vbLightGrey = 12632256 
  2.  
  3.  
  4. If Me.CurrentRecord Mod 2 = 0 Then
  5. Me.Section(acDetail).BackColor = vbLightGrey   'for even records
  6. Else
  7. Me.Section(acDetail).BackColor = vbWhite         'for odd records
  8. End If 
Nov 3 '08 #2

P: 63
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)?
Nov 4 '08 #3

puppydogbuddy
Expert 100+
P: 1,923
Please provide details of what "didn't work" means...error messages,etc. so that I can adjust the code accordingly. Thanks.
Nov 4 '08 #4

puppydogbuddy
Expert 100+
P: 1,923
PS:
Try changing line 4 of the above code from:

If Me.CurrentRecord Mod 2 = 0 Then

to this:

If Me.Parent.CurrentRecord Mod 2 = 0 Then


Let me know if there is any different result.
Nov 4 '08 #5

P: 63
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!
Attached Images
File Type: png subformexample.PNG (1.2 KB, 280 views)
Nov 4 '08 #6

puppydogbuddy
Expert 100+
P: 1,923
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 "YourSubreport" 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:
Expand|Select|Wrap|Line Numbers
  1. Const vbLightGrey = 12632256  
  2.  
  3.  
  4. If Me.CurrentRecord Mod 2 = 0 Then            'me used here is the Main report 
  5. Me.YourSubreport.Report.Section(acDetail).BackColor = vbLightGrey   'for even records 
  6. Else 
  7. Me.YourSubreport.Report.Section(acDetail).BackColor = vbWhite         'for odd records 
  8. End If  
  9.  
Nov 5 '08 #7

P: 63
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 "Transparent" option for the subreport....

Thanks!
Nov 5 '08 #8

puppydogbuddy
Expert 100+
P: 1,923
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 "Transparent" 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:
Expand|Select|Wrap|Line Numbers
  1. Dim x as Integer
  2. Me.Detail.BackColor = vbWhite
  3. x = Me.txtRunningSum mod 2
  4. If x = 0 Then
  5.     Me.Detail.BackColor = vbLightGrey     'or whatever color you want
  6. End If
_______________________

now place this code in the subreport's OnFormat event:
Expand|Select|Wrap|Line Numbers
  1. Dim z As Integer
  2. Me.Detail.BackColor = vbWhite
  3. z= Me.Parent.txtRunningSum mod 2
  4. If z = 0 Then
  5.     Me.Detail.BackColor = vbLightGrey     'or whatever color you want
  6. 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.
Nov 6 '08 #9

P: 63
Well, it worked, but only in Print Preview mode.

Your previous code worked also in Print Preview mode:
Expand|Select|Wrap|Line Numbers
  1. If Me.Parent.CurrentRecord Mod 2 = 0 Then
  2.     Me.Section(acDetail).BackColor = vbLightGrey   'for even records
  3. Else
  4.     Me.Section(acDetail).BackColor = vbWhite         'for odd records
  5. 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.
Nov 6 '08 #10

puppydogbuddy
Expert 100+
P: 1,923
You are ok.

See this link for difference between Report and Preview Modes:

http://groups.google.com/group/micro...8c262b8ded844a
Nov 6 '08 #11

P: 63
Interesting. But the author in that link says that no event code will run, but I've seen OnPaint event code run while in Report View. I'll have to investigate that some more.

Thanks!
Nov 6 '08 #12

Post your reply

Sign in to post your reply or Sign up for a free account.