Data Management

Working with conditional formats in Access

Conditionally displaying formats used to require programming. In Access 2000, a new menu command makes assigning conditional formats to text boxes and combo boxes as easy as writing an expression.


Microsoft Access is widely acclaimed for its ability to prepare great-looking forms and reports quickly. However, until Access 2000, conditionally displaying formats required programming. A new menu command makes assigning conditional formats to text boxes and combo boxes as easy as writing an expression. Database developers and administrators will find that this new feature substantially enhances the flexibility and speed of providing dynamic custom forms and reports.

In this article, we’ll show you how to tap the power of conditional formats with Access 2000. We’ll present an overview of what you can accomplish with the new conditional formatting feature. We’ll examine the programming interface and show you how to achieve the same conditional formatting functionality without manipulating a menu manually. Finally, we’ll provide a code sample that shows you how to apply the programming interface in order to set some conditional formats temporarily and to enumerate the properties of those formats.

What you can do with conditional formatting
A conditional format is a format specification for a text box or combo box control that Access enforces when an associated criterion condition is true. The significant advantage of conditional formats is that now you can create them without code. This new feature works for both forms and reports. You can make formats for text boxes and combo boxes conditional upon anything that you can represent with an expression. This new feature expands the community of users who can unleash the ability of Access to create dynamic forms and reports. Since this version is the initial release of the feature, the amount of functionality is much less than the full extent of the formatting options. Nevertheless, several common formatting options are available.

You can tap conditional formatting through either the new Conditional Formatting command or a programmatic interface. While the advantages of the graphical interface are obvious, the attractions of the programmatic interface may be less apparent. First, the programmatic interface offers a single point for manipulating six formatting capabilities. Second, you can readily assign temporary conditional formats that are associated with the opening of a form or report. It permits the same form or report to open with different conditional format settings.

You can invoke any of five separate formatting options, and you can designate a control as being enabled or disabled. Three of the five formatting options are binary switches that turn bold, italics, and underlining attributes on or off. You control these settings with a toggle button. The remaining two options permit you graphically to set the colors that a control uses to display its contents. One option specifies the background color, and the other designates the font color. Select a color from a drop-down array that presents 40 colors and shades of gray. A toggle button in the Conditional Formatting dialog box permits you to format a control as enabled or disabled.

You can specify up to three conditional formats for a control. Including the default format, Access allows four formats per control. Start by selecting one or more controls in Form or Report Design view. (You can opt for the normal Form and Report views, as well.) Your conditional formats apply exclusively to what you select before opening the Conditional Formatting dialog box and issuing the Format | Conditional Formatting command. Using the new Allow Design Changes form property, database developers and administrators can restrict access from Form view to the Conditional Formatting command, which will help secure their settings. When a control on a form or report satisfies the first criterion, the condition applies its format. If there is more than one condition, Access evaluates them in the order that they appear in the Conditional Formatting dialog box. When there is no match for any criterion, the default format applies.

You can specify that a condition has any of three types: Field Value Is, Expression Is, and Field Has Focus. The Field Value Is type permits you to designate one of eight operators for a field's value. The operators allow you to specify ranges of field values that invoke the formatting for a condition. The operators that you can select are between, not between, equal to, not equal to, greater than, less than, greater than or equal to, and less than or equal to. With the Expression Is type of condition, you can write an expression that evaluates as True or False. When the expression is True, the formatting for that line in the Conditional Formatting dialog box appears in the control. The last rule, Field Has Focus, does not require an argument; it applies a format to a control whenever the control gains focus.

The conditional formatting programming interface
Two Access classes provide the programmatic interface to conditional formatting. First, FormatConditions is the collection of all conditional formats. This object is hierarchically dependent on either a combo box or a text box. Each instance of these form control types can have its own collection of format conditions. A FormatCondition object represents the individual format conditions that are attached to a control.

