I have a database that has information for the past 5 years and will continue 
on. I need to color code the background of the box depending on the value of 
the year in the box. The text box is labeled YearAdded. 

For instance:
If the value in the field is 2006, the background is yellow
If the value in the field is 2007, the background color is blue

and so on...

I've seen so many posts, but I haven't found one that is able to do this.

Can anyone help?

chickalina wrote:

>I have a database that has information for the past 5 years and will continue 
>on. I need to color code the background of the box depending on the value of 
>the year in the box. The text box is labeled YearAdded. 
>
>For instance:
>If the value in the field is 2006, the background is yellow
>If the value in the field is 2007, the background color is blue
>
>and so on...


You can use CF in a convoluted way to do that.

First, make the year text box's BackStyle Transparent.

Then add another text box the same size and position as the
year text box (use Format - Send to Back to put it behind
the year text box).  Set this new text box's BackStyle to
Transparent too.

Now, download and install the special font at:
	http://www.mvps.org/access/forms/frm0055.htm

Set this text box's FontName to LaBox.  Set the control
source expression to this kind of thing:
	=IIf([YearAdded] Between 2008 And 2010,"AAAAAA",Null)
Now you can use CF on this text box to deal with different
colors for those 3 years.
	Expression Is:  [YearAdded] = 2008
	ForeColor:	orange

	Expression Is:  [YearAdded] = 2009
	ForeColor:	purple

	Expression Is:  [YearAdded] = 2010
	ForeColor:	pink

To get colors for another 3 years, repeat all that with
another text box using 2005, 2006 and 2007.

-- 
Marsh
MVP [MS Access]

Thanks for the suggestion... but there is no IF... THEN statement I can use? 

This seems rather labor intensive for a simple database and a simple problem 
since this DB will be used by many people across the country and I cannot 
expect them all to install the font.
M

"Marshall Barton" wrote:

> chickalina wrote:
> 
> >I have a database that has information for the past 5 years and will continue 
> >on. I need to color code the background of the box depending on the value of 
> >the year in the box. The text box is labeled YearAdded. 
> >
> >For instance:
> >If the value in the field is 2006, the background is yellow
> >If the value in the field is 2007, the background color is blue
> >
> >and so on...
> 
> 
> You can use CF in a convoluted way to do that.
> 
> First, make the year text box's BackStyle Transparent.
> 
> Then add another text box the same size and position as the
> year text box (use Format - Send to Back to put it behind
> the year text box).  Set this new text box's BackStyle to
> Transparent too.
> 
> Now, download and install the special font at:
> 	http://www.mvps.org/access/forms/frm0055.htm
> 
> Set this text box's FontName to LaBox.  Set the control
> source expression to this kind of thing:
> 	=IIf([YearAdded] Between 2008 And 2010,"AAAAAA",Null)
> Now you can use CF on this text box to deal with different
> colors for those 3 years.
> 	Expression Is:  [YearAdded] = 2008
> 	ForeColor:	orange
> 
> 	Expression Is:  [YearAdded] = 2009
> 	ForeColor:	purple
> 
> 	Expression Is:  [YearAdded] = 2010
> 	ForeColor:	pink
> 
> To get colors for another 3 years, repeat all that with
> another text box using 2005, 2006 and 2007.
> 
> -- 
> Marsh
> MVP [MS Access]
> .
> 

Also, the AAAAAA is in the box and the color only shows when you are in the 
box, it's not a permanent thing... 

I know I'm doing something wrong, just can't figure out what.

Thanks so much!

"Marshall Barton" wrote:

> chickalina wrote:
> 
> >I have a database that has information for the past 5 years and will continue 
> >on. I need to color code the background of the box depending on the value of 
> >the year in the box. The text box is labeled YearAdded. 
> >
> >For instance:
> >If the value in the field is 2006, the background is yellow
> >If the value in the field is 2007, the background color is blue
> >
> >and so on...
> 
> 
> You can use CF in a convoluted way to do that.
> 
> First, make the year text box's BackStyle Transparent.
> 
> Then add another text box the same size and position as the
> year text box (use Format - Send to Back to put it behind
> the year text box).  Set this new text box's BackStyle to
> Transparent too.
> 
> Now, download and install the special font at:
> 	http://www.mvps.org/access/forms/frm0055.htm
> 
> Set this text box's FontName to LaBox.  Set the control
> source expression to this kind of thing:
> 	=IIf([YearAdded] Between 2008 And 2010,"AAAAAA",Null)
> Now you can use CF on this text box to deal with different
> colors for those 3 years.
> 	Expression Is:  [YearAdded] = 2008
> 	ForeColor:	orange
> 
> 	Expression Is:  [YearAdded] = 2009
> 	ForeColor:	purple
> 
> 	Expression Is:  [YearAdded] = 2010
> 	ForeColor:	pink
> 
> To get colors for another 3 years, repeat all that with
> another text box using 2005, 2006 and 2007.
> 
> -- 
> Marsh
> MVP [MS Access]
> .
> 

