How does one apply conditional formatting to a textbox in a subreport through code?

[codicecrm]

Hello!
I am trying to apply conditional formatting to several subreports within a main report (Charlie) with code - because I have four conditions.

When the value of txt_SafetyFundingNumber is
1-5, red fill with white font;
6-9, yellow fill with black font;
10-14 blue fill with white font; and
15-20, green fill with white font.

The following is my attempt to apply a red fill with white font when the value of the textbox is <=5.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    ' Set color coding of field based on value
 
    Dim intSafetyFundNum As Integer
 
    If Not IsNull(Me.txt_SafetyFundingNumber.Value) Then
        intSafetyFundNum = Me.txt_SafetyFundingNumber.Value
    Else
        Exit Sub
    End If
 
    If intSafetyFundNum <= 5 Then
    Me.txt_SafetyFundingNumber.Report.FillColor = 3
    Me.txt_SafetyFundingNumber.Report.ForeColor = 1
 
    End If
 
End Sub
 

I've attached the zipped db to take a peek. This is the last piece of a very long and painful process for a newbie! Every time I complete one piece, I hit another wall........I guess this is trial-by-fire?
Thanks so much for any advice anyone cares to give!
:)

[codicecrm]

This is what I found this morning and then modified for my own purposes, which was so much simpler than I thought! It seems to be working. :)

If txtSafeFundNum <= 5 Then
txtSafeFundNum.BackColor = vbRed
txtSafeFundNum.ForeColor = vbWhite
ElseIf txtSafeFundNum <= 9 And txtSafeFundNum > 5 Then
txtSafeFundNum.BackColor = vbYellow
txtSafeFundNum.ForeColor = vbBlack
ElseIf txtSafeFundNum <= 14 And txtSafeFundNum > 9 Then
txtSafeFundNum.BackColor = vbBlue
txtSafeFundNum.ForeColor = vbWhite
ElseIf txtSafeFundNum <= 20 And txtSafeFundNum > 14 Then
txtSafeFundNum.BackColor = RGB(51, 102, 0)
txtSafeFundNum.ForeColor = vbWhite
Else
txtSafeFundNum.BackStyle = 1
txtSafeFundNum.ForeColor = vbBlack
End If

[NeoPa]

May I ask why the Conditional Formatting is not applied in the design of the reports (The reports that are used as subreports in your main report that is) themselves?

[NeoPa]

By the way, the concept you are describing is Conditionally Applied Formatting, as opposed to Conditional Formatting. This will work for some instances, but when the form is in continuous (or Datasheet) view mode, you will find problems (See Why Values in Unbound Form Controls do not Persist).

[codicecrm]

@NeoPa
Unless I'm mistaken, which could very well be, the code does reside in the On Format function of the detail in each (sub)report. Here is what I have so far:

 
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
 
On Error Resume Next
 
txtSafeFundNum.BackStyle = 1
 
If txtSafeFundNum <= 5 Then
txtSafeFundNum.BackColor = vbRed
txtSafeFundNum.ForeColor = vbWhite
ElseIf txtSafeFundNum <= 9 And txtSafeFundNum > 5 Then
txtSafeFundNum.BackColor = vbYellow
txtSafeFundNum.ForeColor = vbBlack
ElseIf txtSafeFundNum <= 14 And txtSafeFundNum > 9 Then
txtSafeFundNum.BackColor = vbBlue
txtSafeFundNum.ForeColor = vbWhite
ElseIf txtSafeFundNum <= 20 And txtSafeFundNum > 14 Then
txtSafeFundNum.BackColor = RGB(51, 102, 0)
txtSafeFundNum.ForeColor = vbWhite
Else
txtSafeFundNum.BackStyle = 0
 
End If
 
End Sub
 

I cannot believe how much I'm learning on-the-fly, thanks to everyone in these forums! It may not be the cleanest or the most efficient, but I have much to learn yet! ;)

[codicecrm]

@NeoPa
Thank you for the warning! This drill is to automate the reports to reduce my hands-on time. I haven't created the input forms for the users yet, but that is my next step - so I may run into problems when I do. I'll be sure to refer to the information you've provided.
I've also thought about creating CF pages to allow for user entry and report automation, but that will be another project entirely! :)

[NeoPa]

I would certainly recommend you use Conditional Formatting within your report. In design mode select Format (menu) / Conditional Formatting... then configure the control as you require. This will avoid the problems associated with persisting controls and is also easier to do. Avoiding the requirement for code as it does.

[codicecrm]

Except that I need 4 conditions, and the built-in Conditional Formatting tool is limited to 3. I wish it had been that easy! :)

[NeoPa]

That's a bit of a pain. I suspect you're spot on with that though (except it supports 4 & you have 5 - including the default (or rest) of course. Although you nowhere say how you want >20 handled).

Conditionally Applied Formatting should work for you then, but it means you must apply the formatting at format time for each line. This you are doing, so all should be well.

BTW If >20 is not possible we can use Conditional Formatting with 3 specified conditions and the original format as a catch-all.