How do I change a report line color based on a field value, with VBA.

In Access 2007, I have a report, rptWorkarounds, with the field, Workaround_Status, that is filled with Open or Resolved. I'd like the line of the report to change to a gray background when I change the status to Resolved. So far, I have this, but it's not working.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Me.Workaround_Status = "Resolved" Then
     Me.Section(acDetail).BackColor = 12632256 'grey
     Me.Section(acDetail).BackColor = 16777215 ' std white
 End If
End Sub
David BigelowStaff Operations SpecialistAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Boyd (HiTechCoach) Trimmell, Microsoft Access MVPDesigner and DeveloperCommented:
Your code should may work only in print preview not report view.  What mode are you viewing?

If your code does not work in Print Preview try:
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Me.Workaround_Status = "Resolved" Then
     Me.Detail.BackColor = 12632256 'grey
     Me.Detail.BackColor = 16777215 ' std white
 End If
End Sub 

Open in new window

Jeffrey CoachmanMIS LiasonCommented:
Though not what you asked for specifically, ...You can also use conditional formatting on all of the controls in the detail section and turn them all "grey".

Simply select all the controls in the detail section in design view and set the conditional formatting something like this:
Expression is [Workaround_Status] = "Resolved"

The good part here is that the result looks similar to what you want, ...but the code will work in either Print Preview or Report View

see rptConditionalFormatting in the simple attached db

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jeffrey CoachmanMIS LiasonCommented:
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Dale FyeOwner, Developing Solutions LLCCommented:
I would agree with Jeff that conditional formatting is the "preferred" method for accomplishing this task.  However, there are limits to the number of conditional formats you can apply.

The advantage of Boyd's technique is that you can have as many different conditions and colors as you want, although going beyond the default + 3 will generally cause more confusion than it is worth (unless you include a key somewhere in the form or report to indicate what each color code means).
Jeffrey CoachmanMIS LiasonCommented:
To be clear, the previous experts have answered your post as written.
So take my post as just an FYI.

Yes, ...there are advantages and disadvantages to both approaches

To be toatly honest, I too, mostly will go with VBA because:
I am familiar with the code syntax
I always use Print Preview for reports
You can do "crazier" things with VBA for example: (make gray if the second field is between 1 and 7, ...and the fourth field is Monday, and the state is NY, Or the department is 7)
You can have error handling and comments in VBA
Compatibility with older versions of Access where conditional formatting did not exist.

But for simple things like: Make a line Grey, I would investigate conditional formatting first.

But again, ...if you need a solution for VBA, then what the previous tow experts have posted is the way to go...


David BigelowStaff Operations SpecialistAuthor Commented:
Is there a way to view a Conditional Report Manager in Access 2007 to delete previously entered conditional formats (not VBA)? I'm thinking they may be interfering with my current testing.
Jeffrey CoachmanMIS LiasonCommented:

sorry for causing any confusion here...

Open the report in design view.

1. You can select all the texboxes (in the detail section), then click: Format-->Control Format-->Conditional formatting
Then in the dialog box, will display "Multiple"
From that drop down, can select each field and see the conditional formatting associated with it...

Or you can simply select each control individually, then click: Format-->Control Format-->Conditional formatting
But there, you might forget to select a control that might have formatting set...
David BigelowStaff Operations SpecialistAuthor Commented:
I'm sorry; I'm completely lost. I've seen several posts with Form-->Control Format-->Conditional Formatting while looking for answers, but I'm not finding that option. Are your instructions for Access 2007? In the ribbon, Design tab, Font group, I have a Conditional icon that I've been using, but it doesn't have a Conditional Report Manager associated with it, just the new rule window. There's a Format tab in the Property Sheet, but it has no Conditional Formatting option.

Anywhere else I should be looking? Maybe you could post a screen shot. It may be completely in front of my nose, but I'm not seeing it.

I've attached a screen shot of what I've been using.
Jeffrey CoachmanMIS LiasonCommented:

Access 2007:

acc 2007
David BigelowStaff Operations SpecialistAuthor Commented:
I finally found what was causing the trouble. In using an Access color theme, the BackStyle was transparent. Hence, any BackColor controls I put in were overridden by the default transparent setting. I found that here: Trying to block highlight the controls and change the BackStyle setting to Normal did not work. I had to select them one at a time to change the BackStyle to Normal.

And Access 2007 doesn't have a multiple objects selection field in the Conditional Formatting window. The closest it comes to any sort multiple indication is having garbled fields in the Conditional Formatting window when selecting a row of objects after setting a condition on one of the objects inside the row.

This didn't highlight the row, but it highlighted all of the fields in the row, which is close enough. And the user was ecstatic that I grayed the font somewhat to make the row a slight struggle to read. We don't want people using workarounds for issues that have been resolved.

After having spend probably 12 hours on this issue, I didn't want to spend any more time testing the VBA.
Jeffrey CoachmanMIS LiasonCommented:
Now that you mention it, ...I do remember this happening a while back...

I thought MS would have fixed this in a hotfix or something....
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.