469,342 Members | 6,653 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,342 developers. It's quick & easy.

Conditional Format in Report Limit

9
I'm using VBA to apply Conditional Formatting in a report. I've created code to apply formatting to 31 fields called Date1, Date2, ...Date31 representing the 31 days of a month, for each person in a database. Each of the date fields are setup as strings, and can have up to seven different codes assigned, which will be indicated by different colors on the report using Conditional Format code.

I wrote a for/next loop to apply the conditional formatting to each of the Date(#) fields and it works great, if I only loop up to twelve times. If I loop thirteen times or more, the program errors out on different fields. Error meg: Run time error '438': Object doesn't support this property or method.

I coded each Date(#) field individually and again, after entering into the Date13 code, it errors.

Why does it stop after formatting 12 fields?

I tried to attach a piece of the code (I see there is a max of 20 lines) but new here and I don't know if I got it right.


Expand|Select|Wrap|Line Numbers
  1. For x = 1 To 12
  2. If Me("date" & x) = "LL" Or Me("date" & x) = "PA" Or Me("date" & x) = "PS" _
  3. Or Me("date" & x) = "AS" Or Me("date" & x) = "P" Or Me("date" & x) = "A" Or Me("date" & x) = "S" Then
  4.  
  5.     If Me("date" & x) = "LL" Then
  6.         Me("date" & x).BackColor = vbBlack
  7.         Me("date" & x).ForeColor = vbBlack
  8.     End If
  9.     If Me("date" & x) = "PA" Then
  10.         Me("date" & x).BackColor = vbCyan
  11.         Me("date" & x).ForeColor = vbCyan
  12.     End If
  13.     If Me("date" & x) = "PS" Then
  14.         Me("date" & x).BackColor = vbYellow
  15.         Me("date" & x).ForeColor = vbYellow
  16.     End If
  17.  
  18. ' ...etc
I think I attach a file to show an example of a piece of the report output that works. I would appreciate anyone's help!

PS. I guess it would help if you would know what I am using. I am using Microsoft Access 2007 on Windows XP.

Attached Images
File Type: jpg Report Sample.jpg (73.4 KB, 5860 views)
Oct 13 '11 #1

✓ answered by NeoPa

Rabbit:
I don't see an obvious reason for the bug. But for my peace of mind, can you double check the control names of the ones that aren't getting formatted?
I'm afraid you could have saved yourself some effort if you'd paid more attention to Rabbit's comment Art (It was less than 24 hours ago so you may be looking into it as I post of course).

I found the problem, and it was very simply that all controls after [Date12] had the correct reference in Control Source to the requisite field, but were named as [Text?] where ? started at 70 and went up to 88.

Referring back to your OP (Original Post) it seems you referred to the items as fields. Actually they are controls which reflect the values of (are bound to) fields. The fields referred to fit the name format you describe, but the controls don't. A perfect example of where the correct terminology matters a great deal (not that we expect everyone to know the correct terminology of course, but it does make it worthwhile to comment on it when we can - and I did in post #4).

It is possible to change the code to handle this ignoring the names, but I strongly suggest the result would be better if the names of the controls were brought into line with the standard you have set. Whatever you decide the problem is now solved ;-)

16 3940
NeoPa
32,182 Expert Mod 16PB
There's so little that makes sense in your question it's hard to know what to say.

Firstly, why not use ConditionalFormatting in the design of the controls instead of coding them in your ??? routine?

Where does the idea come from that only 20 lines of code are possible?

I have no idea why it stops formatting after 12 fields. I have very little idea of what you're talking about so that's not too much of a surprise. You've put a lot into the question clearly, but not enough consideration to make it make much sense (so points for trying but more effort needed).

I suggest you try out the ConditionalFormatting idea first. If that doesn't work for you, come back and give us something better to work with than what you have so far and we'll see what we can do.

PS. All the coloured controls in your picture appear to have no contents. As an example it seems to contradict everything said in your question. That won't make it easy for anyone to understand what you're after.
Oct 13 '11 #2
art502
9
First, thanks for attempting to help me. I apoligize for not being more clear. This is the first time trying to code a report for Conditional Formatting, so I may not be doing it the right way, but I did get the desired results, as shown on the attachment. If there is a better way, I would be most grateful to you for your assistence.

To answer your first question, I did that to start with, but it only accepted 3 conditions per control. I have 7 different conditions for each control, producing 7 possible colors depending on the codes applied to each field in the table.

To your second question, being brand new here I was following the instructions about entering programmers code, which was displayed when I clicked on the "Ask a Question" button. It told me to enter the code between the code designators, and to limit the code to 20 lines. If I can enter more, I'll supply the entire code as it is about double what I gave and not that big.

As for the rest, WOW. I tried to be clear, but I obviously am not very good at communicating. As a foundation, I'll attach a Design View of the report. You already have the final output picture and I'll include the entire code for the report.

I have a table that the report is using, that has 33 fields, first is "Name" with a field length of 30, and 31 fields representating the 31 days of the month, each are strings having a field length of 2, and are filled with codes previously established through several other programs. Each of these 31 fields are names Date1, Date2, Date3, etc., through Date31. And the lastfield is Month Selected for a string length of 10. All fields are string.

On the Report Design View attached, you will see a control for each of the table fields listed above. This creates a line on the report for each individual in the table, along with the codes previously assigned to each of the 31 controls. Not all of the 31 controls will have data assigned.

What I am attempting to produce is a matrix by reading the code assigned to the 31 controls and change the foreground and background colors to produce a solid color, which is determined by the code assigned (LL=Black, PA=Cyan, PS=Yellow, AS=Magenta,P=Green, A=Blue, S=Red as shown in the code supplied) Most of the table fields are filled with "P"s and only the Date1 control has an example of all the codes, shown by all the colors on in the first row of the report output example supplied.

Using the loop to address each of the 31 "Date" controls, I am able to successfully post the appropriate colors for the code in the table's field, but as said, when it goes into the thirteenth loop or Day 13 on the report, Access 2007 throws the error previously stated. The majority of the fields in the table are populated with a "P" the will be shown on the report as a Green block, or the "P" will show where the code didn't execute to change the color. Oh, if the field is blank, the "Else" statement changes the color back to Black foreground and white background. Without this statement a previous color replicates.

Expand|Select|Wrap|Line Numbers
  1. Option Explicit
  2. Option Compare Text
  3.  
  4. Private Sub SetFormattingControl()
  5.  
  6. Dim StrDate As String
  7. Dim StrDatePre As String
  8. Dim x As Integer
  9.  
  10. For x = 1 To 12
  11. If Me("date" & x) = "LL" Or Me("date" & x) = "PA" Or Me("date" & x) = "PS" _
  12. Or Me("date" & x) = "AS" Or Me("date" & x) = "P" Or Me("date" & x) = "A" Or Me("date" & x) = "S" Then
  13.  
  14.     If Me("date" & x) = "LL" Then
  15.         Me("date" & x).BackColor = vbBlack
  16.         Me("date" & x).ForeColor = vbBlack
  17.     End If
  18.     If Me("date" & x) = "PA" Then
  19.         Me("date" & x).BackColor = vbCyan
  20.         Me("date" & x).ForeColor = vbCyan
  21.     End If
  22.     If Me("date" & x) = "PS" Then
  23.         Me("date" & x).BackColor = vbYellow
  24.         Me("date" & x).ForeColor = vbYellow
  25.     End If
  26.     If Me("date" & x) = "AS" Then
  27.         Me("date" & x).BackColor = vbMagenta
  28.         Me("date" & x).ForeColor = vbMagenta
  29.     End If
  30.     If Me("date" & x) = "P" Then
  31.         Me("date" & x).BackColor = vbGreen
  32.         Me("date" & x).ForeColor = vbGreen  ' No vb code for dark green use: 4227072
  33.     End If
  34.     If Me("date" & x) = "A" Then
  35.         Me("date" & x).BackColor = vbBlue
  36.         Me("date" & x).ForeColor = vbBlue
  37.     End If
  38.     If Me("date" & x) = "S" Then
  39.         Me("date" & x).BackColor = vbRed
  40.         Me("date" & x).ForeColor = vbRed
  41.     End If
  42. Else
  43.     If IsNull(Me("date" & x)) Then
  44.         Me("date" & x).BackColor = 16777215
  45.         Me("date" & x).ForeColor = vbBlack
  46.     End If
  47. End If
  48. Next x
  49. End Sub
  50.  
  51. Private Sub detail_format(Cancel As Integer, formatcount As Integer) 'shows color on the printout
  52.     SetFormattingControl
  53. End Sub
  54.  
  55. Private Sub detail_paint() 'Shows color on the Report View.
  56.     SetFormattingControl
  57. End Sub

Attached Images
File Type: jpg Report Design View.jpg (51.9 KB, 1235 views)
Oct 13 '11 #3
NeoPa
32,182 Expert Mod 16PB
Art502:
As for the rest, WOW. I tried to be clear, but I obviously am not very good at communicating
That's what I would have said, frankly, after the first post. Not that it's much of a criticism as most people are pretty poor when it comes to that to be honest. Now I've seen your more recent post though, I find I have to change my opinion somewhat (read considerably). It seems to be a picture of clarity and an example of how transferring info can be done if our mind is put to it. For that level of effort and clarity I can go further with this (A because I respond well to effort and B because it's now so much easier to understand what I'm working with) than I expected earlier in the day, for sure.

Firstly, you can ignore my PS. I can now see more clearly there was no contradiction. I simply misunderstood your code and failed to appreciate that as both foreground and background colours were being set, the value, though still there, would not show up.

Next I'd like to comment on the use of a separate procedure which is called by both of the event procedures. I so often try to get that concept across to people, but I'm quite unused to finding it already in evidence. Further impressed.

Now we get closer to the crux of the matter. You have a line of code (Line #10) which explicitly limits the loop to the first 12 columns of data relative to the first twelve days of any month. I will work on the basis that this isn't related to the problem reported, and you only showed the code in this form as it's what you've already got to work reliably.

Having said that, I would consider recoding much of what's there anyway, to enable the code to match your requirements more easily (Less code - less effort).

Expand|Select|Wrap|Line Numbers
  1. Option Explicit
  2. Option Compare Text
  3.  
  4. Private Sub detail_format(Cancel As Integer, formatcount As Integer) 'shows color on the printout
  5.     Call SetFormattingControl
  6. End Sub
  7.  
  8. Private Sub detail_paint() 'Shows color on the Report View.
  9.     Call SetFormattingControl
  10. End Sub
  11.  
  12. Private Sub SetFormattingControl()
  13.     Dim lngCol As Long
  14.     Dim ctl As Control
  15.  
  16.     With Me
  17.         For Each ctl In .Controls
  18.             If Left(.Name, 4) = "Date" Then
  19.                 Select Case .Value
  20.                 Case "LL"
  21.                     lngCol = vbBlack
  22.                 Case "PA"
  23.                     lngCol = vbCyan
  24.                 Case "PS"
  25.                     lngCol = vbYellow
  26.                 Case "AS"
  27.                     lngCol = vbMagenta
  28.                 Case "P"
  29.                     lngCol = vbGreen
  30.                 Case "A"
  31.                     lngCol = vbBlue
  32.                 Case "S"
  33.                     lngCol = vbRed
  34.                 Case Else
  35.                     lngCol = vbWhite
  36.                     .ForeColor = vbBlack
  37.                 End Select
  38.                 .BackColor = lngCol
  39.                 If lngCol <> vbWhite Then .ForeColor = lngCol
  40.             End If
  41.         Next ctl
  42.     End With
  43. End Sub
PS. It may help to understand better if I explain that reports display their data via controls. The record source of a report has fields. The two are not the same.
Oct 13 '11 #4
art502
9
NeoPa, Thanks for your help on this. I have been out the past three days so I just got your reply. You are correct about line 10 where I have explicitly limited the loop to the first 12 columns as this is what worked, if I were to place 13 or more (31) in this line, the application would throw the error, described earlier, and shut down. I had no idea why this was happening, which is why I asked for help.

I will follow your direction and will let you know how I make out. Thanks again for you assistence in this matter.
Oct 17 '11 #5
NeoPa
32,182 Expert Mod 16PB
I wish you the best of luck Art, and I'll wait for your update.
Oct 17 '11 #6
art502
9
I've been working with the new code to no avail. First upon compiling, the ".Value" in line 19 threw an error stating "Method or Data Member not Found". I tried putting "CTL" in front of it and then line 38 ".Backcolor" kicked up the same error.

I'm thinking maybe I'm missing a Reference, but I have no idea which one. Just to try to execute the program, I put CTL in front of that also, as I assumed this should represent the value of the control.

It compiled but when I ran it, nothing happened.

Using debug, I checked ".Name" and it was reporting the name of the report. Changing it to the CTL threw the above listed error. I used the name of a control and found that the value never changed or cycled. After cycling through the code up to 50 times, the value of the control in line 18 never changed.

No matter what I use in line 18, when executed, it goes from line 18 to line 40 and back again.

Your code seemed so logical, yet it only goes into an infinate loop an it really shouldn't. Again, could I be missing a Reference?

I'm at a total loss now! Shouldn't CTL be cycling through all the controls?
Oct 17 '11 #7
NeoPa
32,182 Expert Mod 16PB
My bad Art. I missed out a small, but significant line. Your guesses show a good understanding of the code, so the difference you see won't surprise you I'm sure. I'm sorry I missed it out. It was a little careless. Unfortunately, I'm coding blind so don't get to test it when it's done (With my experience I shouldn't need to - I slipped up).

BTW I'll repost the whole solution for ease of your use :

Expand|Select|Wrap|Line Numbers
  1. Option Explicit
  2. Option Compare Text
  3.  
  4. Private Sub detail_format(Cancel As Integer, formatcount As Integer) 'shows color on the printout
  5.     Call SetFormattingControl
  6. End Sub
  7.  
  8. Private Sub detail_paint() 'Shows color on the Report View.
  9.     Call SetFormattingControl
  10. End Sub
  11.  
  12. Private Sub SetFormattingControl()
  13.     Dim lngCol As Long
  14.     Dim ctl As Control
  15.  
  16.     For Each ctl In Me.Controls
  17.         With ctl
  18.             If Left(.Name, 4) = "Date" Then
  19.                 Select Case .Value
  20.                 Case "LL"
  21.                     lngCol = vbBlack
  22.                 Case "PA"
  23.                     lngCol = vbCyan
  24.                 Case "PS"
  25.                     lngCol = vbYellow
  26.                 Case "AS"
  27.                     lngCol = vbMagenta
  28.                 Case "P"
  29.                     lngCol = vbGreen
  30.                 Case "A"
  31.                     lngCol = vbBlue
  32.                 Case "S"
  33.                     lngCol = vbRed
  34.                 Case Else
  35.                     lngCol = vbWhite
  36.                 End Select
  37.                 .BackColor = lngCol
  38.                 .ForeColor = IIf(lngCol = vbWhite, vbBlack, lngCol)
  39.             End If
  40.         Next ctl
  41.     End With
  42. End Sub
Let me know how this one goes :-)

PS. In case it wasn't clear, all the things you attempted made sense and the reason none of them worked was the small but fatal flaw in my suggested code.
Oct 17 '11 #8
art502
9
NeoPa, no need to apologize, I just can't tell you how much I really appreciate your help, but...

I have good news and bad. I surprised myself by catching the misplacement of the "With CTL" line before I read your correction, and the code works with the "With Me" line 15 and no "Me" next to controls in line 16, and it also works without the "With Me" line by adding "Me.Controls". I think the latter is cleaner and the way to go.

I noticed that your version looks at every control on the page, including all labels, lines, etc., everything! I originally used the For...Next thinking it would be better to only address those controls needing changed. Performance wise, I've learned that it appears not to matter. I've learned a lot through your help!!!

Your's being cleaner is definately the way to go!

Now for the bad news, I'm right back to where I was in the beginning. The code fills in the first 12 columns of the matrix (first 12 days for each individual as before) and then quits. Your code at least does not give and error as mine did, it just doesn't fill in any more columns with colors. Output looks the same as before (shown at beginning oft his thread).

Any ideas? There seems to be a limit to the number of controls that can be modified at one time, but it's not based on the number of individuals because it is the same for a two person listing, as it is for a 10 person listing. It seems to always stop after column 12. Seems weird.

Where do I go from here? Please help.
Oct 18 '11 #9
Rabbit
12,516 Expert Mod 8TB
I don't see an obvious reason for the bug. But for my peace of mind, can you double check the control names of the ones that aren't getting formatted?
Oct 18 '11 #10
NeoPa
32,182 Expert Mod 16PB
Art:
I noticed that your version looks at every control on the page, including all labels, lines, etc., everything! I originally used the For...Next thinking it would be better to only address those controls needing changed. Performance wise, I've learned that it appears not to matter. I've learned a lot through your help!!!
Just to clarify - I suspect you already understand but clarification seldom hurts - although the For Each loop does indeed process through all the controls on the form, the body of the block consists entirely of an inner If block which only processes the relevant controls. So, the main body of the code is only ever executed for the relevant code, but the loop processing itself is executed for all.

Art:
Where do I go from here? Please help.
At this stage I'm at a loss as to why the controls beyond #12 are behaving as if unprocessed by the code. To go further I think I'd need hands-on access to your database (or a sanitised copy of it at least). If you're happy to attach a copy for me then please follow the instructions found in Attach Database (or other work).

I suspect there is something different about these controls, but something subtle. Asking for you to identify the difference so that I could interpret it would be unlikely to yield results. I suspect that info would have been forthcoming before now if you'd been aware of it. If you can attach your work though, I will be happy to look through it and see if I can identify anything anomalous (and report it here of course).
Oct 18 '11 #11
art502
9
I would be more than happy to submit you the table and report for you to dissect, if I can figure out how.

I see your point on my FOR...NEXT loop logic. Of course you are absolutely right! I just never looked at it that way. I have so much to learn.
Oct 19 '11 #12
art502
9
Attached is a database stripped down to just the problem. Double click on the report. For me it only shows colors up to Day12.

I'm working out of Access 2007, which has been a pain in many ways, but have been running this database as a 2003 database all along. One thing I haven't tried was to convert it to a 2007 database. I'll give this a try and let you know if there is any differences. I suspect not.

Thanks again for everything!
Attached Files
File Type: zip Plot Matrix.zip (36.4 KB, 130 views)
Oct 19 '11 #13
art502
9
I have converted the database to 2007 and everything is still the same. If you're using the actual 2003 program and it works for you, it has to be a problem with the 2007 engine, which wouldn't surprise me.
Oct 19 '11 #14
NeoPa
32,182 Expert Mod 16PB
Rabbit:
I don't see an obvious reason for the bug. But for my peace of mind, can you double check the control names of the ones that aren't getting formatted?
I'm afraid you could have saved yourself some effort if you'd paid more attention to Rabbit's comment Art (It was less than 24 hours ago so you may be looking into it as I post of course).

I found the problem, and it was very simply that all controls after [Date12] had the correct reference in Control Source to the requisite field, but were named as [Text?] where ? started at 70 and went up to 88.

Referring back to your OP (Original Post) it seems you referred to the items as fields. Actually they are controls which reflect the values of (are bound to) fields. The fields referred to fit the name format you describe, but the controls don't. A perfect example of where the correct terminology matters a great deal (not that we expect everyone to know the correct terminology of course, but it does make it worthwhile to comment on it when we can - and I did in post #4).

It is possible to change the code to handle this ignoring the names, but I strongly suggest the result would be better if the names of the controls were brought into line with the standard you have set. Whatever you decide the problem is now solved ;-)
Oct 19 '11 #15
art502
9
UNBELIEVABLE!!! I swear I checked and double check that in the beginning. I feel so ashamed to have wasted your time like this! I do apologize. To overlook something staring me right in the face like that. The more I fussed with it the more things started to blur together.

But I thank you so much!!! To me your help was still very valuable, as I have learned a lot through this exercise and can't thank you enough.

Again THANK YOU for all your help!
Oct 19 '11 #16
NeoPa
32,182 Expert Mod 16PB
That's not a problem Art. I enjoyed the thread, and there were some important points covered during its processing too, which is gratifying for me - especially as you seem happy to take them on board. So, even if the first reason for coming might have been avoided, you came and benefitted and that's all good for me :-)
Oct 19 '11 #17

Post your reply

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

Similar topics

1 post views Thread by Propoflady | last post: by
1 post views Thread by Martin Schneider | last post: by
reply views Thread by Martin Schneider | last post: by
1 post views Thread by DavidB | last post: by
1 post views Thread by Tiger Boon | last post: by
reply views Thread by zhoujie | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.