More than three conditional make-up criteria

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

Guest

Hello,

I've got a continuous form containing more than one record (5 to 30). I
present the data of these records in text boxes. I need to color the boxes
according to the data that they contain. This means that the same field can
have different colors in different records. The total number of colors that
is about 8 (correspondending with 8 different codes that can be in the data
fields behind the text boxes).

Normally I would use Access standard conditional make-up function to
implement the functionality described above. However the limit on the
conditionl make-up criteria for each field is three (also if you use the
formatconditions object in VBA) so that is not sufficient in this case. Is
there any way to add more than three conditional make-up criteria to a text
field?

Any help would be very much appreciated.
 
flbos said:
I've got a continuous form containing more than one record (5 to 30). I
present the data of these records in text boxes. I need to color the boxes
according to the data that they contain. This means that the same field can
have different colors in different records. The total number of colors that
is about 8 (correspondending with 8 different codes that can be in the data
fields behind the text boxes).

Normally I would use Access standard conditional make-up function to
implement the functionality described above. However the limit on the
conditionl make-up criteria for each field is three (also if you use the
formatconditions object in VBA) so that is not sufficient in this case. Is
there any way to add more than three conditional make-up criteria to a text
field?
Click to expand...


I was about to to say No, Conditional Formatting can only
deal with the default color plus three conditional colors.
You can have as many conditions as you want by using a
function in the Expression Is option, but that won't help
get more than three colors. However, a bolt from out of the
blue gave me an idea ;-)

We can combine Conditional Formatting with the old A2
technique of stacking text boxes behind the one you want to
color.

Note that when a code text box has the focus, it's own
BackColor will be displayed, so be sure to choose that as
desired FIRST, then set its BackStyle property to
Transparent.

Then add another text box and set its control source
expression to something like:
=IIf(code Between 1 And 3, "AAAAA", Null)
Set the text box's FontName to LaBox (available at
http://www.mvps.org/access/forms/frm0055.htm) and
set the FontSize to 12 or so. Now, use CF to add three
conditions:
Expression Is:
Code: 
=1	ForeColor for this code value
Expression Is: [code]=2	ForeColor for this code value
Expression Is: [code]=3	ForeColor for this code value

Repeat this with two more text boxes, one for codes 4-6 and
the other for codes 7-8.

Test the form to make sure you are getting the desired
colors. After that is all working the way you want, set
these three text box's BackStyle property to Transparent.
This must be done last because changing a setting in CF
resets a text box's BackStyle to Normal.

Finally, size and position these funky text boxes exactly on
top of the code text box and use Format - Send to Back to
put them behind the code text box.
 
Marsh I tied this in the past without success. Access would go into a loop
continuously refreshing the stacked TextBox controls whenever CF was applied
to any of them. Do you have a working sample you could send me?
--

HTH
Stephen Lebans
http://www.lebans.com
Access Code, Tips and Tricks
Please respond only to the newsgroups so everyone can benefit.


Marshall Barton said:
flbos said:
I've got a continuous form containing more than one record (5 to 30). I
present the data of these records in text boxes. I need to color the boxes
according to the data that they contain. This means that the same field
can
have different colors in different records. The total number of colors
that
is about 8 (correspondending with 8 different codes that can be in the
data
fields behind the text boxes).

Normally I would use Access standard conditional make-up function to
implement the functionality described above. However the limit on the
conditionl make-up criteria for each field is three (also if you use the
formatconditions object in VBA) so that is not sufficient in this case. Is
there any way to add more than three conditional make-up criteria to a
text
field?
Click to expand...


I was about to to say No, Conditional Formatting can only
deal with the default color plus three conditional colors.
You can have as many conditions as you want by using a
function in the Expression Is option, but that won't help
get more than three colors. However, a bolt from out of the
blue gave me an idea ;-)

We can combine Conditional Formatting with the old A2
technique of stacking text boxes behind the one you want to
color.

Note that when a code text box has the focus, it's own
BackColor will be displayed, so be sure to choose that as
desired FIRST, then set its BackStyle property to
Transparent.

Then add another text box and set its control source
expression to something like:
=IIf(code Between 1 And 3, "AAAAA", Null)
Set the text box's FontName to LaBox (available at
http://www.mvps.org/access/forms/frm0055.htm) and
set the FontSize to 12 or so. Now, use CF to add three
conditions:
Expression Is:
Code: 
=1 ForeColor for this code value
Expression Is: [code]=2 ForeColor for this code value
Expression Is: [code]=3 ForeColor for this code value

Repeat this with two more text boxes, one for codes 4-6 and
the other for codes 7-8.

Test the form to make sure you are getting the desired
colors. After that is all working the way you want, set
these three text box's BackStyle property to Transparent.
This must be done last because changing a setting in CF
resets a text box's BackStyle to Normal.

