How to change background color in form based on a control value?

[Guest]

I have an Access form that I would like to chnage the background color of a
record based on the value in one or more of the controls.

 

[fredg]

I have an Access form that I would like to chnage the background color of a
record based on the value in one or more of the controls.

If you have Access 2000 or newer you can use the control's Conditional
Formatting property.
Select the control.
Click Format + Conditional Formatting.
Set the Condition1 drop down box to either
Field Value Is
or
Expression Is.
write the value or the expression (without an = sign) in the text box.
Select whatever colors you want.
Exit.
You may need to do the above in more than one control on the form.

 

[Michael Brown via AccessMonster.com]

I have the same problem, but I'm stuck with Access 97. Any ideas?

 

[fredg]

I have the same problem, but I'm stuck with Access 97. Any ideas?

In Access 97 this cannot be done if the form is in Continuous Form
view.

If the form is in Single Form view, then code the Form's Current
event:
If [SomeControl] = some value then
[ControlA].BackColor = vbRed
[ControlB].BackColor = vbRed
etc.
Else
[ControlA].BackColor = vbWhite
[ControlB].BackColor = vbWhite
etc.
End If

Place the same code in the AfterUpdate event of any control which will
affect the [SomeControl] criteria value.

 

[Vadimbar]

---------------------------------------------------------------------------------------
This is an old post so forgive me but I had a similar problem and the
solution suggested seemed to work at first. I have the following set up as an
AfetUpdate and Current. I have two fields one is a date field
[Date_Response_is_Due] while the other is a text field [StatusID]. If the
text field is blank then the date field should stay white. If the text field
says "Completed" then the date field should stay White (no change). However,
if the text field says anyhting else then the Date field should be in Red if
its date has passed. My code is below.

Everything works except when I scroll through my records and the Date field
has not passed yet it should stay White but it does not until I hit refresh.
All other situations seem to work fine. Why do I need to do a refresh? Sorry
for the long post. FYI I am using MS Office / Access 2003

Private Sub Form_AfterUpdate()
'----------------------------------------------------------
' Changes the color of the Date due button if its past the
' deadline and has not been completed.
'----------------------------------------------------------
If IsNull([Date_Response_is_Due]) Then
[Date Response is Due].BackColor = vbWhite
Else
If ([StatusID] <> "Completed" And [Date Response is Due] < Now()) Then
[Date Response is Due].BackColor = vbRed
End If
End If
End Sub

Private Sub Form_Current()
'----------------------------------------------------------
' Changes the color of the Date due button if its past the
' deadline and has not been completed.
'----------------------------------------------------------
If IsNull([Date_Response_is_Due]) Then
[Date Response is Due].BackColor = vbWhite
Else
If ([StatusID] <> "Completed" And [Date Response is Due] < Now()) Then
[Date Response is Due].BackColor = vbRed
End If
End If
End Su
---------------------------------------------------------------------------------------

I have the same problem, but I'm stuck with Access 97. Any ideas?

In Access 97 this cannot be done if the form is in Continuous Form
view.

If the form is in Single Form view, then code the Form's Current
event:
If [SomeControl] = some value then
[ControlA].BackColor = vbRed
[ControlB].BackColor = vbRed
etc.
Else
[ControlA].BackColor = vbWhite
[ControlB].BackColor = vbWhite
etc.
End If

Place the same code in the AfterUpdate event of any control which will
affect the [SomeControl] criteria value.

 

[Vadimbar]

Folks I added the following and it seems to be good: Thank you all for your
continued support!!!

If IsNull([Date_Response_is_Due]) Then
[Date Response is Due].BackColor = vbWhite
Else
If ([StatusID] <> "Completed" And [Date Response is Due] < Now()) Then
[Date Response is Due].BackColor = vbRed
Else
[Date Response is Due].BackColor = vbWhite
End If
End If
End Su
------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------
This is an old post so forgive me but I had a similar problem and the
solution suggested seemed to work at first. I have the following set up as an
AfetUpdate and Current. I have two fields one is a date field
[Date_Response_is_Due] while the other is a text field [StatusID]. If the
text field is blank then the date field should stay white. If the text field
says "Completed" then the date field should stay White (no change). However,
if the text field says anyhting else then the Date field should be in Red if
its date has passed. My code is below.

Everything works except when I scroll through my records and the Date field
has not passed yet it should stay White but it does not until I hit refresh.
All other situations seem to work fine. Why do I need to do a refresh? Sorry
for the long post. FYI I am using MS Office / Access 2003

Private Sub Form_AfterUpdate()
'----------------------------------------------------------
' Changes the color of the Date due button if its past the
' deadline and has not been completed.
'----------------------------------------------------------
If IsNull([Date_Response_is_Due]) Then
[Date Response is Due].BackColor = vbWhite
Else
If ([StatusID] <> "Completed" And [Date Response is Due] < Now()) Then
[Date Response is Due].BackColor = vbRed
End If
End If
End Sub

Private Sub Form_Current()
'----------------------------------------------------------
' Changes the color of the Date due button if its past the
' deadline and has not been completed.
'----------------------------------------------------------
If IsNull([Date_Response_is_Due]) Then
[Date Response is Due].BackColor = vbWhite
Else
If ([StatusID] <> "Completed" And [Date Response is Due] < Now()) Then
[Date Response is Due].BackColor = vbRed
End If
End If
End Sub
---------------------------------------------------------------------------------------

I have the same problem, but I'm stuck with Access 97. Any ideas?

In Access 97 this cannot be done if the form is in Continuous Form
view.

If the form is in Single Form view, then code the Form's Current
event:
If [SomeControl] = some value then
[ControlA].BackColor = vbRed
[ControlB].BackColor = vbRed
etc.
Else
[ControlA].BackColor = vbWhite
[ControlB].BackColor = vbWhite
etc.
End If

Place the same code in the AfterUpdate event of any control which will
affect the [SomeControl] criteria value.