You can use the FormatConditions property of a combo box or a text box to return the FormatConditions collection for that control. Invoke the FormatConditions Add method to create a new format condition for a control. This function requires a Type argument, but it has three optional arguments. Your programs can specify the Type argument with any of three intrinsic constants: acFieldValue, acExpression, or acFieldHasFocus. If you choose acFieldValue as the Type argument value, you can use one of eight intrinsic constants to designate an operator. If you choose either acExpression or acFieldValue as the Type argument, you can use a Variant to specify the expression or field value as a string or numeric constant. This is the Expression1 argument. When the operator is either acBetween or acNotBetween, you also must assign a value to an Expression2 argument. When you assign acFieldHasFocus to the Type argument, no additional arguments are necessary. Use the FormatConditions Delete method to remove all format conditions associated with a control.

After you create a format condition, you can assign it format settings with the FormatCondition object. For example, you can use the FontBold, FontItalic, or FontUnderline properties to set the format for a condition to bold, italic, or underline. The ForeColor and BackColor properties affect the font and background colors of a control, respectively. Use the RGB function to set values for these properties. RGB(255,0,0) and RGB(0,128,0) generate colors of red and dark green. When working with a FormatCondition object, you assign values to its properties. They, in turn, affect the appearance of the individual control to which a FormatCondition belongs.

A couple of FormatCondition methods round out your capabilities in managing format conditions. Use the Modify method to alter the arguments for an existing condition. This method has the same selection of arguments as the FormatConditions Add method. Your Modify argument settings override any existing ones for a FormatCondition object. The Delete method for the FormatCondition object allows you to remove format conditions selectively from a control object. This method contrasts with the Delete method for the FormatConditions collection, which removes all the format conditions associated with a control.

A programmatic conditional formatting example
The following sample, designed to demonstrate programmatic control of conditional formats, shows you how to perform two basic kinds of tasks. First, it creates new FormatCondition objects and sets their properties. Second, it enumerates the members of the FormatConditions collection and identifies selected feature settings.

The sample in Listing A has a main sub procedure that calls three function procedures. The sub procedure creates a pair of conditional formats for an instance of a form. These conditional formats persist only as long as the form instance survives. They do not modify the original form class, as does the Format menu’s Conditional Formatting command. In addition, you can apply different conditional formats concurrently to another form instance that’s based on the same form class. The three function procedures decode the condition type, operator, and color assignment. These decoding operations are particularly convenient when you want to create an easily readable, permanent record of the conditional formats for one or more forms or report controls.
Sub AssignConditionalFormats()
Dim frc1 As FormatCondition
Dim frm1 As Form
Dim strFrmName As String
Dim ctl1 As Control
Dim cbo1 As ComboBox

'Name control on form to search for
'FormatConditions
strFrmName = "frmqryNameExtPhoneReportsTo3c"

'Set reference to open form instance
If Not _
(CurrentProject.AllForms(strFrmName). _
IsLoaded) Then
DoCmd.OpenForm strFrmName
End If
Set frm1 = Forms(strFrmName)

'Find target control box control
For Each ctl1 In frm1.Controls
If TypeOf ctl1 Is ComboBox Then
'After finding control use control box pointer
'to represent it so you can use FormatConditions
'as a hierarchical object
Set cbo1 = ctl1
'Assign two conditional formats to it
Set frc1 = cbo1.FormatConditions. _
Add(acFieldValue, acEqual, 2)
With frc1
.FontBold = True
.ForeColor = RGB(255, 0, 0)
End With
Set frc1 = cbo1.FormatConditions. _
Add(acFieldValue, acEqual, 5)
With frc1
.FontBold = True
.ForeColor = RGB(0, 128, 0)
End With
'Print FormatCondition type, operator, and Forecolor
For Each frc1 In _
frm1.Controls(cbo1.Name).FormatConditions
Debug.Print decodeType(frc1.Type), _
frm1.Controls(cbo1.Name).Name & _
" " & _
decodeOperator(frc1.Operator) & _
" " & _
frc1.Expression1, _
decodeColor(frc1.ForeColor)
Next frc1
End If
Next ctl1

