Listing Conditional Formats

<< Click to Display Table of Contents >>

Navigation:  Forms  >

Listing Conditional Formats

In 2005, Rick Dobson wrote an article on that included details on Conditional Formats on Access Forms. But whilst the article was great, Rick didn't show all the code that he had prepared in his sample database. And its what he didn't show that is of great interest as this information is very hard to find anywhere.

This was compiled by Garry Robinson in 2013

 

Why Bother

Conditional Formats are very powerful but they are hard to read and just as hard to find.  If you decide to apply complex rules, you need to document the equations and crazy color schemes that you have applied to the fields in your forms.

 

List Conditional Formats

 

In figure 1 is a button that lists the conditional formats into the Immediate Window. The code follows but when you read the code, the properties Expression1, Expression2 and Type will be of interest.

 

200501_rdx1

 

 

 

Code to List Conditional Formats

 

 

Private Sub cmdListconditionalformats_Click()

 

Debug.Print vbCr & "ShippedDate"

With ShippedDate

   Debug.Print "Count = " & .FormatConditions.Count

   Debug.Print vbTab & "Type = " & DecodeType(.FormatConditions(0).Type)

   Debug.Print vbTab & "Operator = " & DecodeOp(.FormatConditions(0).Operator)

   Debug.Print vbTab & "Expression1 = " & .FormatConditions(0).Expression1

   Debug.Print vbTab & "Expression2 = " & .FormatConditions(0).Expression2

End With

 

Debug.Print vbCr & "RequiredDate"

With RequiredDate

   Debug.Print "Count = " & .FormatConditions.Count

   Debug.Print vbTab & "Type = " & DecodeType(.FormatConditions(0).Type)

   Debug.Print vbTab & "Operator = " & DecodeOp(.FormatConditions(0).Operator)

   Debug.Print vbTab & "Expression1 = " & .FormatConditions(0).Expression1

   Debug.Print vbTab & "Expression2 = " & .FormatConditions(0).Expression2

End With

 

Debug.Print vbCr & "OrderDate"

Debug.Print "Count = " & OrderDate.FormatConditions.Count

Debug.Print vbTab & "Type = " & DecodeType(OrderDate.FormatConditions(0).Type)

Debug.Print vbTab & "Operator = " & DecodeOp(OrderDate.FormatConditions(0).Operator)

Debug.Print vbTab & "Expression1 = " & OrderDate.FormatConditions(0).Expression1

Debug.Print vbTab & "Expression2 = " & OrderDate.FormatConditions(0).Expression2

Debug.Print vbTab & "Type = " & DecodeType(OrderDate.FormatConditions(1).Type)

Debug.Print vbTab & "Operator = " & DecodeOp(OrderDate.FormatConditions(1).Operator)

Debug.Print vbTab & "Expression1 = " & OrderDate.FormatConditions(1).Expression1

Debug.Print vbTab & "Expression2 = " & OrderDate.FormatConditions(1).Expression2

Debug.Print vbTab & "Type = " & DecodeType(OrderDate.FormatConditions(2).Type)

Debug.Print vbTab & "Operator = " & DecodeOp(OrderDate.FormatConditions(2).Operator)

Debug.Print vbTab & "Expression1 = " & OrderDate.FormatConditions(2).Expression1

Debug.Print vbTab & "Expression2 = " & OrderDate.FormatConditions(2).Expression2

   

Debug.Print vbCr & "OrderID"

Debug.Print "Count = " & OrderID.FormatConditions.Count

Debug.Print vbTab & "Type = " & DecodeType(OrderID.FormatConditions(0).Type)

Debug.Print vbTab & "Operator = " & DecodeOp(OrderID.FormatConditions(0).Operator)

Debug.Print vbTab & "Expression1 = " & OrderID.FormatConditions(0).Expression1

Debug.Print vbTab & "Expression2 = " & OrderID.FormatConditions(0).Expression2

   

End Sub

 

Function DecodeType(TypeProp As Integer) As String

 

' You heed this are there are 3 different ways to setup a CondtionalFormat    

   Select Case TypeProp

       Case 0

           DecodeType = "acFieldValue"

       Case 1

           DecodeType = "acExpression"

       Case 2

           DecodeType = "acFieldHasFocus"

   End Select

   

End Function

 

Function DecodeOp(OpProp As Integer) As String

 

' You need this becuase equations can comprise of = > <> between

 

   Select Case OpProp

       Case 0

           DecodeOp = "acBetween"

       Case 1

           DecodeOp = "acNotBetween"

       Case 2

           DecodeOp = "acEqual"

       Case 3

           DecodeOp = "acNotEqual"

       Case 4

           DecodeOp = "acGreaterThan"

       Case 5

           DecodeOp = "acLessThan"

       Case 6

           DecodeOp = "acGreaterThanOrEqual"

       Case 7

           DecodeOp = "acLessThanOrEqual"

   End Select

   

End Function

 

 

Are the Conditional Formats in picture form

 

200501_rdx2

 

200501_rdx3

 

 

200501_rdx4

 

 

download Your download file is called  Dobson_Conditional_Formats.accdb  and you can download it on this page Forms >>