DEMO: How to display different background colours on a Continuous type form. (1 Viewer)

[Peter Hibbs]

This demo shows how to create a Continuous type form with a different colour for the background that is linked to a field value. Normally we would use Conditional Formatting to achieve this effect but this demo use Rich Text fields and some simple code to do the same. This has the advantage over CF that it is easier to program and causes no flicker at all, especially when scrolling with the scroll bar. However, as it uses the new Rich Text facility, it will only work with Access 2007 or later.

The attached zip file holds the demo database and a User Manual PDF which explains how it works.

Peter Hibbs.

 

[MajP]

I added to this demo so you can compare and contrast the other methods.

• Rich Text
• Conditional Formatting
• Detail OnPaint

To make the new demos work I added a field lngColor to the categories table. When you select a color it saves both the rich text as well as just the color value.

1. By far the easiest approach is the on paint. The entire code to do that is simply

Private Sub Detail_Paint()
  Me.txtColor.BackColor = Me!lngColor
End Sub

I have found that using this methods sometimes does not work well when you are adding or editing fields and the colors change. I did not find any issues with this form.

2. Doing this in Conditional Formatting has to be done dynamically because you change the rules of the formatting when you change or add colors. Here is how you add CFs to a control/s dynamically.

Private Sub Form_Load()
 RefreshFormatConditions
End Sub
Public Sub RefreshFormatConditions()
  RemoveFormatConditions
  AddFormatConditions
End Sub
Public Sub AddFormatConditions()
  Dim Con As FormatCondition
  Dim rs As DAO.Recordset
  Dim CategoryID As Long
  Dim ColorID As Long
 
  Set rs = CurrentDb.OpenRecordset("tblCategories")
  Do While Not rs.EOF
    ColorID = rs!lngColor
    CategoryID = rs!CategoryID
    Set Con = Me.txtColor.FormatConditions.Add(acExpression, , "[CategoryID]=" & rs!CategoryID)
    With Con
      .BackColor = ColorID
    End With
    
    rs.MoveNext
  Loop
  Me.Refresh
End Sub

Public Sub RemoveFormatConditions()
    Dim Con As FormatCondition
    For Each Con In Me.txtColor.FormatConditions
      Con.Delete
    Next Con
End Sub

In this example I did not notice any performance issues or flashes. You can get that depending on how the form is built.

 

[isladogs]

I created something very similar to the second screenshot above many years ago, originally to get around the (then) limit of 3 CF colours.
However the idea of using colour values stored in a table remains just as useful now that limit no longer applies

More info in my Colour Conditions article

Example app attached.

 

[cheekybuddha]

More info in my Colour Conditions article

Malformed url.

Should be:

Colour Conditions

50 colour formats are available using conditional formatting but only 3 colours are available using VBA.This shows another method of obtaining an unlimited number of colour formats in VBA without using conditional formatting.

www.isladogs.co.uk

 

[isladogs]

Thanks. I've fixed it in my post as well.

 

[arnelgp]

from the attached pdf:

"The simple formula for the calculation is ([UnitsInStock]+20)/20 which produces a floating-point number which is converted to an Integer in the Result Type property. In a ‘real’ application you would probably need a more complex query (or even some VBA code) to create the appropriate ColorID value."

so this is the finished product from you "Calculated Field error" thread?

 

[Peter Hibbs]

Hi arnelgp,

Yes, it is. Is there a problem with that (I am relatively new here)!

Peter.

 

[arnelgp]

no problem, what makes you say that?

 

[Peter Hibbs]

no problem, what makes you say that?

OK, it just sounded like there was a question regarding the Calculated Field issue but maybe I misread the comment. My mistake.

Peter.

 

[MajP]

@isladogs, this should be updated on your site. It is no longer up to date since at least 2013.

With the release of Access 2010 , a significant improvement was made with up to 50 colour formats available using the conditional formatting wizard
. . . BUT the 3 colour limit still applies using VBA

At a minimum after 2013 both the GUI and VBA could create 50 FCs. However, I think based on some reading it could have been in 2010 as well. At a time there was a bug and not a limit. You could work around the bug.
This is a 2014 post so the user is on 2013 or earlier version, and you are on the thread

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

so probably should read something more like.

...BUT the 3 colour limit for conditions created by VBA lasted until release 2013. After that the limits for the GUI and VBA were both 50. However in 2013 there was a bug that required a work around to create more than three conditions. That bug was fixed sometime after 2013.

 

[Peter Hibbs]

Hi MajP,

Thanks for the additional options on my demo, this was very interesting for me as I have not come across the Paint feature in this context before. As you say, it certainly looks simpler than the Rich Text version so I will test it out when I have a bit more time to see how it works in practice. I think the CF version is useful when the other 'non-colour' features are required but if only the colours need changing I think I would use the Paint or Rich Text versions, (depending on the project requirements maybe).

Anyway, I hope all this will help any other developers who have the same issues.

Peter Hibbs.

 

[MajP]

Thanks for the additional options on my demo, this was very interesting for me as I have not come across the Paint feature in this context before. As you say, it certainly looks simpler than the Rich Text version so I will test it out when I have a bit more time to see how it works in practice. I think the CF version is useful when the other 'non-colour' features are required but if only the colours need changing I think I would use the Paint or Rich Text versions, (depending on the project requirements maybe).

I think there are some pros and cons for the different cases and the user might have to test. I have done on paint and got some wacky results when it repaints especially if the format is on the actual controls not a text box behind the controls. I have seen it when you start scrolling or click into a cell that the colors start changing to incorrect colors.
With CF you can sometimes get really poor performance and things like flicker.
The rich text may be more stable, but that is a little more involved for the average user unless they import your example.

 

[isladogs]

@isladogs, this should be updated on your site. It is no longer up to date since at least 2013.


At a minimum after 2013 both the GUI and VBA could create 50 FCs. However, I think based on some reading it could have been in 2010 as well. At a time there was a bug and not a limit. You could work around the bug.
This is a 2014 post so the user is on 2013 or earlier version, and you are on the thread

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

so probably should read something more like.

Yes that needs updating. Thanks for pointing it out.
I made use of the workaround by @Royce on several occasions but it didn’t always work eg when there were spaces in the expression.
However I think it was much later that the code bug was completely fixed as suggested in the final post in that thread from @tvanstiphout from 2023

Anyway I will update that comment

 

[MajP]

Also I am not sure if there ever was ever a bug. That thread has a work around because the OP caused the problem.

@AHeyne points this out here

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

I agree. I’ve spent so long trying different things but to no avail. It will be interesting to see what DBguy has done. Sorry guys, I still can only respond using my phone. However, I have also been playing in the background and got the same results as @MajP. My demo from the other thread I...

www.access-programmers.co.uk

The proper syntax for adding format conditions is very exact. If you do not add them correctly you can get weird results. It seems hard to believe but if you leave a space after the equal sign it will cause your code to fail when adding more than three conditions unless you do that workaround.

.Item(i).Modify acExpression, acEqual, "[lngStatusId]= " & CStr(rst!lngStatusId)

The above is incorrect. The OP who presented the work around, actually caused the conditions requiring a workaround.