Changing font colors in Continous Form for one record

[DianaE]

I have a continuous form that has a lot of 0.00 fields and the women I work
with say it makes them dizzy. I know how to make the font match the
background by changing the font color but when they put a number in the field
I want the font to change to another color so that it shows up on the screen.
When I do this the whole column on the continous form changes the font to
show up. I just want the one field in the one record to change. Can someone
help me please. PSS...I need this to happen in VB.

 

[Jack Leach]

Check out the Conditional Formatting... this should get you what you are
looking for.

hth
--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)

 

[John Spencer MVP]

If the field is numeric and you don't mind being limited to a few colors you
can set the format of the control to something like the following

#.00[Red];-#.00[Black];#[White];#[Green]

That will show Red for positive numbers, black for negative numbers, white for
zero, and green for nulls. Other colors available are Yellow, Cyan, and Blue.
There might me one or two more, but at this point I really don't recall what
they might be.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[DianaE]

I am sorry but I do not know where this information is to be put into.
Besides, with a continuous form, whatever I put in reacts with the hole
column of items and not just the one field within the one record within the
continuous form.

If the field is numeric and you don't mind being limited to a few colors you
can set the format of the control to something like the following

#.00[Red];-#.00[Black];#[White];#[Green]

That will show Red for positive numbers, black for negative numbers, white for
zero, and green for nulls. Other colors available are Yellow, Cyan, and Blue.
There might me one or two more, but at this point I really don't recall what
they might be.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I have a continuous form that has a lot of 0.00 fields and the women I work
with say it makes them dizzy. I know how to make the font match the
background by changing the font color but when they put a number in the field
I want the font to change to another color so that it shows up on the screen.
When I do this the whole column on the continous form changes the font to
show up. I just want the one field in the one record to change. Can someone
help me please. PSS...I need this to happen in VB.

 

[DianaE]

The operative word here is Continuous Form. The conditional formating will
change the font colors but it will change the font in a field on all the
records in the continuous form. I need the font to change in a field, in a
record, within the continuous form.

 

[John Spencer MVP]

Conditional formatting in a continuous form should change row by row based on
the condition.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[John Spencer MVP]

If you have a continuous form and want to change the format of one control
based on the value in the field bound to the control you open the form in
design view.

--Right Click on the control and show properties
--Click on the Format tab of the properties
--Enter the specified format in the Format property
FORMAT controls how the data appears when the control does NOT have the focus.
When the control DOES have the focus the Input Mask controls the display of
the value (if you have an input mask).

Try it. It should work on a continuous form. And conditional formatting
should also work. If it doesn't then you are doing something we don't understand.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I am sorry but I do not know where this information is to be put into.
Besides, with a continuous form, whatever I put in reacts with the hole
column of items and not just the one field within the one record within the
continuous form.

If the field is numeric and you don't mind being limited to a few colors you
can set the format of the control to something like the following

#.00[Red];-#.00[Black];#[White];#[Green]

That will show Red for positive numbers, black for negative numbers, white for
zero, and green for nulls. Other colors available are Yellow, Cyan, and Blue.
There might me one or two more, but at this point I really don't recall what
they might be.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I have a continuous form that has a lot of 0.00 fields and the women I work
with say it makes them dizzy. I know how to make the font match the
background by changing the font color but when they put a number in the field
I want the font to change to another color so that it shows up on the screen.
When I do this the whole column on the continous form changes the font to
show up. I just want the one field in the one record to change. Can someone
help me please. PSS...I need this to happen in VB.

 

[DianaE]

Could you please give me an idea what I am look for then. I guess I am
totally lost on this one.

 

[John Spencer MVP]

Diana,

--Open the form in design view
--click on the control you want to apply conditional formatting to
--Select Conditional Formatting from the Format menu
--In the dialog
First box: Field Value is
Second Box: Equal to
Third box: 0
Click on the letter A drop down arrow and select white or the color that
matches the background of the control
--Click the OK button
--Open the form in continuous view and see if that works as you want. If so,
save, if not make adjustments.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[DianaE]

OK...I am getting there. I had the format set to Currency, but if I type
into the format property:

#.00[Red];-#.00[Black];#[White];#[Green]

The numbers do not change to the colors indicated. In fact the numbers show
up with the word [Red] attached to them and so on. The one good thing is
that the 0.00 fields show up as empty fields although the 0.00 is still there
(at least I think so since a 0 appears when you click in the field).

If you have a continuous form and want to change the format of one control
based on the value in the field bound to the control you open the form in
design view.

