Extending the Functionality of Conditional Formatting in Access

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

 

Frank C. Rice
Microsoft Corporation

March 2002

Applies to:
     Microsoft® Access 2000 and later

Summary: Create conditional formatting in Microsoft Access using the user interface and through programming. Also explore ways to programmatically extend these conditions under different circumstances. (9 printed pages)

Contents

Introduction
Creating Conditional Formatting through the User Interface
Programming Conditional Formats
Conclusion

Introduction

Beginning with Microsoft® Access 2000, you are able to apply conditional formatting to combo box and text box controls. This allows you to change certain display characteristics of the data contained in these controls, such as the background color and bold, depending on specific conditions.

There are two ways to create and apply conditional formats. You can select the control and click Conditional Formatting on the Format menu. Using Microsoft Visual Basic® for Applications (VBA), you can also create instances of FormatCondition objects with conditional formatting and add these to the FormatConditions collection.

Each conditionally-formatted control has at least two formats—a default format and a special format when a specific condition is True. A control can have up to three special format conditions. The three-condition limit can be overcome by using VBA code to program these conditions to have different values under different circumstances. This article will first examine creating conditional formatting through the user interface. We will then look at creating conditional formatting in VBA code as well as ways to change the format conditions during run time.

Creating Conditional Formatting through the User Interface

The Conditional Formatting command on the Format menu provides a simple way to create and apply up to three conditional formats. When you click Conditional Formatting on the Format menu, the Conditional Formatting dialog box is displayed.

Figure 1. The Conditional Formatting dialog box

The Default Formatting area shows how the control will be displayed if none of the custom conditional formats conditions are met. The Condition 1 area allows you to add custom conditional formats. As you add conditional formats by clicking Add, the dialog box expands to display up to three conditional formats.

The Conditional Formatting dialog box provides six formatting controls to help you specify each conditional format. Starting from the left in the row of controls, you can apply bold, italic, underline, change the background color, and change the foreground color. You can also enable or disable the control. The drop-down list on the far left allows you to format the control based on the control's field value, its expression value, or whether the control has the focus. You are also provided with the usual equality operators such as equal to (=), greater than (>), between, and so forth. If you apply conditional formatting to a calculated field, you must write an expression using the standard VBA operators.

If your format condition requires an expression, you can use the Expression Builder to help you create the expression. The Expression Builder is available from most places where you write expressions such as in a property sheet of a form or report.

Figure 2 illustrates a form with conditional formatting associated with the Special Customer text box.

Figure 2. Form showing formatting created through the user interface

The format condition changes the background color of the text box based on the value of the Total text box. In this case, the text box displays green if the calculated value of the Total text box is between 300 and 399. Figure 3 illustrates the conditions as defined in the Conditional Formatting dialog box.

Figure 3. Custom formatting based on the value of the Total text box

Programming Conditional Formats

While you can easily create conditional formats using the Conditional Formatting dialog box, there are times when you might want to use VBA code. By creatively using code, you can manage conditional formats so that to the user it appears that there are more than the three conditions allowed. You might also want to use conditional formatting in multiple forms or applications. Creating the conditions in code makes it much easier to apply identical formats to different forms by copying and pasting the code.

Every combo box and text box on a form has a FormatConditions collection containing FormatCondition objects. The FormatConditions collection has an Add method and a Delete method. The FormatCondition object has the Modify method. FormatCondition objects don't have a name property so they are commonly referred to by their index numbers. In addition, FormatCondition objects have property settings such as the Type property that lets you apply a condition based on a field value or expression. For more information on the FormatConditions collection and FormatCondition object, see the FormatConditionsHelp topic on the Microsoft Developer Network.

Let's look at another custom form and the code behind it to see how conditional formats can be created programmatically and how code can be used to provide additional formatting options. Figure 4 shows a form that formats the data in a text box depending on the value of a target number.

Figure 4. Form that applies conditional formatting based on user options

In this form, the user types a number in the Target text box and clicks an option in the Choose an option area. The user then types a number in the Result box and clicks Change. For the first three options, the value in the Result box is compared to the value in the Target box, and depending on whether the two values are less than, equal to, or greater than each other, formats the Result box. For Option 4, when Change is clicked, the current day of the week is compared to the strings Sat and Sun to determine how the Result box is formatted.

The code is located in the Click event of the Change command button and is displayed below:

