Changing the color of a field

[AT76]

I would like to change the color of a field in my access database depending whether the user makes a change to that field or not. Here's what I got:

On the ONFOCUS event of the field I have:

Private Sub LocationID_GotFocus()
Me.LocationID.BackColor = 13434828
End Sub

This changes the color of the field to green when the user is on that field. Then on the LostFocus event of that field I have the following event:

Private Sub LocationID_LostFocus()
If Dirty Then
Me.LocationID.BackColor = 10092543
Else
Me.LocationID.BackColor = 16777215
End If
End Sub

If the user changes the value the field changes color to yellow. If no change is done the field stays white when the user moves on.

The problem I'm having is one a user has changed one field and that field changes color, the rest of the fields change to yellow automatically whether or not a change has been done to that field.

Can someone tell me why I'm getting this output?

Thank you!

 

[Appollo14]

Hi VBA,

Just one question for a start - what type of form is this? are you using single record, continuos or datasheet view?

Regards,
Noel.

 

[AT76]

Hi Noel,

This is a single for a single record in Form View.

 

[Appollo14]

Hi VBA,

The Dirty event applies to the RECORD not the field. Have you tried just putting

Me.DatePromised.BackColor = 10092543

in the onchange event?

Regards,
Noel.

 

[AT76]

Yes Noel,

I did try that. Here's what happened:

On the OnGotFocus Event I put GREEN. This tells the user which cell he/she is on.

On the OnChange event I put YELLOW. This works fine. The only problem is that if the user Does not change the field it STAYS Green.

The other scenerio I tried was the same as above with an addition:

On the OnLostFocus I put White so if the user does not make a change the field does not stay GREEN and goes back to WHITE. The problem with this is that If the field is modified it does not change to YELLOW. It stays as WHITE.

Any suggestions would be greatly appreciated!

Regards,

~ VBAR

 

[Appollo14]

How about using an IF statment (on the LostFocus Event) to find out the current colour - If the cell is green then turn it white if it's yellow leave yellow.

Regards,
Noel.

 

[crobg]

I was playing around with these ideas and found that if I goto to the field more than once it would revert back to white. I also found that if I go to the nexe record that the fields change before would remain the same. Here is what I came up with to solve these problems. Note: I only worked with one field named "Field1".

Dim cstBeforeFocusColor As Long 'Used to check the state of the field at entry.
Option Compare Database

Private Sub Field1_Change()
Me.Field1.BackColor = 10092543 'Active field changed to Yellow on change.
End Sub

Private Sub Field1_GotFocus()
cstBeforeFocusColor = Me.Field1.BackColor 'Save the current color.
Me.Field1.BackColor = 13434828 'Change to active field color.
End Sub

Private Sub Field1_LostFocus()
If Me.Field1.BackColor = 13434828 Then 'Checking for green.  If no change then revert to previous color.
    Me.Field1.BackColor = cstBeforeFocusColor
End If
End Sub

Private Sub Form_Current() 'Cycle through controls and set text box controls back to white.
Dim X As Long

For X = 0 To Me.Controls.Count - 1
    Debug.Print X
    Debug.Print Me.Controls(X).Name
    If Me.Controls(X).ControlType = acTextBox Then
        Me.Controls(X).BackColor = 16777215
    End If
Next

End Sub

 

[AT76]

crobg,

Thank you for your help. The changing of colors works like a charm now! The only subroutine I could not get to work was: Private Sub Form_Current. When I move to a new record the fields that were yellow in the previous record stay yellow in the new record. Any ideas why this might be. Again thank you for your help!!!

 

[crobg]

The sub "Private Sub Form_Current" is the "On Current" event of the form. This code should clear the back color of all of the Text Boxes on your form.

If you have combo or list boxes you will need to modify the code to include these. Search help on ControlType to get the other control type names.

 

[AT76]

Thank you crobg. You were correct. For Combo I used: acComboBox and for list boxes I used: acListBox.

You were lots of help!!

Thanks a million!