troubleshooting Question

More than three Conditional Formats VBA Access 2010

Avatar of Mudasir Noorani
Mudasir NooraniFlag for United Kingdom of Great Britain and Northern Ireland asked on
Microsoft Access
10 Comments5 Solutions2477 ViewsLast Modified:
Hello Experts,

I have been made to understand that in Access 2010, one can set up to 50 Conditional Formats (I read this on some Microsoft Article on the internet).

However, how can one do this in code? Im asking because, on the Form_Load event, I have written some VBA code that calls a function which loops and adds more than three Conditional Formats in Access 2010, however, on the fourth attempt, I get an error telling me:

7966      The format condition number you specified is greater than the number of format conditions

The code is as below:

Sub ApplyConditionalFormatting()
'This Sub Procedure applies Conditional Formatting to the necessary controls
'based on the Fore Colour and Back Colour values in txtForeColor and txtBackColor

Dim objFrc As FormatCondition
Dim ctl As Control
Dim rstConditions As DAO.Recordset
Dim sqlString As String
Dim fcCounter As Integer

sqlString = "SELECT * FROM tblStaff"
Set rstConditions = CurrentDb.OpenRecordset(sqlString, dbReadOnly)
rstConditions.MoveFirst

For Each ctl In Me.Controls
    If ctl.Tag = "Conditional" Then
        With ctl
            'Remove Format Conditions
            .FormatConditions.Delete

            'Initialise recordset Pointer to move to First Record and value of fcCounter to 0 before entering the loop
            rstConditions.MoveFirst
            fcCounter = 0
            
            'Add Format Condtions to the Control Object
            While Not rstConditions.EOF
                Set objFrc = .FormatConditions.Add(acExpression, acEqual, "[txtStaffName] = '" & rstConditions!StaffName & "'")
                
                .FormatConditions(fcCounter).ForeColor = rstConditions!ForeColor
                .FormatConditions(fcCounter).BackColor = rstConditions!BackColor
                fcCounter = fcCounter + 1
                rstConditions.MoveNext
            Wend
        
        End With
    End If
Next ctl

Set objFrc = Nothing

rstConditions.Close
Set rstConditions = Nothing

End Sub

I have tried using more than three Conditional Formats on the Form's Design View, and it works, why does it not work in Code?

Thanks and Well Appreciated in Advance.

ref-IT
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 5 Answers and 10 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 5 Answers and 10 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros