If row type is New, set a controls' background to different color from other rows on an Access 2003 Report

Sandra Smith
Sandra Smith used Ask the Experts™
on
I only the udpate text control to change its background color if the type for that row is "new".  So, I have three controls in the detail, Task Title, Update and Type,  If the Type is "new", I want the background for Update to be blue - is this even possible?  That is, change the background for one control in a row based on a value of another control in that row?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Create code for the OnFormat event of the Detail section :


Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If FormatCount = 1 Then
    If Me![Type] = "new" Then
        Me![Update].BackColor = vbBlue
    Else
        Me![Update].BackColor = vbWhite
    End If
End If
End Sub
Sandra SmithRetired

Author

Commented:
I copied your code directly into the report, but does not work.  
Sandra SmithRetired

Author

Commented:
I am thinking that since this is a report, perhaps it actually cannot be done?  that is, format woud be at the row level, not a control level within the row?
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

It's totally possible, but just pasting the code may not connect it automatically. To be sure:

Open the report in design view. Press F4 to show the Properties list if it is not showing already. Click on the Detail section (not Report Header, Page Header, any footer, or any control. On the Events tab of the Properties list, look at On Format. Drop down the box and choose [Event Procedure]. Then press the . . . button. This will open the VBA editor. The function skeleton will be laid out for you.

The Detail section's OnFormat event fires one or more times for each record in the report.
Don't worry about the reasoning for the outer If-End If.
You'll need to adjust the syntax of lines 3 - 7 to match the names of the textboxes on your Report.

HTH,
pT72
It's possible that pasting the code straight into the module without compiling it may not set the OnFormat property of the Detail section. However, following the above events should "wire" the code to the actual event. Also, in re 36320030, the Detail_OnFormat occurs once or more before any control within it is drawn (print preview) or printed. It's not too late, I assure you.

pT72
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012
Commented:
<I copied your code directly into the report, but does not work.  >

1. The Default View for opening Reports in Access 2007-2010 is "Report View".
Detail_Format code will not run in Report View.
You must open the Report Explicitly in "Print Preview" in order for the code to run and to see the effect.

AND...

2. Also make sure that the "Back Style" property of the control is set to "Normal"
(Transparent is the default in many reports)
Any color change to the Backcolor will not be apparent unless the BackStyle is set to "Normal"

JeffCoachman
Sandra SmithRetired

Author

Commented:
Let me try again and unfortunately, they are still on ACCESS 2003.
Sandra SmithRetired

Author

Commented:
I have not forgottent his question, just had a slight medical emergency and have been out of the office.  Will address this when I get into work tihs morning.  Sorry about the time lapse.  Now, does anyone know how long it takes to heal a broken leg, arm and two toes??
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
See here

;-)
Sandra SmithRetired

Author

Commented:
Thank you both!  I used code from pterrandon72 and the points noted by Jeff got it to work.  This actually now helps to make some other reports more indicative with color as well.  

Sandra

Also, thank for the doctor info, great, 6 months of hobbling......

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial