CODEWITHSUNDEEP
vbams-accessuserform
J.M
J.M

Reputation: 85

Changing the Color of the Rows of a Form Based on Values Stored in a Table

Working with an Access based application. Need to change the color of some specific rows of a form (the color of "one" field per row needs to be changed) based on values stored in these fields. For instance, when we open the form, we need to see color green in the field of a certain row where we have the value 1. If the value of this field is 2, we need to see color orange and if it is 3 it is red.

Example of the output of the form

ID   Criteria
201  1        --> the cell containing 1 should be colored in green
203  3        --> the cell containing 3 should be colored in red
204  3
205           --> the cell that contains nothing should be kept uncolored
206  1        
207  2

Note: the values (1, 2 and 3) are already available when the form opens and they are also stored in a table.

Upvotes: 1

Views: 5353

Answers (1)

J.M
J.M

Reputation: 85

To solve the issue I used conditional formatting (I am using Microsoft Office Access 2007).

Below is the corresponding code.

Option Compare Database
Option Explicit

Const GreenG As Byte = 1

Const OrangeO As Byte = 2

Const RedR As Byte = 3


Private Sub StartCondFormatting()

Dim objFrc As FormatCondition

    Const orange As Long = 42495

    Const green As Long = 25600

    Const red As Long = 255

    Dim ctl As Control



    For Each ctl In Me.Controls

        If ctl.Tag = "Conditional" Then

            With ctl

                'Remove format conditions

                .FormatConditions.Delete



                'Create three format objects and add them to the FormatConditions

                Set objFrc = .FormatConditions.Add(acExpression, acEqual, "[fieldName] = " & GreenG)

                Set objFrc = .FormatConditions.Add(acExpression, acEqual, "[fieldName] = " & OrangeO)

                Set objFrc = .FormatConditions.Add(acExpression, acEqual, "[fieldName] = " & RedR)



                'Specify the formating conditions

                .FormatConditions(0).BackColor = green

                .FormatConditions(0).Enabled = True

                .FormatConditions(1).BackColor = orange

                .FormatConditions(1).Enabled = True

                .FormatConditions(2).BackColor = red

                .FormatConditions(2).Enabled = True

            End With

        End If

    Next ctl

    Set objFrc = Nothing


    End Sub



Private Sub Form_Load()
StartCondFormatting        
End Sub

For more information on conditional formatting.

Upvotes: 5

Related Questions