Conditional Formatting Dispaly On Load?

[Twitchie]

I'm using Access 2010. I set up conditional formatting in some areas of my report. They're very simple like if a cell is between 0 and .4, then the background color of the field is red. They work just fine...when I click on the field. I don't remember ever running into this issue in the past. I've been reading potential solutions on the web, but haven't found a way to make the conditional formatting rules kick in when the report is loaded. Any ideas? Many thanks!

[ItsMe]

I use VBA to format my reports. I use the On Format event. Doing it this way helps to insure the report prints as I want it formatted.

[Twitchie]

I use VBA to format my reports. I use the On Format event. Doing it this way helps to insure the report prints as I want it formatted.

What would the VBA look like? I didn't spend much time with VBA - kind of skipped that when I stopped doing as much in Access and started learning SQL.

[ItsMe]

The trick in reports is getting the correct event. Yes, you want format but, you need to use the correct format handler for the correct section ie, detail, footer, etc. Also, sometimes the value or variable you need to validate is not available in the same "Format Section". If the control you want to format conditionally is in an area other than the field that has the variable, you will need to declare a variable that is available privately throughout the report's module.

It is a lot of words but it is stuff that needs to be said. For starters, you could try some hardcode stuff. Just to test the formatting of your control and make sure you have the correct "format section".

Something like this....

If me.controlname.value = 0 then
me.controlname.forecolor = 255
else
me.controlname.forecolor = 0
end if

[Twitchie]

Greetings. The fields are in the detail section of the report. Each field is calculated in the field's Control Source. For instance, for one field, the formula is =Abs(Nz([Task_1_1_Flag],0)+Nz([Task_1_2_Flag],0))/2. I display it as a percentage. I then have conditional formatting (using the Conditional Formatting button on the ribbon) that has 4 ranges. If the calculation falls within a particular range, the cell turns a certain color. When i click the cell in the report (giving it focus) the formatting is applied perfectly. I'd just like all of the calculated cells to display this conditional formatting on load of the report and not just when a particular field has focus.

[ItsMe]

I would bring the calculations into the VBA module. You can do the calculations in the Report's On Load event. You will need to declare a variable in the VBA module's "General Declarations" area (Module Header).

In the header of the module you will need to declare a variable. Since it will be a percentage, the data type "Double" should be suitable. At the top of the module and below the line "Option Compare Database", type something like....

Dim dblMyCalc as Double

Then you will need to run the calc when the report loads. PLace something like the following in the Report's On Load Event.

dblMyCalc = Abs(Nz([Task_1_1_Flag],0)+Nz([Task_1_2_Flag],0))/2

Now, when the report loads, the calc will be held in the variable dblMyCalc. The only thing left to do is get this calculation to your unbound control using the format event for the detail where the control resides.

Me.UnboundControl.value = dblMyCalc

'and some additional formatting

if dblMyCalc = 0 then
Me.UnboundControl.forecolor = 255
else
Me.UnboundControl.forecolor = 0
end if

[Twitchie]

Ok, I'll give that a shot. Reminds me of C++ in college. I have 23 calculations on this report. I would need to declare a unique variable for each one, correct? I have tried this expression in the On Load, On Click, and On Focus areas, sadly to no avail. The field calculates fine, just no special formatting.

=IIf([Task1Percentage] Between 0.9 And 1,[Task1Percentage].[BackColor]=65280,[Task1Percentage].[BackColor]=3937500)

[ItsMe]

Each calc will need a variable declared. When you place an expression inside a control, there is not a need for a variable (bucket). The control acts as the bucket. This is why you just type = and then the expression.

So you will need some sort of variable in front of your instant if

MyVar = iif()

Of course you will want to declare a variable of the correct data type.

dim lngColor as long

lngColor = iif()

[Twitchie]

Ok, I defined my variable and added the other snipets...I think:

When I run the report, the unbound control doesn't calculate or anything. What'd I mess up?

[ItsMe]

Looks good at a glance. I have not dissected your expression. Try this.....

Instead of MyReport.Task1Percentage use..... Me.Task1Percentage

Also, you can assign a constant to your double to to isolate your expression and test the other parts of your code.

dblTask1Calc = 200 'Just for testing

[Twitchie]

I changed the VBA to this, but when the report loads, 200 isn't put in my unbound control. Is it supposed to have a Control Source?

[ItsMe]

Good point. You need to clear all text from within the control named Task1Percentage. Afterwards, it should read "Unbound". If you are still having trouble, I will mock up some report formatting examples.

[Twitchie]

Yeah, it says Unbound when in design mode. When I run the report, the field is just empty. I just wanted to be sure there shouldn't be anything in Control Source since we were defining a variable and linking it to the name of that field.

[June7]

I find it very odd that the conditional formatting does not automatically display. It works just fine for me in either ReportView or PrintPreview. Does this issue present with all controls, other reports, or another db?

[Twitchie]

I find it very odd that the conditional formatting does not automatically display. It works just fine for me in either ReportView or PrintPreview. Does this issue present with all controls, other reports, or another db?

With the VBA ItsMe has helped me with, the field calculates in Print Preview, but not Report View. The formatting colors appear when the field is clicked (has focus) in Report View, but not until then. And when the field loses focus, the color formatting disappears. I have not used this in another database. I've done more conditional formatting in Forms and a lot more in Excel. Is it normal for the calculation to work only in Print Preview and not Report View?