Easy way to change background color for multiple fields

[swenger]

I have a data entry form with many fields. I would like to have the fields with data colored and the empty ones white.



I created the following code for Form_Current()

If [Field1] <> "" Then
[Field1].BackColor = RGB(255, 255, 204)
Else
[Field1].BackColor = RGB(255, 255, 255)
End If

If [Field2] <> "" Then
[Field2].BackColor = RGB(255, 255, 204)
Else
[Field2].BackColor = RGB(255, 255, 255)
End If

I repeated the above for each field on the form.

I have 2 questions

1. Is there an easier way to accomplish this without repeating this for each field.
2. After data is entered in a field and we tab to the next field, is there an easy way to have the previous field change color besides repeating the code on a lost focus or similar.

Thanks,

[pbaldy]

I would use Conditional Formatting, on the ribbon.

[swenger]

I guess I was concerned that if this was opened on different pc's this may not always work where I figured VBA would work always. I also usually preferred code because it is easier to see when others try to analyze the database when I may not be available and conditional formatting is not visibly identified as being used. Thanks for the suggestion.

[pbaldy]

Up to you. You can create a function and call it from the current event plus all the after update events. It can look like your code, or use a loop of all controls and the tag property to identify which controls to test.

[CJ_London]

you can simplify your if statements to one line per control which cuts down on the typing

Field1.BackColor = RGB(255, 255, 204 - ((Nz(Field1, "") <> "") * 51))

but I agree with Paul - conditional formatting is the way to go - will also work for a continuous form or datasheet which coding will not.

if this was opened on different pc's this may not always work

never had a problem myself

conditional formatting is not visibly identified as being used

put a note in the form module? It also appears in the documenter - for the control properties - although not what the rules actually are, but would serve as a warning for a later developer.

You can also use the format property for a control - not the back colour but to display some text e.g.

;;;[Red]"This field must be completed"