Private Sub cmdChange_Click()

    ' This subroutine demonstrates the use of FormatCondition objects
    ' to add formatting to a text box. It also illustrates ways you can 
    ' extend the functionality of the three conditional-format limit 
    ' allowed in the FormatConditions collection.
    '
    ' Author: Frank C. Rice
    '
    Dim objFrc As FormatCondition
    Dim lngRed As Long
    Dim lngWhite As Long
    Dim lngBlack As Long
    Dim lngYellow As Long

    ' Set up background and foreground colors.
    lngRed = RGB(255, 0, 0)
    lngWhite = RGB(255, 255, 255)
    lngBlack = RGB(0, 0, 0)
    lngYellow = RGB(255, 255, 0)

    ' Remove any existing format conditions.
    Me![txtResult].FormatConditions.Delete

    ' Create three format objects and add them to the FormatConditions
    ' collection.
    Set objFrc = Me![txtResult].FormatConditions.Add(acFieldValue, _
        acLessThan, Me![txtTarget].Value)
    Set objFrc = Me![txtResult].FormatConditions.Add(acFieldValue, _
        acEqual, Me![txtTarget].Value)
    Set objFrc = Me![txtResult].FormatConditions.Add(acFieldValue, _ 
        acGreaterThan, Me![txtTarget].Value)

    ' Depending on the user's option selection, format the txtResult
    ' box.
    Select Case optgrpChoice.Value

        Case 1
            ' Refer to each format condition by its index.

            With Me![txtResult].FormatConditions(0)
                .FontBold = False
                .FontItalic = True
                .FontUnderline = True
            End With

            With Me![txtResult].FormatConditions(1)
                .FontBold = True
                .FontItalic = False
                .FontUnderline = True
            End With

            With Me![txtResult].FormatConditions(2)
                .FontBold = True
                .FontItalic = True
                .FontUnderline = False
            End With
      
        Case 2

            With Me![txtResult].FormatConditions(0)
                .BackColor = lngRed
                .FontBold = True
                .ForeColor = lngBlack
            End With

            With Me![txtResult].FormatConditions(1)
                .BackColor = lngBlack
                .FontBold = True
                .ForeColor = lngRed
            End With

            With Me![txtResult].FormatConditions(2)
                .BackColor = lngYellow
                .FontBold = True
                .ForeColor = lngRed
            End With
      
        Case 3

            Me![txtResult].FormatConditions(0).Enabled = False
            Me![txtResult].FormatConditions(1).Enabled = True
            Me![txtResult].FormatConditions(2).Enabled = False

        Case 4

            ' Here, we redefine the formatting based on an expression 
         ' comparing today to string values. 
            Me![txtResult].FormatConditions.Delete

            Set objFrc = Me![txtResult].FormatConditions. _
          Add(acExpression, , (Format(Now(), "ddd") = "Sat"))
            Set objFrc = Me![txtResult].FormatConditions. _
          Add(acExpression, , (Format(Now(), "ddd") <> "Sat") And _
                (Format(Now(), "ddd") <> "Sun"))
            Set objFrc = Me![txtResult].FormatConditions. _
          Add(acExpression, , (Format(Now(), "ddd") = "Sun"))

            With Me![txtResult].FormatConditions(0)
                .BackColor = lngYellow
                .FontBold = True
                .ForeColor = lngBlack
            End With

            With Me![txtResult].FormatConditions(1)
                .BackColor = lngBlack
                .FontBold = True
                .ForeColor = lngRed
            End With

            With Me![txtResult].FormatConditions(2)
                .BackColor = lngRed
                .FontBold = True
                .ForeColor = lngBlack
            End With    
    
    End Select

End Sub

Let's examine the code in a little more detail.

First, we declare a variable objFrc as a FormatCondition object. These objects will contain our format conditions. Next, we set up the colors that we will use for formatting the Results box.

' Set up background and foreground colors.
lngRed = RGB(255, 0, 0)
lngWhite = RGB(255, 255, 255)
lngBlack = RGB(0, 0, 0)
lngYellow = RGB(255, 255, 0)

Then we remove any existing FormatCondition objects by using the Delete method of the FormatConditions collection.

Me![txtResult].FormatConditions.Delete

This is necessary to make sure that we have sufficient room for the three objects we will create. I should also note that individual FormatCondition objects can be deleted by referencing the object with an index number such as:

Me![txtResult].FormatConditions(1).Delete

Next, we define the specific format conditions and add them to the FormatConditions collection by using the Add method.

Set objFrc = Me![txtResult].FormatConditions.Add(acFieldValue, _
    acLessThan, Me![txtTarget].Value)
Set objFrc = Me![txtResult].FormatConditions.Add(acFieldValue, _
    acEqual, Me![txtTarget].Value)
Set objFrc = Me![txtResult].FormatConditions.Add(acFieldValue, _ 
    acGreaterThan, Me![txtTarget].Value)

The expression and the control determine when to apply the format and what control to apply it to. For example, the first statement applies the format to the txtResult control if its field value is less than the field value of the txtTarget control, and so on for the other conditions.

Next, we use a series of Select Case statements to determine which format to apply when a condition is triggered. It does this by evaluating the output of the Choose an option area (the optgrpChoice control). For example, if the user selects option 1 and the value in the Result box is greater than the value in the Target box, the following section of code is executed:

With Me![txtResult].FormatConditions(2)
    .FontBold = True
    .FontItalic = True
    .FontUnderline = False
End With 

Here, the third format condition (using a zero-based index) applies a bold and italic format to the Result box. Different combinations of formatting are illustrated with the remaining Select Case statements.

Option 4 is a special case in that when the user clicks it, we remove the existing FormatCondition objects from the collection and dynamically create new ones.

Me![txtResult].FormatConditions.Delete
Set objFrc = Me![txtResult].FormatConditions.Add(acExpression, _
    , (Format(Now(), "ddd") = "Sat"))
Set objFrc = Me![txtResult].FormatConditions.Add(acExpression, _
    , (Format(Now(), "ddd") <> "Sat") And _
    (Format(Now(), "ddd") <> "Sun"))
Set objFrc = Me![txtResult].FormatConditions.Add(acExpression, _
    , (Format(Now(), "ddd") = "Sun"))

In these conditional definitions, we are using an expression to derive the day of the week from today's date in a three-character format and comparing it to the strings Sat and Sun to determine if the day falls on a weekday or weekend. The Access Format function is used to change today's date into the three-character string. We indirectly determine whether today's date is a weekday by testing to see that it is not equal to the strings Sat or Sun.

Once we have set up our new format conditions, we then define the new formatting that will be applied when a particular format condition is met.

Conclusion

In this article, we explored the conditional formatting of controls in Access. We looked at applying conditional format rules through the user interface and defining and creating format conditions programmatically. And although Access limits each control to just three conditions at any one time, we looked at a way of providing additional functionality by the use of Select Case statements and dynamically changing formatting conditions. I encourage you to experiment with these techniques to discover additional ways to make your conditional formatting more flexible.