Change BackColour of Form (1 Viewer)

Cliff67

Registered User.
Local time
Today, 08:29
Joined
Oct 16, 2018
Messages
175
Hi all
I'm not sure if this has been posted before but I couldn't find it. I did try to post it yesterday but it seems to have disappeared.

I've done this before but can't get it working and can't remember how I did it before.

So I've got a continuous form that I want to colour code based on the duration that the Tech Support call have been open (just the open ones) the field called "Duration" is calculated based on Today - date opened = days duration

I have a little function called TrafficColourBars as below
Code:
Function TrafficColourBars()
'creates different background colours based on the duration
'set the standard font colour as black
Me.Ticket_Number.ForeColor = vbBlack
Me.DateOpened.ForeColor = vbBlack
Me.Company.ForeColor = vbBlack
Me.Country.ForeColor = vbBlack
Me.Product.ForeColor = vbBlack
Me.Duration.ForeColor = vbBlack
Me.Category.ForeColor = vbBlack

If Me.Duration < 6 Then
    Me.Detail.BackColor = 35653 'kind of green '69 139 00
    
ElseIf Me.Duration > 5 And Me.Duration < 11 Then
    Me.Detail.BackColor = 1030655 'type of yellow 255 185 15
    
ElseIf Me.Duration >= 11 Then
    'If the ticket is over 10 days set the font white and the back colour as red
    Me.Detail.BackColor = 858083 ' red colour
    Me.Ticket_Number.ForeColor = vbWhite
    Me.DateOpened.ForeColor = vbWhite
    Me.Company.ForeColor = vbWhite
    Me.Country.ForeColor = vbWhite
    Me.Product.ForeColor = vbWhite
    Me.Duration.ForeColor = vbWhite
    Me.Category.ForeColor = vbWhite
End If

End Function

The code is called from the Form_Load event but will colour all records displayed the first colour it can match with the duration.

If I call it from Form_Current the whole form changes on the current record so that is a no go on that one.

Anyone done something similar or what am I missing. Do I need to cycle through each record and set the background based on the Duration criteria?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 08:29
Joined
Oct 29, 2018
Messages
21,358
Hi. It wouldn't matter. If you do a loop, then everything will have the color of the last record. The only way to do it is by using conditional formatting on a textbox in the background.
 

isladogs

MVP / VIP
Local time
Today, 15:29
Joined
Jan 14, 2017
Messages
18,186
Continuous forms just have one set of controls, repeated for each record. So any standard formatting is for all records (or none). As DBG stated, you need to use conditional formatting via the wizard or using code
 

Cliff67

Registered User.
Local time
Today, 08:29
Joined
Oct 16, 2018
Messages
175
Thanks DBG

I was sure I had done it a different way with an earlier release of Access but maybe I was just thinking of something else
 

Cliff67

Registered User.
Local time
Today, 08:29
Joined
Oct 16, 2018
Messages
175
Isladogs I've never really used the conditional formatting in access before but I just gave it a try. Can you tell it to set a control e.g. text box a colour based on another text box, because I can't get that bit to work
 

isladogs

MVP / VIP
Local time
Today, 15:29
Joined
Jan 14, 2017
Messages
18,186
Yes you can but the expression is based on the field name not the control name.
Use Expression Is ...[FieldName] = some condition
You can apply the same formatting to multiple controls at the same time

Here's a very simple example:
 

Attachments

  • Capture.PNG
    Capture.PNG
    18.5 KB · Views: 194

Cliff67

Registered User.
Local time
Today, 08:29
Joined
Oct 16, 2018
Messages
175
Hi IslaDogs

Thanks for that, I selected multiple text boxes and entered the correct expression like you showed above. it works a treat I suppose you get too bogged down with doing things in VBA.

Now how do you make a thread SOLVED....
 

Users who are viewing this thread

Top Bottom