Finally, size and position these funky text boxes exactly on
top of the code text box and use Format - Send to Back to
put them behind the code text box.
[/QUOTE]
Click to expand...
 
Thanks for the reactions!

Seems that there might be a solution but it is not tested. Stephen did you
test it exactly the way as described by Marshall and are you sure it won't
work or is it worth for me to give it a shot?

Thanks,

Floris

Stephen Lebans said:
Marsh I tied this in the past without success. Access would go into a loop
continuously refreshing the stacked TextBox controls whenever CF was applied
to any of them. Do you have a working sample you could send me?
--

HTH
Stephen Lebans
http://www.lebans.com
Access Code, Tips and Tricks
Please respond only to the newsgroups so everyone can benefit.


Marshall Barton said:
flbos said:
I've got a continuous form containing more than one record (5 to 30). I
present the data of these records in text boxes. I need to color the boxes
according to the data that they contain. This means that the same field
can
have different colors in different records. The total number of colors
that
is about 8 (correspondending with 8 different codes that can be in the
data
fields behind the text boxes).

Normally I would use Access standard conditional make-up function to
implement the functionality described above. However the limit on the
conditionl make-up criteria for each field is three (also if you use the
formatconditions object in VBA) so that is not sufficient in this case. Is
there any way to add more than three conditional make-up criteria to a
text
field?
Click to expand...


I was about to to say No, Conditional Formatting can only
deal with the default color plus three conditional colors.
You can have as many conditions as you want by using a
function in the Expression Is option, but that won't help
get more than three colors. However, a bolt from out of the
blue gave me an idea ;-)

We can combine Conditional Formatting with the old A2
technique of stacking text boxes behind the one you want to
color.

Note that when a code text box has the focus, it's own
BackColor will be displayed, so be sure to choose that as
desired FIRST, then set its BackStyle property to
Transparent.

Then add another text box and set its control source
expression to something like:
=IIf(code Between 1 And 3, "AAAAA", Null)
Set the text box's FontName to LaBox (available at
http://www.mvps.org/access/forms/frm0055.htm) and
set the FontSize to 12 or so. Now, use CF to add three
conditions:
Expression Is:
Code: 
=1 ForeColor for this code value
Expression Is: [code]=2 ForeColor for this code value
Expression Is: [code]=3 ForeColor for this code value

Repeat this with two more text boxes, one for codes 4-6 and
the other for codes 7-8.

Test the form to make sure you are getting the desired
colors. After that is all working the way you want, set
these three text box's BackStyle property to Transparent.
This must be done last because changing a setting in CF
resets a text box's BackStyle to Normal.

Finally, size and position these funky text boxes exactly on
top of the code text box and use Format - Send to Back to
put them behind the code text box.
[/QUOTE]
Click to expand...
Click to expand...
 
Worked fine in my test case. Why don't you give it a try
while we're waiting for Stephen to look at it.
--
Marsh
MVP [MS Access]

Seems that there might be a solution but it is not tested. Stephen did you
test it exactly the way as described by Marshall and are you sure it won't
work or is it worth for me to give it a shot?


Stephen Lebans said:
Marsh I tied this in the past without success. Access would go into a loop
continuously refreshing the stacked TextBox controls whenever CF was applied
to any of them. Do you have a working sample you could send me?


"Marshall Barton" wrote
flbos wrote:
I've got a continuous form containing more than one record (5 to 30). I
present the data of these records in text boxes. I need to color the boxes
according to the data that they contain. This means that the same field
can
have different colors in different records. The total number of colors
that
is about 8 (correspondending with 8 different codes that can be in the
data
fields behind the text boxes).

Normally I would use Access standard conditional make-up function to
implement the functionality described above. However the limit on the
conditionl make-up criteria for each field is three (also if you use the
formatconditions object in VBA) so that is not sufficient in this case. Is
there any way to add more than three conditional make-up criteria to a
text
field?


I was about to to say No, Conditional Formatting can only
deal with the default color plus three conditional colors.
You can have as many conditions as you want by using a
function in the Expression Is option, but that won't help
get more than three colors. However, a bolt from out of the
blue gave me an idea ;-)

We can combine Conditional Formatting with the old A2
technique of stacking text boxes behind the one you want to
color.

Note that when a code text box has the focus, it's own
BackColor will be displayed, so be sure to choose that as
desired FIRST, then set its BackStyle property to
Transparent.

Then add another text box and set its control source
expression to something like:
=IIf(code Between 1 And 3, "AAAAA", Null)
Set the text box's FontName to LaBox (available at
http://www.mvps.org/access/forms/frm0055.htm) and
set the FontSize to 12 or so. Now, use CF to add three
conditions:
Expression Is:
Code: 
=1 ForeColor for this code value
Expression Is: [code]=2 ForeColor for this code value
Expression Is: [code]=3 ForeColor for this code value

