Access 2007 Conditional Formating

[firebug88]

I would like to be able to apply conditional formating to my report but am having trouble with the VBA code. What i would like to do is apply a condition to several Text boxes in the details section of my report. I want to be able to highlight in yellow the max value in each text box source (10 text box columns to be exact) and to highlight the min value to blue.

I have been able to right click on the text box and set each individual text box with the conditions:

value = Max([field1]) - back color yellow
value = Min([field1]) - back color blue

I would like to apply a vba code to each text box in case i need to change colors or add conditions. Can anyone suggest a simple vba code to do this?

 

[ranman256]

did you do this in VB code, or using the CONDITIONAL FORMATTING section of the report?

if VB code, youd place it in the DETAIL band , ON PRINT event.
but you dont need code if you use the condit. format. section.

 

[firebug88]

I can use the conditional formatting section of the report, but i would rather do this in VBA for several reasons, main reason being for simple changes that effect all fields. Also, i dont want it to only be on print as this report will never be printed, just viewed in report view.

 

[firebug88]

Ok so i have a code that half works but it is making everything black :

maxx = DMax("field1","Query1")
minx = DMin("field1","Query1")

If Me.field1 = maxx Then
Me.field1.BackColor = vbBlack
ElseIf Me.field1 = minx Then
Me.field1.BackColor = vbBlue
Else
Me.field1.BackColor = vbWhite

 

[Micron]

I don't use elseif and else like this so I'm guessing something about your code is not allowing the white property to be set. I presume the report wizard built your report as it looks like the control name and query name are the same. Change "yourfield" as needed. This can cause issues when trying to do calculations in report footers, so beware of it.
Try this:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim maxx As Date, minx As Date
Dim ctl As Control

maxx = DMax("yourField", "Query1")
minx = DMin("yourField", "Query1")

Set ctl = Me.Field1
ctl.BackStyle = 1 'default is transparent, so set it to 'normal'
Select Case ctl.Value
    Case Is = maxx
        ctl.BackColor = vbBlack
    Case Is = minx
        ctl.BackColor = vbBlue
    Case Else
        ctl.BackColor = vbWhite
End Select

End Sub

 

[firebug88]

Hello again Micron. so the code works but only if i go into print preview. Is there anyway to make this formating work in report view? Also, the report is all custom and actually pretty complex, but I cannot post any specific code due to the confidentiality of it, thus the dummy variables.

Thanks for all the help and patience.

 

[Micron]

Add it to the Paint event of the detail section as well. Not sure if you need it in both. Could you test that and let us know?
Thanks.

 

[firebug88]

When I add it to the paint section it technically works, but everytime i scroll down the page it becomes extreamly laggy and has to re-paint the entire details section over and over again. As I couldn't find a better solution as of yet, I had to use the conditional formating properties (not VBA) and set each field individually. Although not perfered, I guess its the best method for what I want to do?

 

[Micron]

Sound like. The manipulation was meant to be executed in the print preview mode AFAIK. The paint thing was a work-around for your preference to see the data in report view. To the best of my recollection, I've never bothered with this view. The whole concept of reports (where I worked for many years) was to have a printable document for meetings, emailing or whatever. Anything that needed a "report" type of view was handled by a form.