VBA Conditional Formatting error because I have more than 3 formatting requirements.

[klsblues]

Hi all,

I am getting the error...

An error occurred: The format condition number you specified is greater then the number of format conditions.

I have a table that lists the status details of a job (see attached jpg) which allows the user to select (from a form) a fore color and back color for the format to be displayed on a txt field in a grid.
The first three formats work fine and any combination of back color and fore color displays as it should, but I get the error and no format displays on status 4 - 6. Here's the code, can anyone help with this please?

(For info, I call this on Form_Load)

Public Sub ApplyFormatConditions()

    On Error GoTo ErrorHandler ' Add error handling



    ' Define recordset to hold formatting rules

    Dim db As DAO.Database

    Dim rs As DAO.Recordset

    Dim BackColor As Long

    Dim ForeColor As Long

    Dim StatusID As Long

    Dim StatusDescription As String

    Dim JObStatus As Long

    Dim i As Integer

 



    ' Set up the database and open the recordset

    Set db = CurrentDb

    Set rs = db.OpenRecordset("tblStatus")



    ' Clear default formatting for JobStatusTxt

    Me.JobStatusTxt.FormatConditions.Delete



    ' Loop through the recordset to apply each formatting rule

    i = 1

   

    While Not rs.EOF

 

         ' Retrieve formatting details from the recordset

        StatusID = rs!StatusID

        BackColor = rs!BackColor

        ForeColor = rs!ForeColor

        StatusDescription = rs!StatusDescription

        JObStatus = Me.JObStatus

 

    Debug.Print "BackColor: " & BackColor & ", ForeColor: " & ForeColor & ", Status: " & StatusID & ", Desc: " & StatusDescription

 

    With Me.JobStatusTxt.FormatConditions.Add(acExpression, , "JObStatus = " & StatusID)

        .BackColor = BackColor

        .ForeColor = ForeColor

    End With

 

        ' Move to the next record

    rs.MoveNext

       i = i + 1



   Wend

 

    ' Close the recordset

    rs.Close

    Set rs = Nothing

    Set db = Nothing

 

 Exit Sub



ErrorHandler:

  MsgBox "An error occurred: " & Err.Description

  If Not rs Is Nothing Then rs.Close

 

 End Sub

Thanks in anticipation.

 

[theDBguy]

Had to ask, which version of Access are you using?

 

[DickyP]

First question is which version of Access? In Access 2003 / 2007 the built in limit was three. Certainly in Ver 2021 I've used 4 (I think they all worked but I'm not certain) but never bothered with the finding out the limit. You'll need to look it up for your version.

 

[klsblues]

I have the Microsoft Office 365 for Business (Version 2410 Build 18129.20116)

 

[DickyP]

Just been looking at previous threads on the subject where it is suggested that it is a bug. (And work rounds using VBA).

I then checked on my old laptop running 2003 where you cannot actually add more than three, and since in 2021 I can add lots of CFs I assume the bug explanation has credence.

 

[klsblues]

Just been looking at previous threads on the subject where it is suggested that it is a bug. (And work rounds using VBA).

I then checked on my old laptop running 2003 where you cannot actually add more than three, and since in 2021 I can add lots of CFs I assume the bug explanation has credence.

Hi Dickyp, So does this mean that the user can't set more than three from a form - instead, I will have to 'pre-set' the CF using the conditional formatting button and not allow the user to choose their own colour scheme?

 

[Gasman]

Please use code tags to keep indentation.

 

[klsblues]

Please use code tags to keep indentation.

Hi Gasman, How do I do that?

 

[Gasman]

Use the </> icon

is one way.

 

[theDBguy]

Hi Dickyp, So does this mean that the user can't set more than three from a form - instead, I will have to 'pre-set' the CF using the conditional formatting button and not allow the user to choose their own colour scheme?

I don't think it means that. I know you should be able to set up more than 3 conditions using VBA, but I may have to find the code demonstrating it.

 