chickalina wrote:

>Thanks for the suggestion... but there is no IF... THEN statement I can use? 
>
>This seems rather labor intensive for a simple database and a simple problem 
>since this DB will be used by many people across the country and I cannot 
>expect them all to install the font.


It definitely is convoluted, but I have not found any other
way to get more than four colors in a text box on a
continous form.

The reason you can not use VBA code for this kind of effect
is because a text box only has one set of properties, so
when you set a property in code, it applies to all the
"rows" in the form.

Before Conditional Formatting was introduced, we had to
stack a separate text box for each different color.  With
CF, we can use 3 colors on one text box.  I just ran a quick
check in A2010 Beta and the CF limit seems to have been
raised to substantially more than 3 so the convoluted games
may come to an end.
 
-- 
Marsh
MVP [MS Access]

chickalina wrote:

>Also, the AAAAAA is in the box and the color only shows when you are in the 
>box, it's not a permanent thing... 
>
>I know I'm doing something wrong, just can't figure out what.


There are several text boxes involved, which one are you
talking about?

Did you set the BackStyle of all of the text boxes to
Tranparent?

Note that the data text box should display it's own
BackColor when it has the focus (that's the way Transparent
works).  It should display the CF color from the other text
boxes on all the other rows.

-- 
Marsh
MVP [MS Access]

Here is what I'm using:

YearAdded - is the text box where the year gets entered by a user

In the first text box (which is set to transparent):

=IIf([YearAdded] Between 2008 And 2010,"AAAAAA",Null)

I then apply conditional formatting for each of the years:

2008, 2009 and 2010

It displays both the 2008 and the AAAAAA in the box, one on top of the 
other. It also does not show the color unless the cursor is in the year added 
text box.

So am I supposed to replace the AAAAAA with something?

Thanks!
M

"Marshall Barton" wrote:

> chickalina wrote:
> 
> >Also, the AAAAAA is in the box and the color only shows when you are in the 
> >box, it's not a permanent thing... 
> >
> >I know I'm doing something wrong, just can't figure out what.
> 
> 
> There are several text boxes involved, which one are you
> talking about?
> 
> Did you set the BackStyle of all of the text boxes to
> Tranparent?
> 
> Note that the data text box should display it's own
> BackColor when it has the focus (that's the way Transparent
> works).  It should display the CF color from the other text
> boxes on all the other rows.
> 
> -- 
> Marsh
> MVP [MS Access]
> .
> 

chickalina wrote:

>Here is what I'm using:
>
>YearAdded - is the text box where the year gets entered by a user
>
>In the first text box (which is set to transparent):
>
>=IIf([YearAdded] Between 2008 And 2010,"AAAAAA",Null)
>
>I then apply conditional formatting for each of the years:
>
>2008, 2009 and 2010
>
>It displays both the 2008 and the AAAAAA in the box, one on top of the 
>other. It also does not show the color unless the cursor is in the year added 
>text box.
>
>So am I supposed to replace the AAAAAA with something?


It sounds like you did not install the LaBox font and/or did
not set the "other" text box's FontName to LaBox.

The "AAAAA" is the largest solid block character in the font
and should fill the "other" text box's entire area.  If
there is an uncolored area on the right side if the text
box, add more AAA to the string.  If there is an uncikired
aread at the top/bottom make the FontSize bigger.

All of the text boexes involved in this process need to have
their BackStyle set to Transparent.

-- 
Marsh
MVP [MS Access]

Thank you for the information Marshall.... I don't want to sound like a pain, 
but the only problem why I cannot use the LABox font is because I don't know 
who all will be using this application in our offices across the country, so 
I will not be able to install on every machine. Is there a way around this?



"Marshall Barton" wrote:

> chickalina wrote:
> 
> >Here is what I'm using:
> >
> >YearAdded - is the text box where the year gets entered by a user
> >
> >In the first text box (which is set to transparent):
> >
> >=IIf([YearAdded] Between 2008 And 2010,"AAAAAA",Null)
> >
> >I then apply conditional formatting for each of the years:
> >
> >2008, 2009 and 2010
> >
> >It displays both the 2008 and the AAAAAA in the box, one on top of the 
> >other. It also does not show the color unless the cursor is in the year added 
> >text box.
> >
> >So am I supposed to replace the AAAAAA with something?
> 
> 
> It sounds like you did not install the LaBox font and/or did
> not set the "other" text box's FontName to LaBox.
> 
> The "AAAAA" is the largest solid block character in the font
> and should fill the "other" text box's entire area.  If
> there is an uncolored area on the right side if the text
> box, add more AAA to the string.  If there is an uncikired
> aread at the top/bottom make the FontSize bigger.
> 
> All of the text boexes involved in this process need to have
> their BackStyle set to Transparent.
> 
> -- 
> Marsh
> MVP [MS Access]
> .
> 

