Solved

Change Form background based on field value

Posted on 2014-01-16
6
2,332 Views
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
0
Comment
Question by:cansevin
  • 3
  • 3
6 Comments
 
LVL 84
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: http://cloford.com/resources/colours/500col.htm

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

Me.FormHeader.BackColor = 11186720
0
 

Author Comment

by:cansevin
ID: 39786524
Thanks... when would it change colors... immediately after we update it or when we click onto the form?
0
 
LVL 84
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.
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

Author Comment

by:cansevin
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
0
 
LVL 84

Accepted Solution

by:
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
0
 

Author Closing Comment

by:cansevin
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!
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

789 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