Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2634
  • Last Modified:

Change Form background based on field value

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
cansevin
Asked:
cansevin
  • 3
  • 3
1 Solution
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
 
cansevinAuthor Commented:
Thanks... when would it change colors... immediately after we update it or when we click onto the form?
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
cansevinAuthor Commented:
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
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
 
cansevinAuthor Commented:
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now