Microsoft Knowledge Base Article
This article contents is Microsoft Copyrighted material.
©2005-©2007 Microsoft Corporation. All rights reserved.
Terms
of Use |
Trademarks
Article ID: 304104 - Last Review: April 10, 2007 - Revision: 7.1
How to programmatically apply conditional formatting settings to all form controls
This article was previously published under Q304104
Advanced: Requires expert coding, interoperability, and multiuser
skills.
This article applies to a Microsoft Access database (.mdb) and to a
Microsoft Access project (.adp).
SUMMARY
In Microsoft Access 2000, conditional formatting was added
to forms and reports to allow users to format a control based on a particular
condition. However, in the Access user interface, you cannot automatically
propagate the conditional formatting of one control to all other controls on
the form. But you can do this by using the FormatConditions collection of the control on which the initial conditional format
has been set. This article shows you how to use code to propagate the
conditional formatting of one control to all other controls on the form.
MORE INFORMATION
Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements. For an example of how to propagate the conditional
formatting of one control to all other controls on the form, follow these
steps.
CAUTION: If you follow the steps in this example, you modify the sample
database Northwind.mdb. You may want to back up the Northwind.mdb file and
follow these steps on a copy of the database.
- If you use Access 2002 or Access 2003, follow these steps:
- Start Access.
- On the Help menu, point to Sample Databases, and then click Northwind Sample Database.
- In the Database window, click the Modules under Objects.
- Click New to create a new module.
- Add the following line to the Declarations section of the
module if it is not already there:
- Type or paste the following code:
Function AddFormats(ctlSource As Control, frm As Form) As Integer
Dim ctl As Control
Dim fcdSource As FormatCondition
Dim fcdDestination As FormatCondition
Dim varOperator As Variant
Dim varType As Variant
Dim varExpression1 As Variant
Dim varExpression2 As Variant
Dim intConditionCount As Integer
Dim intCount As Integer
intConditionCount = ctlSource.FormatConditions.Count
For Each ctl In frm.Controls
If ctl.Name = ctlSource.Name Then
' This is the source. Don't apply formatting.
ElseIf ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox Then
intCount = 0
' Bulk remove all current FormatConditions
ctl.FormatConditions.Delete
Do Until intCount = intConditionCount
Set fcdSource = ctlSource.FormatConditions.Item(intCount)
varOperator = fcdSource.Operator
varType = fcdSource.Type
varExpression1 = fcdSource.Expression1
varExpression2 = fcdSource.Expression2
' Add the FormatCondition
ctl.FormatConditions.Add varType, varOperator, varExpression1, varExpression2
' Reference the FormatCondition to apply formatting.
' Note: The FormatCondition cannot be referenced
' in this manner until it exists.
Set fcdDestination = ctl.FormatConditions.Item(intCount)
With fcdDestination
.BackColor = fcdSource.BackColor
.FontBold = fcdSource.FontBold
.FontItalic = fcdSource.FontItalic
.FontUnderline = fcdSource.FontUnderline
.ForeColor = fcdSource.ForeColor
End With
' Move to the next FormatCondition
intCount = intCount + 1
Loop
End If
Next ctl
' Cleanup
AddFormats = intConditionCount
MsgBox "There were " & AddFormats & " Conditional Format(s) applied to all text and combo boxes except the source."
Set ctl = Nothing
Set fcdSource = Nothing
Set fcdDestination = Nothing
Set varOperator = Nothing
Set varType = Nothing
Set varExpression1 = Nothing
Set varExpression2 = Nothing
intConditionCount = 0
intCount = 0
End Function
- Save the module as
Module1.
- Open the Orders form in Design view.
- Click the Freight text box.
- On the Format menu, click Conditional Formatting, and then apply the following conditional formats to the Freight
text box:
- Condition 1 = Field Has Focus, Fill/Back Color =
Yellow
- Condition 2 = Field Value Is Equal To 1, Font/Fore
Color = Green
- Condition 3 = Expression Is 1+1=2, Underline =
True
- Add a command button named cmdApplyCondFormat to the Orders form.
- Set the OnClick property of the command button to the following event procedure:
Private Sub cmdApplyCondFormat_Click()
AddFormats Me.Freight, Me
End Sub - Save the Orders form.
- Open the Orders form in Form View. Note the conditional
formatting of the Freight text box.
- Click the command button to apply the Freight text box
conditional formatting to all other text and combo boxes on the main
form.
- If you use Access 2007, follow these steps:
- Start Access, and then open sample datebase.
- On the Create tab, click Macro in the Other group, and then click Module.
- Add the following line to the Declarations section of the
module if it is not already there:
- Type or paste the following code:
Function AddFormats(ctlSource As Control, frm As Form) As Integer
Dim ctl As Control
Dim fcdSource As FormatCondition
Dim fcdDestination As FormatCondition
Dim varOperator As Variant
Dim varType As Variant
Dim varExpression1 As Variant
Dim varExpression2 As Variant
Dim intConditionCount As Integer
Dim intCount As Integer
intConditionCount = ctlSource.FormatConditions.Count
For Each ctl In frm.Controls
If ctl.Name = ctlSource.Name Then
' This is the source. Don't apply formatting.
ElseIf ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox Then
intCount = 0
' Bulk remove all current FormatConditions
ctl.FormatConditions.Delete
Do Until intCount = intConditionCount
Set fcdSource = ctlSource.FormatConditions.Item(intCount)
varOperator = fcdSource.Operator
varType = fcdSource.Type
varExpression1 = fcdSource.Expression1
varExpression2 = fcdSource.Expression2
' Add the FormatCondition
ctl.FormatConditions.Add varType, varOperator, varExpression1, varExpression2
' Reference the FormatCondition to apply formatting.
' Note: The FormatCondition cannot be referenced
' in this manner until it exists.
Set fcdDestination = ctl.FormatConditions.Item(intCount)
With fcdDestination
.BackColor = fcdSource.BackColor
.FontBold = fcdSource.FontBold
.FontItalic = fcdSource.FontItalic
.FontUnderline = fcdSource.FontUnderline
.ForeColor = fcdSource.ForeColor
End With
' Move to the next FormatCondition
intCount = intCount + 1
Loop
End If
Next ctl
' Cleanup
AddFormats = intConditionCount
MsgBox "There were " & AddFormats & " Conditional Format(s) applied to all text and combo boxes except the source."
Set ctl = Nothing
Set fcdSource = Nothing
Set fcdDestination = Nothing
Set varOperator = Nothing
Set varType = Nothing
Set varExpression1 = Nothing
Set varExpression2 = Nothing
intConditionCount = 0
intCount = 0
End Function
- Save the module as
Module1.
- Open the Orders Details form in Design view.
- Click the Customer ID dropdown list, click Conditional, and then apply the following conditional formats to the Customer ID dropdown list:
- Condition 1 = Field Has Focus, Fill/Back Color =
Yellow
- Condition 2 = Field Value Is Equal To 1, Font/Fore
Color = Green
- Condition 3 = Expression Is 1+1=2, Underline =
True
- Add a command button named cmdApplyCondFormat to the Order Details form.
- Set the OnClick property of the command button to the following event procedure:
Private Sub cmdApplyCondFormat_Click()
AddFormats Me.Customer_ID, Me
End Sub - Save the Order Details form.
- Open the Order Details form in Form View. Note the conditional
formatting of the Customer ID text box.
- Click the command button to apply the Payment Date text box
conditional formatting to all other text and combo boxes on the main
form.
APPLIES TO
- Microsoft Office Access 2007
- Microsoft Office Access 2003
- Microsoft Access 2002 Standard Edition
| kbprogramming kbvba kbinfo kbhowto KB304104 |
Community Feedback System
Very often, it takes hours to solve a problem. Very often, you've looked high
and low, and have tried a lot of solutions. When you finally found it, chances
are, it was because someone else helped you. Here's your chance to give back.
Use our community feedback tool to let others know what worked for you and what
didn't.
Please also understand that the community feedback system is not warranted to be
correct, it's simply a system that we've built to let people try and help each
other. If something in a feedback response doesn't make sense to you, or you're
not comfortable making changes that the feedback talks about (like registry
edits), please consult a professional.
Thank you for using kbAlertz.com Feedback System.
-- Scott Cate
Be the first to leave feedback, to help others about this knowledge base
article.
(Optional) Name
(Optional)
Public URL Or Email
Comments
No
HTML -- Text Only Please