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

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