--Right Click on the control and show properties
--Click on the Format tab of the properties
--Enter the specified format in the Format property
FORMAT controls how the data appears when the control does NOT have the focus.
When the control DOES have the focus the Input Mask controls the display of
the value (if you have an input mask).

Try it. It should work on a continuous form. And conditional formatting
should also work. If it doesn't then you are doing something we don't understand.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I am sorry but I do not know where this information is to be put into.
Besides, with a continuous form, whatever I put in reacts with the hole
column of items and not just the one field within the one record within the
continuous form.

If the field is numeric and you don't mind being limited to a few colors you
can set the format of the control to something like the following

#.00[Red];-#.00[Black];#[White];#[Green]

That will show Red for positive numbers, black for negative numbers, white for
zero, and green for nulls. Other colors available are Yellow, Cyan, and Blue.
There might me one or two more, but at this point I really don't recall what
they might be.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

DianaE wrote:
I have a continuous form that has a lot of 0.00 fields and the women I work
with say it makes them dizzy. I know how to make the font match the
background by changing the font color but when they put a number in the field
I want the font to change to another color so that it shows up on the screen.
When I do this the whole column on the continous form changes the font to
show up. I just want the one field in the one record to change. Can someone
help me please. PSS...I need this to happen in VB.

 

[JimBurke via AccessMonster.com]

