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

Acc2K - Shading rows in report if criteria is met.

MindBender77
100+
P: 234
Hello All,
I'm not sure if this is possible and have been unsuccessful in my attempts.

Scenerio: I have a report with several fields. If the data in field X = Y then shade the entire row.

I've been attempting to loop through the report using the following in the OnFormat Event:
Expand|Select|Wrap|Line Numbers
  1. For Each ctl In Me.Section(0).Controls
  2.     If ctl.Column(4) = "text string here" Then
  3.         Me.Section(0).BackColor = RGB(255, 255, 255)
  4.     Else
  5.         Me.Section(0).BackColor = RGB(192, 192, 192)
  6.     End If
  7. Next
  8.  
Is what I'm attempting even possible in a report?
Bender
Sep 17 '08 #1
Share this Question
Share on Google+
4 Replies


Megalog
Expert 100+
P: 378
I've used Conditional Formatting to do this. I'm not sure if there's a way though VBA to apply the entire row backcolor at once.

So in your case, you might want to try highlighting the cells in the row you want to format, go to Conditional Formatting, and select "Expression is:"
Then put in your condition: [fieldname]="text string here"
Apply the font color/style, or background fill color, and see if it looks good enough for you.
Sep 17 '08 #2

FishVal
Expert 2.5K+
P: 2,653
Something like the following:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
  2.     ....
  3.     If X = Y Then Me.Detail.BackColor = ....
  4.     .....
  5. End Sub
  6.  
Sep 17 '08 #3

MindBender77
100+
P: 234
I've used Conditional Formatting to do this. I'm not sure if there's a way though VBA to apply the entire row backcolor at once.
Megalog,
Thank you for your quick reply. However, the problem with using conditional formatting is that there are 11 text strings to search for. CF allows for only 3 which is why was I trying this using VBA.

Bender
Sep 17 '08 #4

MindBender77
100+
P: 234
Thank You All, for your assistance.

With some minor changes I was able to shade the rows in the report. Here is the code in case it can assist others.
Expand|Select|Wrap|Line Numbers
  1. Private Sub Detail_Format(Cancel as Integer, FormatCount as integer)
  2. For Each rec In Me.Reports.Controls
  3.     If X = Y Then
  4.         Me.Section(0).BackColor = RGB(192, 192, 192)
  5.     Else
  6.         Me.Section(0).BackColor = RGB(255, 255, 255)
  7.     End If
  8. Next
  9.  
Thanks Again,
Bender
Sep 17 '08 #5

Post your reply

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