chickalina wrote:
>Thank you for the information Marshall.... I don't want to sound like a pain, 
>but the only problem why I cannot use the LABox font is because I don't know 
>who all will be using this application in our offices across the country, so 
>I will not be able to install on every machine. Is there a way around this?


Did you read my article about all the reasons why other
fonts are way less than ideal?  If you want to find a simple
non solid block font solution, you will have to wait for
A2010 to come out and hope that the new CF doesn't get cut
from the final release..

Note that, unlike most other fonts, LaBox is in the public
domain so you can send it to your users without licensing
concerns.  I never had a distribution problem like yours,
but there are lots of other people that are knowledgable
about using install scripts to make sure each user has all
needed files.  Of particular note is Tony's very popular
Auto FE Updater at  http://autofeupdater.com/

-- 
Marsh
MVP [MS Access]

OK... I read what you wrote again and I did what you said to do with the 
transparent and changing the font. I have a colored box now, but it's black, 
not the colors I indicated in CF.
I'm still going to have problems with undefined users though.
M

"Marshall Barton" wrote:

> chickalina wrote:
> >Thank you for the information Marshall.... I don't want to sound like a pain, 
> >but the only problem why I cannot use the LABox font is because I don't know 
> >who all will be using this application in our offices across the country, so 
> >I will not be able to install on every machine. Is there a way around this?
> 
> 
> Did you read my article about all the reasons why other
> fonts are way less than ideal?  If you want to find a simple
> non solid block font solution, you will have to wait for
> A2010 to come out and hope that the new CF doesn't get cut
> from the final release..
> 
> Note that, unlike most other fonts, LaBox is in the public
> domain so you can send it to your users without licensing
> concerns.  I never had a distribution problem like yours,
> but there are lots of other people that are knowledgable
> about using install scripts to make sure each user has all
> needed files.  Of particular note is Tony's very popular
> Auto FE Updater at  http://autofeupdater.com/
> 
> -- 
> Marsh
> MVP [MS Access]
> .
> 

chickalina wrote:

>OK... I read what you wrote again and I did what you said to do with the 
>transparent and changing the font. I have a colored box now, but it's black, 
>not the colors I indicated in CF.


Black?  Where did that come from?  Maybe you set the
BackColor in CF instead of the ForeColor as I said?

-- 
Marsh
MVP [MS Access]

The backcolor doesn't remain... it disappears when you try and type a new 
year in the text box. You have to completely exit out of Access and come back 
in.  When you edit it again, the same thing happens. 

I removed the As in between the "AAAAA" and now they are gone.

"Marshall Barton" wrote:

> chickalina wrote:
> 
> >I have a database that has information for the past 5 years and will continue 
> >on. I need to color code the background of the box depending on the value of 
> >the year in the box. The text box is labeled YearAdded. 
> >
> >For instance:
> >If the value in the field is 2006, the background is yellow
> >If the value in the field is 2007, the background color is blue
> >
> >and so on...
> 
> 
> You can use CF in a convoluted way to do that.
> 
> First, make the year text box's BackStyle Transparent.
> 
> Then add another text box the same size and position as the
> year text box (use Format - Send to Back to put it behind
> the year text box).  Set this new text box's BackStyle to
> Transparent too.
> 
> Now, download and install the special font at:
> 	http://www.mvps.org/access/forms/frm0055.htm
> 
> Set this text box's FontName to LaBox.  Set the control
> source expression to this kind of thing:
> 	=IIf([YearAdded] Between 2008 And 2010,"AAAAAA",Null)
> Now you can use CF on this text box to deal with different
> colors for those 3 years.
> 	Expression Is:  [YearAdded] = 2008
> 	ForeColor:	orange
> 
> 	Expression Is:  [YearAdded] = 2009
> 	ForeColor:	purple
> 
> 	Expression Is:  [YearAdded] = 2010
> 	ForeColor:	pink
> 
> To get colors for another 3 years, repeat all that with
> another text box using 2005, 2006 and 2007.
> 
> -- 
> Marsh
> MVP [MS Access]
> .
> 

I deleted the original text boxes I made and created new ones from scratch. 
They all work now....

Changed all the BackStyles to Transparent (even the "yearadded" text box 
where the information is entered)
Changed the font to LABox
Used the IIF statement for the three conditions and the AAA to designate the 
length of the color
Set the three expressions in CF to the [yearadded] text box and changed the 
forecolor.

Everything is working perfectly now.... thank you so much for your help and 
your patience!
M

"Marshall Barton" wrote:

> chickalina wrote:
> 
> >OK... I read what you wrote again and I did what you said to do with the 
> >transparent and changing the font. I have a colored box now, but it's black, 
> >not the colors I indicated in CF.
> 
> 
> Black?  Where did that come from?  Maybe you set the
> BackColor in CF instead of the ForeColor as I said?
> 
> -- 
> Marsh
> MVP [MS Access]
> .
>