Conditional Formatting via Code

[ant1983]

Hi,

posted this but couldnt quite figure out the code i was given.

Please can someone help: i have a report and want a certain textbox
numPercentageBooked shaded one of 4 colours depending on the value. Values
below:

0% to 65% = Red
66% to 99% = Yellow
100% = Green
More that 100% = Blue

Was thinking of doing it on the the form load event but have no idea what to
write being a novice

Ta

 

[Dirk Goldgar]

Hi,

posted this but couldnt quite figure out the code i was given.

Please can someone help: i have a report and want a certain textbox
numPercentageBooked shaded one of 4 colours depending on the value.
Values
below:

0% to 65% = Red
66% to 99% = Yellow
100% = Green
More that 100% = Blue

Was thinking of doing it on the the form load event but have no idea what
to
write being a novice

Why do this by code? Why not use the built-in conditional formatting? I
know you have four conditions, not three, but one of them can be your
default state. If you had five conditions, *then* you couldn't use
conditional formatting.

Is the field, numPercentageBooked, a floating-point field (single or
double), so it actually has value ranges like these:

0 - .65 --> Red
.65000001 - .99999999 --> Yellow
1.0 - 1.0 --> Green

1.0 --> Blue

?

One problem you may run into is that floating-point numbers are inherently
imprecise, especially if they are the result of a calculation. So you need
to decide how close the number must be to one of your boundary values to be
considered equal to it. For example, you might choose to express your
conditions like this:

Null or <=.650001

.650001 And <= .999999
.999999 And <= 1.000001
1.000001

I picked those values pretty arbitrarily, but you get the idea.

If you want to do this in code, not via conditional formatting, then you
should use the form's Current event to apply the formatting. However, that
will really only work the way you want when the form is in single-form view,
so the user only sees the record that is current. On continuous forms,
where the user sees many records that are not current, conditional
formatting is the only simple solution.

 

[Dirk Goldgar]

If you want to do this in code, not via conditional formatting, then you
should use the form's Current event to apply the formatting. However,
that will really only work the way you want when the form is in
single-form view, so the user only sees the record that is current. On
continuous forms, where the user sees many records that are not current,
conditional formatting is the only simple solution.

CORRECTION
I just realized you said "report", not "form". Therefore the above
paragraph is totally wrong. For a report, to do it in code use the
Detail_Format event, and don't worry about what I said regrding the
continuous forms and current records. For example,

'------ start of example code ------
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

With Me.numPercentageBooked
Select Case .Value
Case 0 To 0.650001: .BackColor = vbRed
Case Is <= 0.999999: .BackColor = vbYellow
Case Is <= 1.000001: .BackColor = vbGreen
Case Is > 1.000001: .BackColor = vbBlue
Case Else: .BackColor = vbRed
End Select
End With

End Sub
'------ end of example code ------

 

[ant1983]

Er,..... Wheres the detail format event?

CORRECTION
I just realized you said "report", not "form". Therefore the above
paragraph is totally wrong. For a report, to do it in code use the
Detail_Format event, and don't worry about what I said regrding the
continuous forms and current records. For example,

'------ start of example code ------
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

With Me.numPercentageBooked
Select Case .Value
Case 0 To 0.650001: .BackColor = vbRed
Case Is <= 0.999999: .BackColor = vbYellow
Case Is <= 1.000001: .BackColor = vbGreen
Case Is > 1.000001: .BackColor = vbBlue
Case Else: .BackColor = vbRed
End Select
End With

End Sub
'------ end of example code ------

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)

 

[Dirk Goldgar]

Er,..... Wheres the detail format event?

It's an event for the report's Detail section. In design view, if you
select the Detail section and open its property sheet, on the Event tab of
the property sheet you'll find the "On Format" property where you can set
behavior for the Detail event.

 

[Dirk Goldgar]

I[...] on the Event tab of the property sheet you'll find the "On Format"
property where you can set behavior for the Detail event.

Sorry, that should have been "... for the Format event."

 

[ant1983]

LOL Thanks...

It's an event for the report's Detail section. In design view, if you
select the Detail section and open its property sheet, on the Event tab of
the property sheet you'll find the "On Format" property where you can set
behavior for the Detail event.

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)