Friday, June 10, 2016

How do I use Conditional Formatting in Access

Traditionally, conditional formatting in Access was accomplished through the use of VBA code.  If I wanted to change the format of a field based on it’s value, I’d do something like this to change the background color of the Balance field based on its value.

Select Case Me!txtBal
   Case Is < 2500
      Me!txtBal.BackColor = vbWhite
   Case Is < 5000
      Me!txtBal.BackColor = vbGreen
   Case Is < 7500
      Me!txtBal.BackColor = vbYellow
   Case Is >= 7500
      Me!txtBal.BackColor = vbRed
End Select

or maybe this to format fields based on the value in another field (Check14).

If Me!Check14 = True Then
  Me!txtDesc.BackColor = vbRed
  Me!Acct.ForeColor = vbRed
Else
  Me!txtDesc.BackColor = vbBlue
  Me!Acct.ForeColor = vbBlue
End If

Which gives me the following on one record

image

But this on the next record

image

This works well in Single Form view, but not so well in Continuous Form View or Datasheet View.

image

image

Datasheet View has no formatting at all.

image

The problem is that formatting with VBA affects all records the same. This is fine in Single Form view, because you can only see one.

Conditional Formatting

So to solve this problem, Microsoft introduced Conditional Formatting to Access Forms and Reports. I’m going to concentrate on forms here, but the same applies to reports.

Conditional Formatting is built into the form controls themselves.  So to set it, you need to open the form in Design View to get at the control properties.

You need to select the control (in this case a textbox) , the FORMAT tab on the Ribbon, and then Conditional Formatting.

image

You’ll get a dialog box like this.

image

Click the New Rule button and you will get yet another dialog box

image

In this article, I’m goin to concentrate on comparing to values within the current record, so I’ll leave the top rule type selected.

The next step is to choose the Field Value Is drop down box.  It will have 3 choices:

image

  1. Field Value Is: Compares the selected control to some hard coded value(s)
  2. Expression Is: Compares the selected control to the value of another control.
  3. Field Has Focus: Determines whether the control is currently selected.

Field Value Is

Comparing the selected control to some hard coded value(s)

So first, I’m going to change the formatting a field (Balance) based on the value it contains. If you recall from above, the Balance field had the following conditions.

  txtBal < 2500 –> White
  txtBal < 5000 –> Green
  txtBal < 7500 –> Yellow
  txtBal >= 7500 –> Red

I just need to replicate this logic in the Formatting Rule dialog box. First, I’ll make the following selections:image

Next, I’ll set the background color image to white

image

Then click OK.

image

Next, create another New Rule for the next condition

image

Giving you this result.

image

Complete the rest of the conditions. It should look like this.

image

Which results in this Continuous View

image

I can also modify multiple properties for each rule

image

image

Expression Is:

Comparing the selected control to the value of another control

Another common  problem is to change formatting of a field base on the value in another field.  Above, in VBA, I formatted the Acct and Desc fields with logic as follows:

If the check box is checked (True) Then
  the background color of Desc becomes Red
  the text color of Acct becomes Blue

If the check box is not checked (False) Then
  the background color of Desc becomes Blue
  the text color of Acct becomes Red
End If

I can use the Expression Is option to accomplish this.

Select the field you want to format (Desc) and open the Conditional Formatting editor. Create a New Rule.

image

Select Expression Is

image

The ellipsis at the end will open the Expression Builder

Make the following selections:

image

image

image

The result is [Check14] = True.  I don’t have to use the Expression Builder.  I could just type it in the expression field.  But the Expression Builder is a great tool to investigate the range of expressions that can be used for formatting.

Then finish the formatting

image

Create another rule for the False Condition [Check14] = False, and format it appropriately.

image

Repeat the process for field Acct.

image

And your formatting is done.

image

Field Has Focus

Determines whether the control is currently selected.

Lastly, I can format based on whether or not a form control has the focus.  This is great for highlighting the currently selected field.

image 

Clicking anywhere in the table will highlight the selected field.  Now, I have to set the conditional formatting for each field, which would be tedious except that Access allows me to set the formatting for multiple fields at the same time.

In Design View, select all the textboxes in the Detail section.

image

Then go to the Conditional Formatting editor. Notice how the selection for formatting says Multiple.

image

Create a New Rule and selecting Has Focus  and formatting it as shown

image

Click OK here and in the Rules Manager and I’m done.

Highlight Selected Record

To highlight the selected record, I do essentially the same thing with a few modifications.

Add an unbound textbox control (txtBackColor3) to the form and put it behind the other control.  Make sure this unbound control is the same shape as the Detail section. (I’ve shown it below the other text boxes here to illustrate.)

image

Then set the Back Style property of the other textboxes to Transparent.

image

It also requires one bit of VBA code.  In the OnCurrent event of the form, add this:

Private Sub Form_Current()
    If Not Me.NewRecord Then
        Me!txtBackColor3.ControlSource = "=[txtGLID] = " & Me!GLID
    Else
        Me!txtBackColor3.ControlSource = "=False"
   End If
End Sub

What does this do?

The textbox that displays the primary key of the record (GLID) is txtGLID. If the value of txtGLID matches the GLID of the underlying recordset, set the value of txtBackColor3 to TRUE. If it’s not, set the value to FALSE.

Now, I can make a Conditional Format that tests the value of txtBackColor3 and sets the Fill to Green if TRUE.

image

Now just make sure the txtBackColor3 control is moved behind the other controls.

image

And it’s done.

image

If the conditional formatting of the other textboxes are set to green fill when it has the focus (as in Field Has Focus example above), the currently selected field will also be green.  If not, the selected field in the selected row will be white.

image

Sample Database

You can find a sample database illustrating all of these techniques here:

http://www.rogersaccesslibrary.com/forum/topic375.html

No comments: