We help IT Professionals succeed at work.

Access 2016 Report Not Applying Backcolour To Fields

Bob Collison
Bob Collison asked
on
Hi Experts,

I have just completed a report where I apply code like the following in the Report Load event to various fields.  I have the following Sections in the report:
- Page Header.
- Section 3 Header - This is where the fileds are that are being formatted.
- Detail.
- Page Footer.

'Step 811-Apply Group Pre-Registration Fee Amount Background Colour.
WrkFieldAmt001 = Me.WrkGroupPreRegFeeAmt
Dim WrkDisplayGroupPreRegFeeAmt811 As Currency
WrkDisplayGroupPreRegFeeAmt811 = 0
WrkDisplayGroupPreRegFeeAmt811 = WrkFieldAmt001
'Me.WrkGroupPreRegFeeAmt.BackStyle = 0   'Trasparent
Me.WrkGroupPreRegFeeAmt.BackColor = ColourWhite001
Select Case WrkDisplayGroupPreRegFeeAmt811
   Case Is = 0
      Me.WrkGroupPreRegFeeAmt.BackStyle = 0   'Trasparent
      Me.WrkGroupPreRegFeeAmt.BackColor = ColourWhite001
      GoTo STEP_812   'Step 812-Apply Group Pre-Registration Total Payments Amt Background Colour.
   Case Is < 0
      Me.WrkGroupPreRegFeeAmt.BackStyle = 1   'Normal
      Me.WrkGroupPreRegFeeAmt.BackColor = ColourLiteRed001
      GoTo STEP_812   'Step 812-Apply Group Pre-Registration Total Payments Amt Background Colour.
    Case Is > 0
      Me.WrkGroupPreRegFeeAmt.BackStyle = 1   'Normal
      Me.WrkGroupPreRegFeeAmt.BackColor = ColourLiteGreen001
      GoTo STEP_812   'Step 812-Apply Group Pre-Registration Total Payments Amt Background Colour.
   Case Else
      Me.WrkGroupPreRegFeeAmt.BackStyle = 0   'Trasparent
      Me.WrkGroupPreRegFeeAmt.BackColor = ColourWhite001
      GoTo STEP_812   'Step 812-Apply Group Pre-Registration Total Payments Amt Background Colour.
End Select

When I run the report in 'Preview' it doesn't apply the formatting.  If I go to Design View and back to Report View the formatting is applied.

I reviewed the Microsoft Support Help to see if the order of events is having an effect but their documentation doesn't even mention the Report Load Event.

Could you please advsie me of how I can code this to ensure that the formatting aways is applied?

Thanks,
Bob C.
Comment
Watch Question

I could be wrong, but I think maybe the Report Load event only fires in report view, while certain other events such as Detail_format and detail_print only runs in Preview and Normal.

But to as a different question: Why are you using code for this? By the looks of you have shown it could be handled with conditional formatting.
Bob CollisonSystem Architect

Author

Commented:
Hi Anders,

Thanks for the comments

I am using code for this as I have similar code in various forms.  The solution requires that If an amount is negative the background be red, if positive it be green with zero having no background colout.

I'm not sure what you mean by 'conditional formatting'.  Could you please explain?

Thanks,
Bob C.
When the textbox is selected in design view, if you look in the ribbon under the format tab, there is a button called conditional formatting. Here you can setup your expressions and which format to use when the expression is met.

You do specify background color here, but if you were to just format the textcolor, you can setup that by using the format property of the control, I don't recall the exact syntax right now, but can find it for you, if it has an interest.
John TsioumprisSoftware & Systems Engineer
Distinguished Expert 2019

Commented:

Conditional Formatting is the way to accomplish what you want

2 ways...setting it up manually or by code

Check my attachment

FCDEMO.accdb

Bob CollisonSystem Architect

Author

Commented:
Hi Experts,

Anders,
I tried useing the conditioal formatting tool on the report fields and it doesn't work correctly.  It still does the same thing.  

John,
I will try your code and let you know the results.

Thanks,
Bob C.
MIS Liason
Most Valuable Expert 2012
Commented:
I tried useing the conditioal formatting tool on the report fields and it doesn't work correctly.
Can you post a stripped down sample of your database, (that exhibits this issue), for us to examine...?

In the old days, If you set the backstyle property of the textbox to: Transparent, ...it would "Hide" back-color changes.
(With a transparent back style, ...the color was always "Transparent")
This seems to be fixed in my most recent version of Access, ...but to be sure, ...check that as well.

Also, ...I almost never use Report View, ...because it does not represent exactly what the actual printout will look like (Page breaks,...Page numbering, ...ect)
(The features of Report View (Filtering, ...record navigation, ...etc) are things I don't really use.)
..so, flip-flopping between the two views may not give you what you want.

So as the Expert above stated, ...In Print Preview, ...the code should fire On the "Format" event of the section, ...so keep us posted on the code suggestion,...  But again, ...Conditional formatting should have worked for you.
Bob CollisonSystem Architect

Author

Commented:
Thanks to you all for your suggestions.
Boib C.
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
ok