Repeat this with two more text boxes, one for codes 4-6 and
the other for codes 7-8.

Test the form to make sure you are getting the desired
colors. After that is all working the way you want, set
these three text box's BackStyle property to Transparent.
This must be done last because changing a setting in CF
resets a text box's BackStyle to Normal.

Finally, size and position these funky text boxes exactly on
top of the code text box and use Format - Send to Back to
put them behind the code text box.[/QUOTE][/QUOTE][/QUOTE]
Click to expand...
Click to expand...
Click to expand...
 
Stephen said:
Marsh I tied this in the past without success. Access would go into a loop
continuously refreshing the stacked TextBox controls whenever CF was applied
to any of them. Do you have a working sample you could send me?
Click to expand...


I will send it later tonight, when I get a chance to extract
it from my scraps db.
 
Hi Marshall,

That's good news. I'll give it a try for sure. Could you send me your test
example too? It would be much easier for me to understand the method you
describe I think. My address: (e-mail address removed)

Thanks,

Floris

Marshall Barton said:
Worked fine in my test case. Why don't you give it a try
while we're waiting for Stephen to look at it.
--
Marsh
MVP [MS Access]

Seems that there might be a solution but it is not tested. Stephen did you
test it exactly the way as described by Marshall and are you sure it won't
work or is it worth for me to give it a shot?


Stephen Lebans said:
Marsh I tied this in the past without success. Access would go into a loop
continuously refreshing the stacked TextBox controls whenever CF was applied
to any of them. Do you have a working sample you could send me?


"Marshall Barton" wrote
flbos wrote:
I've got a continuous form containing more than one record (5 to 30). I
present the data of these records in text boxes. I need to color the boxes
according to the data that they contain. This means that the same field
can
have different colors in different records. The total number of colors
that
is about 8 (correspondending with 8 different codes that can be in the
data
fields behind the text boxes).

Normally I would use Access standard conditional make-up function to
implement the functionality described above. However the limit on the
conditionl make-up criteria for each field is three (also if you use the
formatconditions object in VBA) so that is not sufficient in this case. Is
there any way to add more than three conditional make-up criteria to a
text
field?


I was about to to say No, Conditional Formatting can only
deal with the default color plus three conditional colors.
You can have as many conditions as you want by using a
function in the Expression Is option, but that won't help
get more than three colors. However, a bolt from out of the
blue gave me an idea ;-)

We can combine Conditional Formatting with the old A2
technique of stacking text boxes behind the one you want to
color.

Note that when a code text box has the focus, it's own
BackColor will be displayed, so be sure to choose that as
desired FIRST, then set its BackStyle property to
Transparent.

Then add another text box and set its control source
expression to something like:
=IIf(code Between 1 And 3, "AAAAA", Null)
Set the text box's FontName to LaBox (available at
http://www.mvps.org/access/forms/frm0055.htm) and
set the FontSize to 12 or so. Now, use CF to add three
conditions:
Expression Is:
Code: 
=1 ForeColor for this code value
Expression Is: [code]=2 ForeColor for this code value
Expression Is: [code]=3 ForeColor for this code value

Repeat this with two more text boxes, one for codes 4-6 and
the other for codes 7-8.

Test the form to make sure you are getting the desired
colors. After that is all working the way you want, set
these three text box's BackStyle property to Transparent.
This must be done last because changing a setting in CF
resets a text box's BackStyle to Normal.

Finally, size and position these funky text boxes exactly on
top of the code text box and use Format - Send to Back to
put them behind the code text box.[/QUOTE][/QUOTE]
[/QUOTE]
Click to expand...
Click to expand...
Click to expand...
 
Thanks Marshall for the sample!

I had a quick peek at it this morning. When I opened the sample form the
display of the TextBox controls contents was garbled. You seems to be using
a Font named laBox.

I was surprised though, that CF was not causing the continual rendering loop
I had seen in the past.
Perhaps they fixed this issue in A2003. I will test earlier versions
tomorrow.

--

HTH
Stephen Lebans
http://www.lebans.com
Access Code, Tips and Tricks
Please respond only to the newsgroups so everyone can benefit.
 
Stephen said:
Thanks Marshall for the sample!

I had a quick peek at it this morning. When I opened the sample form the
display of the TextBox controls contents was garbled. You seems to be using
a Font named laBox.

I was surprised though, that CF was not causing the continual rendering loop
I had seen in the past.
Perhaps they fixed this issue in A2003. I will test earlier versions
tomorrow.
Click to expand...


Stephen,

The LaBox font is available at
http://www.mvps.org/access/forms/frm0055.htm

It is a custom font consisting of various height and width
solid block characters that a helpful person, Apostrophe, in
the Fonts newsgroup volunteered to create just for this kind
of use.

I was using A2002.
 
Back
Top