Conditional Format in Report Limit

[art502]

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.

For x = 1 To 12
If Me("date" & x) = "LL" Or Me("date" & x) = "PA" Or Me("date" & x) = "PS" _
Or Me("date" & x) = "AS" Or Me("date" & x) = "P" Or Me("date" & x) = "A" Or Me("date" & x) = "S" Then
 
    If Me("date" & x) = "LL" Then
        Me("date" & x).BackColor = vbBlack
        Me("date" & x).ForeColor = vbBlack
    End If
    If Me("date" & x) = "PA" Then
        Me("date" & x).BackColor = vbCyan
        Me("date" & x).ForeColor = vbCyan
    End If
    If Me("date" & x) = "PS" Then
        Me("date" & x).BackColor = vbYellow
        Me("date" & x).ForeColor = vbYellow
    End If
 
' ...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.

[NeoPa]

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.

[art502]

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.

Option Explicit
Option Compare Text
 
Private Sub SetFormattingControl()
 
Dim StrDate As String
Dim StrDatePre As String
Dim x As Integer
 
For x = 1 To 12
If Me("date" & x) = "LL" Or Me("date" & x) = "PA" Or Me("date" & x) = "PS" _
Or Me("date" & x) = "AS" Or Me("date" & x) = "P" Or Me("date" & x) = "A" Or Me("date" & x) = "S" Then
 
    If Me("date" & x) = "LL" Then
        Me("date" & x).BackColor = vbBlack
        Me("date" & x).ForeColor = vbBlack
    End If
    If Me("date" & x) = "PA" Then
        Me("date" & x).BackColor = vbCyan
        Me("date" & x).ForeColor = vbCyan
    End If
    If Me("date" & x) = "PS" Then
        Me("date" & x).BackColor = vbYellow
        Me("date" & x).ForeColor = vbYellow
    End If
    If Me("date" & x) = "AS" Then
        Me("date" & x).BackColor = vbMagenta
        Me("date" & x).ForeColor = vbMagenta
    End If
    If Me("date" & x) = "P" Then
        Me("date" & x).BackColor = vbGreen
        Me("date" & x).ForeColor = vbGreen  ' No vb code for dark green use: 4227072
    End If
    If Me("date" & x) = "A" Then
        Me("date" & x).BackColor = vbBlue
        Me("date" & x).ForeColor = vbBlue
    End If
    If Me("date" & x) = "S" Then
        Me("date" & x).BackColor = vbRed
        Me("date" & x).ForeColor = vbRed
    End If
Else
    If IsNull(Me("date" & x)) Then
        Me("date" & x).BackColor = 16777215
        Me("date" & x).ForeColor = vbBlack
    End If
End If
Next x
End Sub
 
Private Sub detail_format(Cancel As Integer, formatcount As Integer) 'shows color on the printout
    SetFormattingControl
End Sub
 
Private Sub detail_paint() 'Shows color on the Report View.
    SetFormattingControl
End Sub

[NeoPa]

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).

Option Explicit
Option Compare Text
 
Private Sub detail_format(Cancel As Integer, formatcount As Integer) 'shows color on the printout
    Call SetFormattingControl
End Sub
 
Private Sub detail_paint() 'Shows color on the Report View.
    Call SetFormattingControl
End Sub
 
Private Sub SetFormattingControl()
    Dim lngCol As Long
    Dim ctl As Control
 
    With Me
        For Each ctl In .Controls
            If Left(.Name, 4) = "Date" Then
                Select Case .Value
                Case "LL"
                    lngCol = vbBlack
                Case "PA"
                    lngCol = vbCyan
                Case "PS"
                    lngCol = vbYellow
                Case "AS"
                    lngCol = vbMagenta
                Case "P"
                    lngCol = vbGreen
                Case "A"
                    lngCol = vbBlue
                Case "S"
                    lngCol = vbRed
                Case Else
                    lngCol = vbWhite
                    .ForeColor = vbBlack
                End Select
                .BackColor = lngCol
                If lngCol <> vbWhite Then .ForeColor = lngCol
            End If
        Next ctl
    End With
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.

[art502]

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.

[NeoPa]

I wish you the best of luck Art, and I'll wait for your update.

[art502]

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?

[NeoPa]

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 :

Option Explicit
Option Compare Text
 
Private Sub detail_format(Cancel As Integer, formatcount As Integer) 'shows color on the printout
    Call SetFormattingControl
End Sub
 
Private Sub detail_paint() 'Shows color on the Report View.
    Call SetFormattingControl
End Sub
 
Private Sub SetFormattingControl()
    Dim lngCol As Long
    Dim ctl As Control
 
    For Each ctl In Me.Controls
        With ctl
            If Left(.Name, 4) = "Date" Then
                Select Case .Value
                Case "LL"
                    lngCol = vbBlack
                Case "PA"
                    lngCol = vbCyan
                Case "PS"
                    lngCol = vbYellow
                Case "AS"
                    lngCol = vbMagenta
                Case "P"
                    lngCol = vbGreen
                Case "A"
                    lngCol = vbBlue
                Case "S"
                    lngCol = vbRed
                Case Else
                    lngCol = vbWhite
                End Select
                .BackColor = lngCol
                .ForeColor = IIf(lngCol = vbWhite, vbBlack, lngCol)
            End If
        Next ctl
    End With
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.

[art502]

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.

[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?

[NeoPa]

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).

[art502]

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.

[art502]

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!

[art502]

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.

[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 ;-)

[art502]

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!

[NeoPa]

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 :-)