End Sub
Function decodeType(myType As Integer) As String
'Assign acFormatConditionType instrinsic
'constants to strings
Select Case myType
Case 0
decodeType = "Field value"
Case 1
decodeType = "Expression"
Case 2
decodeType = "Has focus"
End Select

End Function
Function decodeOperator(myOperator As Integer) _
As String

'Assign acFormatConditionOperator instrinsic
'constants to strings
Select Case myOperator
Case 0
decodeOperator = "Between"
Case 1
decodeOperator = "Not Between"
Case 2
decodeOperator = "Equal"
Case 3
decodeOperator = "NotEqual"
Case 4
decodeOperator = "GreaterThan"
Case 5
decodeOperator = "LessThan"
Case 6
decodeOperator = "GreaterThanOrEqual"
Case 7
decodeOperator = "LessThanOrEqual"
End Select

End Function
Function decodeColor(intColor) As String
'Loop through RGB function values to find
'match for graphically assigned color value
For ib = 0 To 255
For ig = 0 To 255
For ir = 0 To 255
If intColor = RGB(ir, ig, ib) Then
decodeColor = _
"RGB(" & ir & ", " & ig & _
", " & ib & ")"
Exit Function
End If
Next ir
Next ig
Next ib
End Function


The sub procedure starts by assigning the name of the form that will receive the conditional formats to a string variable. Next, it uses the AllForms collection to determine if the target form is open. If it isn’t, the procedure opens it. In any event, the procedure assigns the form class to the frm1 pointer. This pointer represents an instance of the class.

After creating an instance of the target form's class, the procedure searches for the target control on the form instance. This target control will receive conditional formatting. In this case, it’s a combo box that denotes the manager for an employee. The form uses a lookup field to represent the EmployeeID in the combo box as a manager's name. Once the form finds the combo box control, it assigns the control to a ComboBox object. This step is necessary to expose the FormatConditions collection.

The procedure moves on to creating two FormatCondition objects with the FormatConditions Add method. After specifying the criteria for a condition, the procedure sets selected format properties for the condition. The first condition has a bold, red font color. The second format condition has a bold, dark-green font color. The procedure reuses a single FormatCondition pointer, frc1, to reference each format condition sequentially.

In most circumstances, your procedures can stop here, but our sample continues—for tutorial purposes—to enumerate the format conditions for the combo box. A For Each…Next loop iterates through the members of the FormatConditions collection for the combo box. On each pass through the loop, the procedure prints the type of condition, the control's name, the operator, the Expression1 setting, and the font color for the conditional format. The decodeType and decodeOperator procedures work on the same design to translate intrinsic constants from their numeric values to strings that designate the constants. A simple Select Case statement does the translations. The decodeColor procedure represents the long number, which shows a font color as a string with its RGB function settings. Three nested For loops perform the translation.

Conclusion
The introduction of conditional formats with Access 2000 improves the suitability of Access for one of its most typical applications. This simplified and flexible way of applying conditional formats improves Access forms and reports by making it easier to highlight important points.

Rick Dobson, Ph.D., and his wife operate a development and training consultancy. He is the author of the best-selling book Programming Microsoft Access 2000 for Microsoft Press. Rick is a regular contributor to TechRepublic and numerous computer periodicals. In addition, he has presented training sessions and seminars on Access and Web development topics in Australia, Canada, the United Kingdom, and throughout the United States. Rick is a Microsoft Certified Professional and a Microsoft Certified Trainer. You can reach Rick at either of the two Web sites that his practice maintains (www.programmingmsaccess.com and www.cabinc.net ).

The authors and editors have taken care in preparation of the content contained herein, but make no expressed or implied warranty of any kind and assume no responsibility for errors or omissions. No liability is assumed for any damages. Always have a verified backup before making any changes.

Editor's Picks

Free Newsletters, In your Inbox