Listing Conditional Formats

<< Click to Display Table of Contents >>

Navigation:  Forms and Menus >

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

 

 

Your download file is called 501DOBSON.ZIP in the file SA2005-01down.zip

This can be purchased with all the other downloads on this page