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

[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.

 

[MajP]

Curious what will happen when you run this database. It creates dynamic format conditions based on values in a table. It does at least 26.

Solved - Programatically change color on continous form conditional formatting

My ORIGINAL post, all the way back at #3 suggested a function might work. The OP wasn't buying and everyone else is suggesting that VBA will work.

www.access-programmers.co.uk

 

[MajP]

Do we really need all of this. Can you simply give us tblStatus and frmGrid? FYI there is no frmGrid.

Where is this "Schedule grid displaying the conditional format"?

 

[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...