 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Reports / Printing / November 2008HELP: Conditional Formatting with more then 3 conditions
ACCESS 2000 There is a field in my database that requires highlighting; however, I have a total of 15 cases that requires, obviously I can't set that up with the default C.F. Is there a way to set up a VBA code that would allow me to set up the 15 conditions? Yes, in the Format event of the section of the report that the field is reported in, you could set the formatting of the control for that field. A Select Case statement may be the easiest. Example: Select Case Me.txtMyTextbox Case "AB" Me.txtMyTextbox.ForeColor = 8454143 Case 'etc Case Else Me.txtMyTextbox.ForeColor = 0 End Select  SignatureWayne Morgan MS Access MVP > ACCESS 2000 > [quoted text clipped - 5 lines] > Is there a way to set up a VBA code that would allow me to set up the 15 > conditions? that code works for the text, but I need it for the background color. I tried Select Case Me.txtBox Case "ab" me.txtBox.BackColor=255 Case Else me.txtBox.BackColor=0 End Select End Sub (to highlight the entire box red) but it didn't work nevermind, I solved the problem. I had the text box set on TRANSPARENT; that's why the conditional highlighting wasn't appearing. when I changed the default BackColor to white, the conditional BackColor appeared. thanks for your help. Could this work: Condition BackColor X1 255 X2 255 X3 255 Y1 100 Y2 100 Z1 301 Z2 302 Would this be more efficient, espically if I have a long list, and more conditions arise that would require highlighting? How would I structure the VBA code? Select Case Me.txtBox Case "X1", "X2", "X3" Me.txtBox.BackColor = 255 Case "Y1", "Y2" Me.txtBox.BackColor = 100 Case "Z1" Me.txtBox.BackColor = 301 Case "Z2" Me.txtBox.BackColor = 302 Case Else Me.txtBox.BackColor = 16777215 End Select In the first statement, you may be able to get by with Case "X1" To "X3" so, if you have a lot more of these (i.e. X4, X5, X6, etc), that may be the shorter way to write it. With just 3 elements though, it's not much shorter. Whether or not this could be simplified (to lessen the typing) will depend on how many you have and what sort of pattern they are (i.e. is there a pattern to them that would be short and easy to code). The Like operator may come to mind, but it's not supported in a Select Case statement. SignatureWayne Morgan MS Access MVP > Could this work: > [quoted text clipped - 11 lines] > > How would I structure the VBA code? So I would not be able to do this by setting up a table with the condition in one column and the color# in the second column. I would need to specify the condition in the line of code? This list has the potentional of expanding; what I wrote was just a quick-and-dirty example. Sorry I didn't clarify that before. > Select Case Me.txtBox > Case "X1", "X2", "X3" [quoted text clipped - 35 lines] > > > > How would I structure the VBA code? Yes, you could set up a table with the condition and the color. You could then lookup the color and apply it. Example: Me.txtBox.BackColor = DLookup("ColorField", "TableName", "ConditionField='" & Me.txtBox & "'") SignatureWayne Morgan MS Access MVP > So I would not be able to do this by setting up a table with the condition > in [quoted text clipped - 49 lines] >> > >> > How would I structure the VBA code? The colors won't appear. Here is the code Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer) Select Case Me.txtBOX Case Me.txtBOX.BackColor = DLookup("color", "condition-color", "condition='" & Me.txtBOX & "'") Case Else Me.txtBOX.BackColor = 16777215 End Select End Sub Using this wouldn't be a Select Case, you would just place the statement in the code. Also, please see my second reply which I believe would be faster than the DLookup. SignatureWayne Morgan MS Access MVP > The colors won't appear. Here is the code > [quoted text clipped - 8 lines] > > End Sub PS. DLookup could get fairly slow. It may be better to include the lookup table in the query feeding the report. Link the lookup table to the main table on the condition field and the field in the main table that has the value (i.e. X1, X2, etc). You would then have the associated color field available for each record. Place a textbox in the same report section as txtBox and set this new textbox's Visible property to No. In the Format event code for the section you could now use: Me.txtBox.BackColor = Me.txtColorBox To handle the Else condition as in the Case Else in the Select statement, set the link between the tables to "include all fields from the main table and only those fields from the lookup table where the fields match". This will give you a value of Null for all records that didn't have a color specified. You would then change the above statement to: Me.txtBox.BackColor = Nz(Me.txtColorBox, 16777215) This will return the color 16777215 if a color hasn't been specified. SignatureWayne Morgan MS Access MVP > So I would not be able to do this by setting up a table with the condition > in [quoted text clipped - 49 lines] >> > >> > How would I structure the VBA code? nothing worked here is the code Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer) Me.txtBOX.BackColor = DLookup("color", "condition-color", "condition='" & Me.txtBOX & "'") End Sub Am I missing something? And when I tried the query approach I got an error message: The SQL statement could not be executed because it contains ambiguous outer joins. To force one of the joins to be performed first, create a seperate query that performs that first join and then include that query in your SQL statement. Ok, the query may not work depending on how complicated the query is that you already have. I just tried the DLookup option and it worked. Are you getting any error messages? Have you stepped through the code to see what values are actually being found in the code? While the table name doesn't have any spaces in it, try enclosing it in brackets anyway in case the hyphen is causing a problem, "[Condition-Color]". To handle items that may not have a match (this will cause DLookup to return Null) you may want to wrap the equation with Nz. Me.txtBOX.BackColor = Nz(DLookup("color", "condition-color", "condition='" & Me.txtBOX & "'"), 16777215) This will return a white background if DLookup returns Null. The syntax for Condition is for a Text value, is that correct? You placed the code in the Format event of the Detail section, is the textbox txtBox in the Detail section? SignatureWayne Morgan MS Access MVP > nothing worked > [quoted text clipped - 14 lines] > SQL > statement. it worked thank you for your help I don't know VBA. Is there any other alternative for more than 3 conditions in conditonal formatting? I need six or five since I can use the default as the sixth. Thanks! > Ok, the query may not work depending on how complicated the query is that > you already have. [quoted text clipped - 32 lines] > > SQL > > statement. >I don't know VBA. Is there any other alternative for more than 3 conditions >in conditonal formatting? I need six or five since I can use the default as >the sixth. Well there is, but it's more complicated than the simple code that you can use in a report (or single view form, but not in a continuous or datasheet form). If you will explain the conditions you want to use (including the names of the text box controls), I'll take a shot at the code to do it. SignatureMarsh MVP [MS Access] Basically, if [coname]=x, then use color per the below. I've setup the conditional formatting for three, but I can't do the additional due to limitation. Red Lt Blue Dk Blue Purple Green Thanks for your help! > >I don't know VBA. Is there any other alternative for more than 3 conditions > >in conditonal formatting? I need six or five since I can use the default as [quoted text clipped - 7 lines] > (including the names of the text box controls), I'll take a > shot at the code to do it. The general idea for doing this in the report Detail section's Format event procedure is something like: Select Case Me.coname Case "x" Me.coname.BackColor = vbRed Case "y" Me.coname.BackColor = Rgb(220,220,255) 'lt blue Case "z" Me.coname.BackColor = vbBlue . . . Case Else Me.coname.BackColor = vbWhite End Select If that's too confusing for you, then explain more about the "x" and anything else you don't follow. SignatureMarsh MVP [MS Access] >Basically, if [coname]=x, then use color per the below. I've setup the >conditional formatting for three, but I can't do the additional due to [quoted text clipped - 17 lines] >> (including the names of the text box controls), I'll take a >> shot at the code to do it. Sorry to be a pain, but it is a bit over my head. Where would I enter this info? Is it in the properties, which has five tabs (format, data, event, other, & all)? If so, where or how would you enter this? I'm trying to create a grid with the color chart next to the name. Color CoName Red John Lt Blue Cyn Dk Blue Rod Green Antonio Therefore, if the field coname is "john", the box is shaded red. If it's "cyn", the box is shaded lt. blue, etc. This is a report based on a crosstab query that I'd like to embed as a sub rpt in the rpt header. I'm using it as a legend. It's working, except for the conditional formatting limitation. The current rpt is block style (spreadsheet format). The first column is an unbound text box (shaded by conditional formatting based on [coname]). The next column is the coname. The remainder columns are the totals. Thanks AGAIN for helping on this! Apologies for my ignorance! > The general idea for doing this in the report Detail > section's Format event procedure is something like: [quoted text clipped - 34 lines] > >> (including the names of the text box controls), I'll take a > >> shot at the code to do it. First, open the report )the one you are using as a subreport) in Design View. Then click on a *blank* area of the report section (Detail?) that contains the name text box. In the properties list, under the Events tab, select [Event Procedure] from the drop list on the right side of the OnFormat property. That should open the report's VBA module and put the cursor in the event procedure, ready for you to enter the code: Select Case Me.coname Case "John" Me.coname.BackColor = vbRed Case "Cyn" Me.coname.BackColor = Rgb(220,220,255) 'lt blue Case "Rod" Me.coname.BackColor = vbBlue Case "Antonio" Me.coname.BackColor = vbGreen Case Else Me.coname.BackColor = vbWhite End Select SignatureMarsh MVP [MS Access] >Sorry to be a pain, but it is a bit over my head. Where would I enter this >info? Is it in the properties, which has five tabs (format, data, event, [quoted text clipped - 56 lines] >> >> (including the names of the text box controls), I'll take a >> >> shot at the code to do it. I copied the below and pasted per your instructions, but nothing changed. I'm thinking it may be my unbound text box. There isn't any control source. This is the box I'd like shaded per the colors based on the name. It is next to the field/textbox that has the name. Any suggestions? :-/ Also, was I supposed to leave the name " 'lt blue" in the code? Is the last color purple? If not, what is the code for purple? Thanks AGAIN for your help!!! I truly appreciate it! > First, open the report )the one you are using as a > subreport) in Design View. Then click on a *blank* area of [quoted text clipped - 78 lines] > >> >> (including the names of the text box controls), I'll take a > >> >> shot at the code to do it. Did you make sure that the section's OnFormat property contains [Event Procedure] Another reason for nothing happening is that the coname text box doesn't really have the values "John", "Cyn", etc. so the Case Else was in effect. This can be verified by changing vbWhite to vbBlack so you can see when it happens. If the colored text box is not the coname text box, then change the coname to the name of the colored text box in all of the lines like: Me.[color text box name].BackColor = vbRed You did not have a person assigned to purple. The code depends on what you think purple looks like. I kind of like this one: RGB(190,85,255) There is no predefined VBA variable for Lt Blue so I guessed at a shade that might be acceptable. Look up the RGB function in VBA Help for details about how to specify colors. If you have additional qusestions on this topic, please post copy/paste the code as you have it along with your question. SignatureMarsh MVP [MS Access] >I copied the below and pasted per your instructions, but nothing changed. I'm >thinking it may be my unbound text box. There isn't any control source. This [quoted text clipped - 45 lines] >> >unbound text box (shaded by conditional formatting based on [coname]). The >> >next column is the coname. The remainder columns are the totals. Ok, the below is what I have in the "Event Procedure" window. When I changed white to black, it still didn't do anything. The same is true by changing text box to actual name of field. No results. Instead the text box has the actual field names and no color. :( Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer) Select Case Me.AcqdByCoName Case "Amazon" Me.AcqdByCoName.BackColor = vbRed Case "Intel" Me.AcqdByCoName.BackColor = RGB(220, 220, 255) Case "RealNetworks" Me.AcqdByCoName.BackColor = vbBlue Case "Intuit" Me.AcqdByCoName.BackColor = vbGreen Case Else Me.AcqdByCoName.BackColor = vbBlack End Select End Sub > Did you make sure that the section's OnFormat property > contains [Event Procedure] [quoted text clipped - 69 lines] > >> >unbound text box (shaded by conditional formatting based on [coname]). The > >> >next column is the coname. The remainder columns are the totals. NEVER MIND -- I got it to work! I had used the wrong name for the text box! I was using the control source name and not the text box name, which is "text33". #-o I also changed the text box from unbound to the control source with the values. It works beautifully now, except the names/values are in the box. I only wanted a shaded box with no text. Any suggestions? THANKS SO MUCH for hanging in there with me!!!!!! I feel like an idiot! L-) -- > Did you make sure that the section's OnFormat property > contains [Event Procedure] [quoted text clipped - 69 lines] > >> >unbound text box (shaded by conditional formatting based on [coname]). The > >> >next column is the coname. The remainder columns are the totals. >NEVER MIND -- I got it to work! I had used the wrong name for the text box! I >was using the control source name and not the text box name, which is [quoted text clipped - 7 lines] > >THANKS SO MUCH for hanging in there with me!!!!!! I feel like an idiot! L-) You are not allowed to feel like an idiot until you make the same mistake three times ;-) Live and learn! To color the other text box, change the code so it looks more like: Me.[name of color text box].BackColor = . . . and make sure the color text box is unbound. SignatureMarsh MVP [MS Access] Thanks for the encouraging words! I feel a lil' better. :) Sorry to drag this on, but (sadly) I still can't get my unbound text box to be shaded per the value of another field (the same five variables in coding below -- Amazon=Red, Intel=Blue, etc.). It works with the coding in earlier post, but the values name's are in the box (I've got a text box with the name shaded by the color based on the value). I only want the text box to be shaded and no text. Is that possible? I couldn't find a way to make the text font transparent. You're solid, THANK you AGAIN so VERY MUCH! -- Cyn Desparately trying to grasp this . . . > >NEVER MIND -- I got it to work! I had used the wrong name for the text box! I > >was using the control source name and not the text box name, which is [quoted text clipped - 17 lines] > > and make sure the color text box is unbound. >Thanks for the encouraging words! I feel a lil' better. :) > [quoted text clipped - 8 lines] > >You're solid, THANK you AGAIN so VERY MUCH! Hmmm, I guess I better see a copy of the code that doesn't work. Maybe I can take a shot at some of the things that might cause nothing to happen. Since the color works when you set the color of the AcqdByCoName text box but doesn't work when you try to set it on some other text box, you better make sure the other (better if I knew the real name) text box's BackStyle property is set to Normal. Are you sure it really is a text box (or label) and not some other kind of control? This exercise is very possible. Actually it's a fairly simple to do . . . once you understand what it's all about. SignatureMarsh MVP [MS Access] Yes, it's a text box and the BackStyle is normal. :) I don't think it's in the coding, because when I change the working text box's control source to blank "unbound", the text box defaults to the "Case Else", which is purple. I end up with five solid colored purple boxes without any data in it. I'd like the five boxes colored per vba code, but without the names/data in it. In other words, I'm trying to get a text box without the company name's in the box. The working rpt shows text box with the company name in a white font and shaded per the vba code. I'd prefer the colored/shaded boxes without the company name in it. Therefore I'd have a shaded box (per the code), then a text box next to that with company name. I hope that makes sense. :-< e.g. Red Shaded Box Amazon Green Shaded Box Intuit I know this is simple and I'm making it hard. Pls forgive me! ;-l -- Cyn > >Thanks for the encouraging words! I feel a lil' better. :) > > [quoted text clipped - 22 lines] > This exercise is very possible. Actually it's a fairly > simple to do . . . once you understand what it's all about. You forgot to post a copy of the code that is not working. I also want to know the name of the text box you want to have the colors. I just thought of one other mistake you might have made. You didn't change the text box name in the Select Case statement did you? It should still be the bound text box: Select Case Me.AcqdByCoName SignatureMarsh MVP [MS Access] >Yes, it's a text box and the BackStyle is normal. :) > [quoted text clipped - 15 lines] > >I know this is simple and I'm making it hard. Pls forgive me! ;-l If a hack is acceptable, you can change the forecolor as well (text the same color as the background) and that will effectively "hide" the text within the control. The text will still be viewable when selected with the mouse. -Kris i have entered the following code in the OnFormatEvent of the Detail section: Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer) Select Case Me.category Case "A" Me.client.BackColor = 12632256 Case "B" Me.client.BackColor = 12632256 Case "C" Me.client.BackColor = 12632256 Case Else Me.client.BackColor = 16777215 End Select Select Case Me.category Case "dat" Me.client.FontUnderline = yes Case Else Me.client.FontUnderline = no End Select End Sub but i get the formatting only with the back color, not with the font underline. have i done something wrong with the code? Ο χρήστης "Wayne Morgan" έγγραψε: > Yes, in the Format event of the section of the report that the field is > reported in, you could set the formatting of the control for that field. A [quoted text clipped - 18 lines] > > Is there a way to set up a VBA code that would allow me to set up the 15 > > conditions? |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.