HELP: Conditional Formatting with more then 3 conditions

[Guest]

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?

 

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

 

[Guest]

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

 

[Guest]

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.

 

[Guest]

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?

 

[Wayne Morgan]

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.

 

[Guest]

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.

 

[Wayne Morgan]

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 & "'")

 

[Wayne Morgan]

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.

 

[Guest]

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

 

[Wayne Morgan]

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.

 

[Guest]

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.

 

[Wayne Morgan]

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?

 

[Guest]

it worked

thank you for your help

 

[Guest]

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.

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

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.

 

[Marshall Barton]

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.

 

[Guest]

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]

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]

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

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.

 

[Guest]

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:

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]

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

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.

 

[Marshall Barton]

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]

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:

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]

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


:

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.