Conditional Formatting Limitations

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi everyone,

I am using Access XP and have ran into a really annoying problem with this
whole conditional formatting limitation. Hopefully someone has managed to
get around this. I have tried several of the VBA recommendations found on
Microsofts site and it still doesn't seem to get around the issue when you
are trying to format more than one control at a time.

I have four fields in a subform and for ease of reference, lets call them:
- TradeType
- TradeVolume
- TradeCost
- TradeSurcharge

TradeType is a simple string field and the rest are all numerics.

The TradeType can be one of 6 different states and based on that state, they
want the entire row (the remaining fields) to have a different back color.

Every time I try to code this, I end up getting the three conditional rules
error.

Does anyone have any advice as to how I might be able to get around this.

Thank you very much in advance.

Lance
 
LTofsrud said:
I am using Access XP and have ran into a really annoying problem with this
whole conditional formatting limitation. Hopefully someone has managed to
get around this. I have tried several of the VBA recommendations found on
Microsofts site and it still doesn't seem to get around the issue when you
are trying to format more than one control at a time.

I have four fields in a subform and for ease of reference, lets call them:
- TradeType
- TradeVolume
- TradeCost
- TradeSurcharge

TradeType is a simple string field and the rest are all numerics.

The TradeType can be one of 6 different states and based on that state, they
want the entire row (the remaining fields) to have a different back color.

Every time I try to code this, I end up getting the three conditional rules
error.
Click to expand...


The limit on the number of different formats is 4 (default
format and 3 conditional formats). There is no way to use
Conditional Formatting to get more colors than that.

You said you want the entire "row" to be colored, which kind
of infers that your form is displayed in either Continuous
or Datasheet View. For a Single View form the issue is
trivial (just use code to set the color. You're completely
out of luck for DataSheet view. For Continuous View, there
is a way, but it is very messy and rather slow.
 
I am using the following method:

Recordsource: Select .... , myFormat(fieldname,1) As f1,
myFormat(fieldname,2) as f2,...

Where myFormat is in a code module:

Public Function myFormat(s As String, n As Integer)
select case n
case 1
select case s
case "foo"
myFormat = "foo"
case else
myFormat = ""
End select
case 2
select case s
case "bar"
myFormat = "bar"
case else
myFormat = ""
End select
end select
End Function

In the continuous form I have two textboxes with BackStyle = transparent. They
are bound to f1 and f2 respectively, and they have different ForeColors. The
responsibility of MyFormat is to make sure there is only text <> "" in one of
the textboxes. Of course I only use this technique when conditional
formatting is
insufficient :-)

Is this roughly what you had in mind, or is there a better way?
 
Yes, that's the general idea. You could also use
expressions in the text boxes instead of the query fields,
but that's usually going to be slower.

If the field values were limited to positive integers, you
can reduce the number of "stacked" text boxes by taking
advantage of the positive/negative options in the text
boxes' Format property. But, this approach limits you to
the basic 8 colors.

As I said, this whole issue gets messy (and slow),
especially when you get more than a couple of different
colors.
 
Thanks you guys for the response, it is really appreciated. I'll give
Daniel's code a try and just tell the client they will have to live with a
type field or something similar in the mean time.

Thanks again!

Lance

Marshall Barton said:
Yes, that's the general idea. You could also use
expressions in the text boxes instead of the query fields,
but that's usually going to be slower.

If the field values were limited to positive integers, you
can reduce the number of "stacked" text boxes by taking
advantage of the positive/negative options in the text
boxes' Format property. But, this approach limits you to
the basic 8 colors.

As I said, this whole issue gets messy (and slow),
especially when you get more than a couple of different
colors.
--
Marsh
MVP [MS Access]


I am using the following method:

Recordsource: Select .... , myFormat(fieldname,1) As f1,
myFormat(fieldname,2) as f2,...

Where myFormat is in a code module:

Public Function myFormat(s As String, n As Integer)
select case n
case 1
select case s
case "foo"
myFormat = "foo"
case else
myFormat = ""
End select
case 2
select case s
case "bar"
myFormat = "bar"
case else
myFormat = ""
End select
end select
End Function

In the continuous form I have two textboxes with BackStyle = transparent. They
are bound to f1 and f2 respectively, and they have different ForeColors. The
responsibility of MyFormat is to make sure there is only text <> "" in one of
the textboxes. Of course I only use this technique when conditional
formatting is
insufficient :-)

Is this roughly what you had in mind, or is there a better way?
Click to expand...
Click to expand...
 
Back
Top