Monday, March 12, 2018

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
  Me!txtDesc.BackColor = vbBlue
  Me!Acct.ForeColor = vbBlue
End If

Which gives me the following on one record
But this on the next record
This works well in Single Form view, but not so well in Continuous Form View or Datasheet View.
Datasheet View has no formatting at all.
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.
You’ll get a dialog box like this.
Click the New Rule button and you will get yet another dialog box
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:
  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
Then click OK.
Next, create another New Rule for the next condition
Giving you this result.
Complete the rest of the conditions. It should look like this.
Which results in this Continuous View
I can also modify multiple properties for each rule

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.
Select Expression Is
The ellipsis at the end will open the Expression Builder
Make the following selections:
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
Create another rule for the False Condition [Check14] = False, and format it appropriately.
Repeat the process for field Acct.
And your formatting is done.

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.
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.
Then go to the Conditional Formatting editor. Notice how the selection for formatting says Multiple.
Create a New Rule and selecting Has Focus  and formatting it as shown
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.)
Then set the Back Style property of the other textboxes to Transparent.
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
        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.
Now just make sure the txtBackColor3 control is moved behind the other controls.
And it’s done.
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.

Sample Database

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

No comments: