CONDITIONAL FORMATTING AN ACCESS REPORT 2

[harrisharris]

Good Morning,
I want to use Conditional Formatting on a report by clicking on the text box that has the condition (eg a number) and then depending on the number , I would like the font colour of all the other text boxes in that row to change as well. I have tried it, but am only able to get the original text box to change, is there a way to get the rest of the row to change as well?

Many thanks

 

[PaulBricker]

I'm curious how you manage to get even the one textbox to change. There is no click event for a Report textbox, actually there's no Click Event in a report anyplace that I can see. Can you share a little more about what you are doing.

Paul

 

[harrisharris]

Morning Paul,
Thanks for coming back on this one.
My explanation was probably as good as my knowledge of Access.
Situation is :
I have a report which for simplicity's sake has three columns - Scheme, Location and Status. The Status column is populated with a different number for each row (depending on the status). If for example a row has a status number of 4, I would like all the text in the other two columns of that row to change to red.

What I did was go into design view, select the text box for status, go Format, Conditional Formatting, Field Value is +4 and then selected Font Red. Back to view report and where the status number is 4, its now red font. What I would like is for the other information in the same row to be red font as well.

Hope this is clear

reagrds

Laurence

 

[PaulBricker]

That does clear it up Laurence. In Access Reports, you can format records more efficiently using VBA and Format Events. Here is what you do.
1. Open the Report in Design View.
2. Assuming your data is in the Detail Section, click on the Detail bar and open the properties box for that section.
3. Look for the On Format Event in the properties box
4. CLick on the event line. You will see a box with 3 dots. Click on that and select Code Builder from the menu. This will open the On Format event for the Detail section.
5. In between the lines
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
and
End Sub
you will put the code that will format your records. It will look like this (you will need to change the names of your textboxes to whatever your textbox names actually are).

If Me.Status = 4 Then
Me.Scheme.ForeColor = vbRed
Me.Location.ForeColor = vbRed
Me.Status.ForeColor = vbRed
Else
Me.Scheme.ForeColor = vbBlack
Me.Location.ForeColor = vbBlack
Me.Status.ForeColor = vbBlack
End If

Now you can expand this code to include other Status value by using an ElseIf statement like this

If Me.Status = 4 Then
Me.Scheme.ForeColor = vbRed
Me.Location.ForeColor = vbRed
Me.Status.ForeColor = vbRed
ElseIf Me.Status = 6 Then
Me.Scheme.ForeColor = vbBlue
Me.Location.ForeColor = vbBlue
Me.Status.ForeColor = vbBlue
Else
Me.Scheme.ForeColor = vbBlack
Me.Location.ForeColor = vbBlack
Me.Status.ForeColor = vbBlack
End If


Post back with specific problems.

Paul

 

[harrisharris]

Thanks Paul - after a few problems (for instance some of my Text boxe names had a space or number - I have since adjusted them) it works brilliantly.

As a newcomer to using code, what is the significance of the "Me."?

Your help is as always much appreciated.

Regards
Laurence

 

[PaulBricker]

The Me keyword acts as an intrinsic variable. That means that it is a variable that always exists(doesn't need to be declared) and holds the name of the current Form or Report.
So no matter what form/report you have open, if you want to refer to a property or object of that form/report the Me keyword can be used instead of
Forms!FormName

It's quite possible that your code could have done without the Me. keyword but I always use it, mostly because when you type Me. (Me(dot)) you get a drop down list of objects and properties for the form/report that include the field names. Then I just select the Field name from the list and I don't have to worry about typo's or brackets.
HTH
Paul

 

[harrisharris]

Thanks again and cheers until (I am sure) next time.

laurence