[klsblues]

Use the </> icon

is one way.

Hi Gasman,

Thanks for this - I have edited my original post. I hope this helps someone to help me!

Regards.

 

[klsblues]

I don't think it means that. I know you should be able to set up more than 3 conditions using VBA, but I may have to find the code demonstrating it.

Thanks DBguy, anything you can do would be very much appreciated.

 

[theDBguy]

Thanks DBguy, anything you can do would be very much appreciated.

Found it!

Workaround for Format Conditions limited to 3

As many of you know, you can set lots (Some say 32, some say 50) of Format Conditions in Design View by right clicking a text box and Selecting Conditional Formatting. But you try it in code and you will get errors if you try to set more than 3. (Internally, there is a Microsoft BUG, not a...

www.access-programmers.co.uk

 

[klsblues]

Hi DBguy, I've had a look at this link, I don't understand how I can use it because the user selects the colours and they are stored in a table. Can you help with what I need to do with my code please?

 

[theDBguy]

Hi DBguy, I've had a look at this link, I don't understand how I can use it because the user selects the colours and they are stored in a table. Can you help with what I need to do with my code please?

I'm only using my phone to respond to the forum right now, so I don't have a way to write/post code efficiently. However, if you can post a demo db, I'll download it later and repost it with modified code, when I get back in front of a computer.

 

[klsblues]

I'm only using my phone to respond to the forum right now, so I don't have a way to write/post code efficiently. However, if you can post a demo db, I'll download it later and repost it with modified code, when I get back in front of a computer.

Hi DBguy.

I have attached the front end, back end dummy data and a DoNotDelete DB (which is usually encrypted) with licence info in it.
When you first go in, the login screen sets user rights and other info, so select the username Eileen and the password is Dover, this will give full rights to every screen.

The problem I have is with the Schedule grid displaying the conditional format, (which the user sets using the frmStatus form).

Thanks in advance for any help.

 

[klsblues]

The schedule grid is frmGrid, but it has onload criteria that restricts access. The form frmmainmenu uses the login to set the restrictions. Then select schedule grid.

 

[theDBguy]

Hi DBguy.

I have attached the front end, back end dummy data and a DoNotDelete DB (which is usually encrypted) with licence info in it.
When you first go in, the login screen sets user rights and other info, so select the username Eileen and the password is Dover, this will give full rights to every screen.

The problem I have is with the Schedule grid displaying the conditional format, (which the user sets using the frmStatus form).

Thanks in advance for any help.

Hi. Can you shoot me an email, so I can send you my code changes? Thanks.

Sent from phone...

 

[klsblues]

Curious what @theDBguy found out. My guess it is either a bug or a bad error message where some other error is actually happening. The code looks correct to me and you should definitely be able to do this on a modern version of Access. But stepping through it, it does what you say and loads the first 3 format conditions and then errors.

Definitely something strange. I modified this to apply formatting on the JobStatus Field using the value property instead of the acExpression
With Me.JObStatus.FormatConditions.Add(acFieldValue, acEqual, StatusID)
and it works fine and adds 11 format conditions. So there has to be some trick when using the AcExpression.

I agree. I’ve spent so long trying different things but to no avail. It will be interesting to see what DBguy has done.

 

[Gasman]

Curious what @theDBguy found out. My guess it is either a bug or a bad error message where some other error is actually happening. The code looks correct to me and you should definitely be able to do this on a modern version of Access. But stepping through it, it does what you say and loads the first 3 format conditions and then errors.

Definitely something strange. I modified this to apply formatting on the JobStatus Field using the value property instead of the acExpression
With Me.JObStatus.FormatConditions.Add(acFieldValue, acEqual, StatusID)
and it works fine and adds 11 format conditions. So there has to be some trick when using the AcExpression.

My interpretation from the link, was that for expression, you need to create placeholders and then modify those?