Conditional formatting (sort of?) (1 Viewer)

Alc

Registered User.
Local time
Today, 05:07
Joined
Mar 23, 2007
Messages
2,407
I have a form that opens in 'Continuous Forms' view. I know how to have different text fields appear in different colours, based on the value of one of the text fields. Where I'm getting stuck is on trying to set the colour of all text fields for a given record to the value STORED in one of the fields.

I have a field called ItemColourCode in a table. The values in this field are calculated as part of a lengthy overnight process that takes into account days to target, number of people affected, etc. This process updates many thousands of records and takes far too long to run 'on the fly', so I schedule it to run at 2:00 AM. The end result is that each record has an ItemColourCode value of, say, #F00000, stored as a text string.

What I want to do is, as the form opens, set the background colour for each text field to be the value in this field, but I'm not having much luck. I thought getting the formula right to calculate the colour for each record was going to the hardest part, but displaying it has turned out to be more frustrating.

I've tried going to the properties tab and setting the Back Color property for a field to =[ItemColourCode] but that doesn't work. Do I need to loop through all records as the form opens and colour each field? I suspect this might be time consuming.

I'm really hoping that there's a simple way of doing this, of which I'm just unaware.

As always, any suggestions much appreciated
 

Alc

Registered User.
Local time
Today, 05:07
Joined
Mar 23, 2007
Messages
2,407
*sigh* As is often the case, after spending Friday trying to resolve this, I hit the <Sumbit> button on this question and immediately thought of another way to approach the problem. I'm posting it in case it helps anyone else.

I run the same process to calculate the colour values, the use Conditional Formatting to create a slew of rules to colour one text box based on the value in the ItemColourCode field. I then copied that formatting to all of the text fields on that record and all looks good.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:07
Joined
May 7, 2009
Messages
19,170
use the Detail Section Paint Event.
now for each field's On Got Focus Event,
put this:

=ReColor()

Code:
Private Sub Detail_Paint()
    If Not IsNull(Me.ID) Then
        Me.ID.BackColor = Me.ItemColorCode
        Me.names.BackColor = Me.ItemColorCode
        Me.qty.BackColor = Me.ItemColorCode
    Else
        Me.ID.BackColor = vbWhite
        Me.names.BackColor = vbWhite
        Me.qty.BackColor = vbWhite
    End If
End Sub

Public Function ReColor()
    Call Detail_Paint
End Function
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:07
Joined
May 7, 2009
Messages
19,170
you are only limited to 3
FormatConditions. that means
only 3 colors.
my code, unlimited colors as you
like.
 

Alc

Registered User.
Local time
Today, 05:07
Joined
Mar 23, 2007
Messages
2,407
Much appreciated. I shall give that a try asap.

In the interim, I'm using Access 2010, so I'm not limited to just the three conditional formats (currently using nine).
 

isladogs

MVP / VIP
Local time
Today, 09:07
Joined
Jan 14, 2017
Messages
18,186
I'm a bit late .... but see if the attached can be adapted for your needs....



Alter any Colour Value and the Colour column is updated to match this
 

Attachments

  • Capture.PNG
    Capture.PNG
    18.8 KB · Views: 168
  • ColourCategories.accdb
    796 KB · Views: 49

Users who are viewing this thread

Top Bottom