I don't think conditional formatting alone will work for what you want, at
least from the way it works as I understand it. There are three options in
the Condition 1 dropdown when you select Format, Conditional Formatting. The
first two, 'Field Value Is' and 'Expression Is', will affect all rows in the
continuous form (at least when I've used it it works this way). The third
option, 'Field Has Focus', will allow you to apply the format only to the
selected row and field, but it doesn't allow other options along with it. So
if you use that option, the format will be applied to that row only, but it's
applied no matter what the value is. I don't think there's a simple way to
have only the selected row conditionally formatted for certain values.

If there is a way to do conditional formatting thru VBA code, then you could
use the forms On Current event to do it. You could do something like this

Select Case myValue
case 1
turn conditional formatting on, set formatting values for when = 1
case 2
turn conditional formatting on, set formatting values for when = 2...

case else
turn conditional formatting off
End Select

It looks like you can turn it on/off in VBA after doing a little searching.
Here's a link that has an example...

http://blogs.msdn.com/frice/archive/2004/06/08/151178.aspx

OK...I am getting there. I had the format set to Currency, but if I type
into the format property:

#.00[Red];-#.00[Black];#[White];#[Green]

The numbers do not change to the colors indicated. In fact the numbers show
up with the word [Red] attached to them and so on. The one good thing is
that the 0.00 fields show up as empty fields although the 0.00 is still there
(at least I think so since a 0 appears when you click in the field).

If you have a continuous form and want to change the format of one control
based on the value in the field bound to the control you open the form in

[quoted text clipped - 42 lines]

 

[John Spencer MVP]

What version of Access are you using? This worked for me in Access 2003,
Access 2000 and Access 97.

If you want currency then you might need to modify the format a bit to the
following. However, a better solution is to use conditional formatting as
described in my earlier post. Conditional formatting was not available until
Access 2000.

$#,###.00[Black];-$#,###.00[Red];0[White];

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

OK...I am getting there. I had the format set to Currency, but if I type
into the format property:

#.00[Red];-#.00[Black];#[White];#[Green]

The numbers do not change to the colors indicated. In fact the numbers show
up with the word [Red] attached to them and so on. The one good thing is
that the 0.00 fields show up as empty fields although the 0.00 is still there
(at least I think so since a 0 appears when you click in the field).

If you have a continuous form and want to change the format of one control
based on the value in the field bound to the control you open the form in
design view.

--Right Click on the control and show properties
--Click on the Format tab of the properties
--Enter the specified format in the Format property
FORMAT controls how the data appears when the control does NOT have the focus.
When the control DOES have the focus the Input Mask controls the display of
the value (if you have an input mask).

Try it. It should work on a continuous form. And conditional formatting
should also work. If it doesn't then you are doing something we don't understand.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I am sorry but I do not know where this information is to be put into.
Besides, with a continuous form, whatever I put in reacts with the hole
column of items and not just the one field within the one record within the
continuous form.

:

If the field is numeric and you don't mind being limited to a few colors you
can set the format of the control to something like the following

#.00[Red];-#.00[Black];#[White];#[Green]

That will show Red for positive numbers, black for negative numbers, white for
zero, and green for nulls. Other colors available are Yellow, Cyan, and Blue.
There might me one or two more, but at this point I really don't recall what
they might be.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

DianaE wrote:
I have a continuous form that has a lot of 0.00 fields and the women I work
with say it makes them dizzy. I know how to make the font match the
background by changing the font color but when they put a number in the field
I want the font to change to another color so that it shows up on the screen.
When I do this the whole column on the continous form changes the font to
show up. I just want the one field in the one record to change. Can someone
help me please. PSS...I need this to happen in VB.

 

[DianaE]

Jim, John, and Jack

I must thank you all for your help. I am hoping one of these ways will be
the ultimate answer. I do have it working but am not very confident. I have
just changed from Access 2003 to Access 2007 and the difference is just
amazing. Now the learning curve begins again and I am not sure I am ready
for it but will make a good go of everything that comes my way. Again, thank
you all for your help and understanding in getting this through my brain. I
do really appreciate it.

I don't think conditional formatting alone will work for what you want, at
least from the way it works as I understand it. There are three options in
the Condition 1 dropdown when you select Format, Conditional Formatting. The
first two, 'Field Value Is' and 'Expression Is', will affect all rows in the
continuous form (at least when I've used it it works this way). The third
option, 'Field Has Focus', will allow you to apply the format only to the
selected row and field, but it doesn't allow other options along with it. So
if you use that option, the format will be applied to that row only, but it's
applied no matter what the value is. I don't think there's a simple way to
have only the selected row conditionally formatted for certain values.

If there is a way to do conditional formatting thru VBA code, then you could
use the forms On Current event to do it. You could do something like this

Select Case myValue
case 1
turn conditional formatting on, set formatting values for when = 1
case 2
turn conditional formatting on, set formatting values for when = 2...

case else
turn conditional formatting off
End Select

It looks like you can turn it on/off in VBA after doing a little searching.
Here's a link that has an example...

http://blogs.msdn.com/frice/archive/2004/06/08/151178.aspx

OK...I am getting there. I had the format set to Currency, but if I type
into the format property:

#.00[Red];-#.00[Black];#[White];#[Green]

The numbers do not change to the colors indicated. In fact the numbers show
up with the word [Red] attached to them and so on. The one good thing is
that the 0.00 fields show up as empty fields although the 0.00 is still there
(at least I think so since a 0 appears when you click in the field).

If you have a continuous form and want to change the format of one control
based on the value in the field bound to the control you open the form in

[quoted text clipped - 42 lines]

show up. I just want the one field in the one record to change. Can someone
help me please. PSS...I need this to happen in VB.

 

[JimBurke via AccessMonster.com]

She doesn't just want it for the one column, she wants it only for the that
column and for the row that is the current row. I'm pretty sure 'standard'
conditional formatting applies to all rows, except for when using 'Field Has
Focus', but that option doesn't allow you to apply conditions to the values
if you select it - it simply changes the format when the row is current,
regardless of what the value is. Unless I'm misinterpeting something - if so,
my apologies.

What version of Access are you using? This worked for me in Access 2003,
Access 2000 and Access 97.

If you want currency then you might need to modify the format a bit to the
following. However, a better solution is to use conditional formatting as
described in my earlier post. Conditional formatting was not available until
Access 2000.

$#,###.00[Black];-$#,###.00[Red];0[White];

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

OK...I am getting there. I had the format set to Currency, but if I type
into the format property:

[quoted text clipped - 52 lines]

 

[JimBurke via AccessMonster.com]

Just out of curiousity, what exactly did you do to get that to work? Did just
a simple condtional format do it? Just wondering if my understanding of it is
off base. I went and created a very simple On Current proc that will hi-lite
a particular field in red only when it is in the currently selected row and
has a value of 0, but if there's a simpler way to do it without code that
would be preferable. Here's the routine I created, which can be easily
modified to use as many Case's as needed, and can use multiple colors and
other properties as well if needed:

Private Sub Form_Current()

Dim objFrc As FormatCondition
Dim lngRed As Long

lngRed = RGB(255, 0, 0)

FeeAmount.FormatConditions.Delete
Set objFrc = FeeAmount.FormatConditions.Add(acFieldHasFocus)

Select Case FeeAmount
Case 0
With FeeAmount.FormatConditions(0)
.BackColor = lngRed
End With
End Select

End Sub

Jim, John, and Jack

I must thank you all for your help. I am hoping one of these ways will be
the ultimate answer. I do have it working but am not very confident. I have
just changed from Access 2003 to Access 2007 and the difference is just
amazing. Now the learning curve begins again and I am not sure I am ready
for it but will make a good go of everything that comes my way. Again, thank
you all for your help and understanding in getting this through my brain. I
do really appreciate it.

I don't think conditional formatting alone will work for what you want, at
least from the way it works as I understand it. There are three options in

[quoted text clipped - 40 lines]