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.

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