totn Access

MS Access 2003: Change the background color of a text box based on whether a value has been entered

This MSAccess tutorial explains how to change the background color of a text box based on whether a value has been entered in a form in Access 2003 (with screenshots and step-by-step instructions).

Question: In Microsoft Access 2003/XP/2000/97, how can I apply a conditional format on a text box? I want to change the background color to red from white when the text box has not been filled in (null value). How do I do this?

Answer: Because you are trying to change the format of a text box based on whether it has been filled in or not, you can not use the traditional conditional formatting that Access provides.

Instead, you need to create code that checks the value of the text box and changes the background color accordingly.

Let's look at an example.

Microsoft Access

In this example, we have a form called Suppliers. We want to change the background color of the ContactTitle text box if the user has not filled in a value. (ie: If the text box contains a null value).

Microsoft Access

First, you want to view the properties for the Form. Select the property called "On Current". A button with three dots should appear to the right. Click on the button.

Microsoft Access

When the Choose Builder window appears, highlight "Code Builder" and click on the OK button.

Microsoft Access

Next, we want to enter the following code:

If IsNull(ContactTitle) = True Then
   ContactTitle.BackColor = vbRed
Else
   ContactTitle.BackColor = vbWhite
End If

The purpose of this code is to change the background color of the ContactTitle field to red if it is null, or white if it has a value. We place this code on the Form's On Current event so that when you scroll through the records in the Supplier form, the ContactTitle field will reformat itself.

You can close down the Microsoft Visual Basic window.

Next, you need to place similar code on the text box's After Update event.

Microsoft Access

To do this, select the properties for the ContactTitle text box. Click on the property called "After Update". A button with three dots should appear to the right. Click on the button.

Microsoft Access

When the Choose Builder window appears, highlight "Code Builder" and click on the OK button.

Microsoft Access

Now, we want to enter the same code on this event:

If IsNull(ContactTitle) = True Then
   ContactTitle.BackColor = vbRed
Else
   ContactTitle.BackColor = vbWhite
End If

We place this code on the text box's After Update event so that once a user enters a value in the ContactTitle field, the text box will reformat itself again.

Microsoft Access

When we take a look at our form, we see that if the ContactTitle field is blank/null, the background color is red.

Microsoft Access

Once a user enters a value, the background color will change back to white.