By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,041 Members | 1,858 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,041 IT Pros & Developers. It's quick & easy.

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

P: 14
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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
  2.     ' Set color coding of field based on value
  4.     Dim intSafetyFundNum As Integer
  6.     If Not IsNull(Me.txt_SafetyFundingNumber.Value) Then
  7.         intSafetyFundNum = Me.txt_SafetyFundingNumber.Value
  8.     Else
  9.         Exit Sub
  10.     End If
  12.     If intSafetyFundNum <= 5 Then
  13.     Me.txt_SafetyFundingNumber.Report.FillColor = 3
  14.     Me.txt_SafetyFundingNumber.Report.ForeColor = 1
  16.     End If
  18. 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!
Attached Files
File Type: zip (1.02 MB, 62 views)
May 5 '10 #1
Share this Question
Share on Google+
8 Replies

P: 14
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. :)
Expand|Select|Wrap|Line Numbers
  1. If txtSafeFundNum <= 5 Then
  2. txtSafeFundNum.BackColor = vbRed
  3. txtSafeFundNum.ForeColor = vbWhite
  4. ElseIf txtSafeFundNum <= 9 And txtSafeFundNum > 5 Then
  5. txtSafeFundNum.BackColor = vbYellow
  6. txtSafeFundNum.ForeColor = vbBlack
  7. ElseIf txtSafeFundNum <= 14 And txtSafeFundNum > 9 Then
  8. txtSafeFundNum.BackColor = vbBlue
  9. txtSafeFundNum.ForeColor = vbWhite
  10. ElseIf txtSafeFundNum <= 20 And txtSafeFundNum > 14 Then
  11. txtSafeFundNum.BackColor = RGB(51, 102, 0)
  12. txtSafeFundNum.ForeColor = vbWhite
  13. Else
  14. txtSafeFundNum.BackStyle = 1
  15. txtSafeFundNum.ForeColor = vbBlack
  16. End If
May 6 '10 #2

Expert Mod 15k+
P: 31,494
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?
May 6 '10 #3

Expert Mod 15k+
P: 31,494
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).
May 6 '10 #4

P: 14
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:

Expand|Select|Wrap|Line Numbers
  2. Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
  4. On Error Resume Next
  6. txtSafeFundNum.BackStyle = 1
  8. If txtSafeFundNum <= 5 Then
  9. txtSafeFundNum.BackColor = vbRed
  10. txtSafeFundNum.ForeColor = vbWhite
  11. ElseIf txtSafeFundNum <= 9 And txtSafeFundNum > 5 Then
  12. txtSafeFundNum.BackColor = vbYellow
  13. txtSafeFundNum.ForeColor = vbBlack
  14. ElseIf txtSafeFundNum <= 14 And txtSafeFundNum > 9 Then
  15. txtSafeFundNum.BackColor = vbBlue
  16. txtSafeFundNum.ForeColor = vbWhite
  17. ElseIf txtSafeFundNum <= 20 And txtSafeFundNum > 14 Then
  18. txtSafeFundNum.BackColor = RGB(51, 102, 0)
  19. txtSafeFundNum.ForeColor = vbWhite
  20. Else
  21. txtSafeFundNum.BackStyle = 0
  23. End If
  25. 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! ;)
May 6 '10 #5

P: 14
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! :)
May 6 '10 #6

Expert Mod 15k+
P: 31,494
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.
May 6 '10 #7

P: 14
Except that I need 4 conditions, and the built-in Conditional Formatting tool is limited to 3. I wish it had been that easy! :)
May 6 '10 #8

Expert Mod 15k+
P: 31,494
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.
May 6 '10 #9

Post your reply

Sign in to post your reply or Sign up for a free account.