If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
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. -- Wayne Morgan MS Access MVP "Tim" wrote in message ... 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 |
#12
|
|||
|
|||
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. |
#13
|
|||
|
|||
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? -- Wayne Morgan MS Access MVP "Tim" wrote in message ... 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. |
#14
|
|||
|
|||
it worked
thank you for your help |
#15
|
|||
|
|||
Conditional Formatting with more then 3 conditions
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! "Wayne Morgan" wrote: 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? -- Wayne Morgan MS Access MVP "Tim" wrote in message ... 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. |
#16
|
|||
|
|||
Conditional Formatting with more then 3 conditions
cynteeuh wrote:
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. -- Marsh MVP [MS Access] |
#17
|
|||
|
|||
Conditional Formatting with more then 3 conditions
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! "Marshall Barton" wrote: cynteeuh wrote: 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. -- Marsh MVP [MS Access] |
#18
|
|||
|
|||
Conditional Formatting with more then 3 conditions
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. -- Marsh MVP [MS Access] cynteeuh wrote: 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 "Marshall Barton" wrote: cynteeuh wrote: 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. |
#19
|
|||
|
|||
Conditional Formatting with more then 3 conditions
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! "Marshall Barton" wrote: 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. -- Marsh MVP [MS Access] cynteeuh wrote: 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 "Marshall Barton" wrote: cynteeuh wrote: 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. |
#20
|
|||
|
|||
Conditional Formatting with more then 3 conditions
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 -- Marsh MVP [MS Access] cynteeuh wrote: 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! "Marshall Barton" wrote: 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. -- Marsh MVP [MS Access] cynteeuh wrote: 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 "Marshall Barton" wrote: cynteeuh wrote: 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. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
conditional formatting in form slows down calculations | Susan | Using Forms | 5 | June 13th, 2005 11:24 AM |
Conditional Formatting Error | ddate | Worksheet Functions | 0 | May 5th, 2005 09:00 PM |
Conditional Formatting Anomaly | Rutgers_Excels | General Discussion | 1 | November 1st, 2004 08:43 PM |
Freezing Conditional Formatting | Ashish Chamaria | Worksheet Functions | 3 | December 19th, 2003 10:54 AM |
Conditional formatting with formula | Ann Scharpf | Worksheet Functions | 5 | October 21st, 2003 04:29 AM |