Change Form background based on field value

Posted on 2014-01-16
Last Modified: 2014-01-16
I would like the form background to be based on  a value in a field. The field name is "office". I am guessing it would be an "After Update" event?

If Office is the following values, I would like the background color to change to the given color:

Value in Office  Desired Color
OC                         Light Green
RS                          Light Blue
SD                         Blue
LA                          Blue-ish Purple
SV                          Yellow
680                        Light Red
Question by:cansevin
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
LVL 85
ID: 39786504
You'd need to use the Form's Current event:

Sub Form_Current()
  Select Case Me!Office
    Case "OC"
      Me.Detail.Backcolor = vbGreen
    Case "RS"
      Me.Detail.Backcolor = vbBlue
     etc etc
  End Select
End Sub

Note that "vbGreen" is a constant. You can also define a specific color, if you know the correct values. For example "lightseagreen" is 11186720, so you could do this:

Me.Detail.Backcolor = 11186720

Here's a list of colors:

If you're using Form Headers and Footers you'll need to set the BackColor of those as well:

Me.FormHeader.BackColor = 11186720

Author Comment

ID: 39786524
Thanks... when would it change colors... immediately after we update it or when we click onto the form?
LVL 85
ID: 39786723
The Current event fires for each record, so it would fire whenever you changed to a different record.

If you want this to fire only when the form is opened, then move it to the Form Open or Load event.

If you want it to fire when you update the value in a Control, then put it in that control's After Update event.
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.


Author Comment

ID: 39786790
It is almost working perfectly... the only issue is when there isn't a value on the next form, it stay the color of the prior form. Do I have to put a "case" for when it is null? If so, how? This is the current code I am using:

Private Sub Form_Current()

Select Case Me!cmOffice
     Case "Orange County"
      Me.Detail.BackColor = vbGreen
    Case "Riverside"
     Me.Detail.BackColor = vbBlue
End Select

End Sub
LVL 85

Accepted Solution

Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 39787168
Yes ... use a Case Else for those sorts of things (and you may want to use the Nz function also):

Select Case Nz(Me!cmOffice, "")
     Case "Orange County"
      Me.Detail.BackColor = vbGreen
    Case "Riverside"
     Me.Detail.BackColor = vbBlue
    Case Else
      Me.Detail.BackColor = vbWhite
End Select

Author Closing Comment

ID: 39787226
Thanks... works great. I am going to open another question for an "after update" code. If you can help on that, that would be awesome. Thanks again!

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

628 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question