473,836 Members | 1,505 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Conditional Format in Report Limit

9 New Member
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, 6066 views)
Oct 13 '11 #1
16 4419
NeoPa
32,584 Recognized Expert Moderator MVP
There's so little that makes sense in your question it's hard to know what to say.

Firstly, why not use ConditionalForm atting 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 ConditionalForm atting 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 New Member
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=Gr een, 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, 1323 views)
Oct 13 '11 #3
NeoPa
32,584 Recognized Expert Moderator MVP
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 New Member
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,584 Recognized Expert Moderator MVP
I wish you the best of luck Art, and I'll wait for your update.
Oct 17 '11 #6
art502
9 New Member
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,584 Recognized Expert Moderator MVP
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 New Member
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.Control s". 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 Recognized Expert Moderator MVP
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

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

Similar topics

6
5515
by: Allan Koch | last post by:
Dear NG, I would like to format a record in a subform, dependent on a value in one field. If one field in the record I show in a subform (datasheet view) is true I like to view that particular row in green, all other rows just as they are normal. Is that possible ? regards Allan
1
1456
by: Propoflady | last post by:
I have a database that has tables, contact event and last date of contact. Then I have a report where I can print current contact and event. If the last date of contact is less than 3 months previous I want to ignore this event. Is it possible to make the dbase go look at the field of last date of contact and if less than three months then put in last date of contact? All help appreciated
1
4483
by: Martin Schneider | last post by:
Hi! I have an unbound text field conditionally formatted: =DomWert("";"ActiveLines") (DomWert is the german translation for domain value) (to check whether the currend record is within the set of records returned by a query).
0
1439
by: Martin Schneider | last post by:
Hi! I'd like to set the 'enabled'-property for a checkbox depending on a conditional format. Unfortunately, the object doesn't feature a format condition. Is there a solution for this? Alternatively I'd like to disable the whole record (but only this one) - is that possible? Best regards,
2
8620
by: Lyn | last post by:
Hi, Having fun trying to get Conditional Formatting working on a textbox control in a continuous form subform (Access 2003). The condition I want is when the value of the textbox is Null and/or an empty string. 1) Using Access: I select "Field value is" and "equal to". Not sure what to put in the Expression1 field. Have tried leaving it empty, inserting a pair of double quotes (""), a pair of single quotes ('') and the word Null. None...
1
1904
by: DavidB | last post by:
I have a report that (among other data) lists three different date fields from one of my tables. This is a validation report in that date #1 and date #2 should be earlier than date #3. The report only contains records that fail this edit check. On some records date #1 is later than date #3, on some date #2 is later than date #3, and on other both date #1 and date #2 are later than date #3. I'd like to conditionally format both date #1...
1
1471
by: mattloflin | last post by:
Hey guys i'm kind of confused. I'm new to access but I've been getting a lot of progress. I have a form FRMDATABASE that is loaded into FRMSEARCH and frm search has a bunch of txtboxes that searches the the FRMDATABASE but now I need to conditional format the data within the FRMDATABASE and I have to do it based of code sense I can't limit it to 3. I use this on the report of the searched criteria and it works like a charm: If Like...
1
5051
by: Tiger Boon | last post by:
Anyone knows how to overcome the following problem? And also, I belive Crystal Report has a limit of 75 print job. How do we check the current usage? The maximum report processing jobs limit configured by your system administrator has been reached. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in...
3
2336
by: Rhys Gottwald | last post by:
Hi All, I have a report that is going to have 30 odd fields each containing a number, the number is the wind speed. I want to format the background of the field based on the value of the text box. I have code that formats the background, but I am having issues with it. They are listed below 1. I cannot get it to use # values i.e. #e4fefc 2. I can only get it to format when I put in detail_click, detail_format does not seem to...
0
9813
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, weíll explore What is ONU, What Is Router, ONU & Routerís main usage, and What is the difference between ONU and Router. Letís take a closer look ! Part I. Meaning of...
0
9664
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10832
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10539
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10584
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10248
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
6976
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5815
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4446
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.