Change background color based on value

[denileigh]

I am trying to create a label report with the attached form. I have it all designed and seems to be working great. What I need is the car number (shown here in grey) field to changed based upon the content. For example this "car id" is TILX and needs to be gray. If it contains GATX the background needs to be red, if it's ACFX, green and so on.

Is there any way to accomplish this?

 

[pbaldy]

The simplest way would be Format/Conditional Formatting (on the ribbon in 2007). In 2007 and earlier, it's limited to 3 conditions plus the default. If you need more, you'll need to use code in the detail format event.

 

[denileigh]

We have 1/2 of the systems here running 07 and half on 03 so it's probably best to set up in 03 correct? Can you give me a sample code? I'm still just a little above novice level.

 

[pbaldy]

It is usually safest to develop in the lowest version. The code would look like:

Select Case Me.CarID  'the name of the control with the value to be tested
  Case "TILX"
    Me.TextBoxName.BackColor = 12632256  '(gray)
  Case "GATX"
    Me.TextBoxName.BackColor = vbRed
  Case Else
    Me.TextBoxName.BackColor = RGB(255, 255, 255)  '(white)
End Select

Note the different ways of specifying colors. More info in help.

 

[denileigh]

THANKS!!! Where can I get the color codes?

 

[pbaldy]

There are only a few of the vbRed type. One cheat I use is to have a form open in design view, use the color selector to change the backcolor of a textbox, and see what numerical equivelant Access gave it. Then I use that in code.

 

[Kryst51]

Here's one site. If you do a google search there are all kinds of places to go and look for them.

 

[denileigh]

Thanks all! Will get to work!

 

[denileigh]

Okay - stupid novice question...where do I put that code? Tried a couple of places but nothing happens.

 

[pbaldy]

The detail format event would be the spot. In the properties of the section:

http://www.baldyweb.com/FirstVBA.htm

 

[denileigh]

Okay - Text2 contains the field "Item".... in the "on click" I put the code below but nothing is happening when the report(labels) open:

Private Sub Text2_Click()
Select Case Me.Text2

Case "EAGX"
Me.TextBoxName.BackColor = 3937500 '(gray)

Case "GATX"
Me.TextBoxName.BackColor = 36095

Case "IPBX"
Me.TextBoxName.BackColor = 65535

Case "RPBX"
Me.TextBoxName.BackColor = 65535

Case "ACFX"
Me.TextBoxName.BackColor = 7456369

Case "RTMX"
Me.TextBoxName.BackColor = 7456369

Case "PTLX"
Me.TextBoxName.BackColor = 7456369

Case "NATX"
Me.TextBoxName.BackColor = 7456369

Case "JRSX"
Me.TextBoxName.BackColor = 16769482

Case "SHPX"
Me.TextBoxName.BackColor = 12698111

Case "TILX"
Me.TextBoxName.BackColor = 13224397

Case "TCIX"
Me.TextBoxName.BackColor = 15628703

Case "TGOX"
Me.TextBoxName.BackColor = 16775416

Case "UTLX"
Me.TextBoxName.BackColor = 16711935

Case "UCLX"
Me.TextBoxName.BackColor = 9145088

Case Else
Me.TextBoxName.BackColor = 3329434

End Select

End Sub

 

[pbaldy]

No, that's the click event of a textbox. I said the detail format event in the report. See attached.

 

[denileigh]

Okay, I put the code below into the format section of the detail on the report(label) as per this link:
http://www.access-programmers.co.uk/forums/attachment.php?attachmentid=31935&d=1272576420

It's giving me a macro error. Do I need to list the Textbox or the Field name? I tried it both ways but both locked up.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Select Case Me.Text2

Case "EAGX"
Me.TextBoxName.BackColor = 3937500 '(gray)

Case "GATX"
Me.TextBoxName.BackColor = 36095

Case "IPBX"
Me.TextBoxName.BackColor = 65535

Case "RPBX"
Me.TextBoxName.BackColor = 65535

Case "ACFX"
Me.TextBoxName.BackColor = 7456369

Case "RTMX"
Me.TextBoxName.BackColor = 7456369

Case "PTLX"
Me.TextBoxName.BackColor = 7456369

Case "NATX"
Me.TextBoxName.BackColor = 7456369

Case "JRSX"
Me.TextBoxName.BackColor = 16769482

Case "SHPX"
Me.TextBoxName.BackColor = 12698111

Case "TILX"
Me.TextBoxName.BackColor = 13224397

Case "TCIX"
Me.TextBoxName.BackColor = 15628703

Case "TGOX"
Me.TextBoxName.BackColor = 16775416

Case "UTLX"
Me.TextBoxName.BackColor = 16711935

Case "UCLX"
Me.TextBoxName.BackColor = 9145088

Case Else
Me.TextBoxName.BackColor = 3329434

End Select
End Sub

 

[Kryst51]

I have a suggestion, although I am not sure it would work, but it might save time if things ever change with your abbreviations. Maybe you could add afield to the table with the abbreviations for the back color you want, then you could do a code that looks at that field for the back color, you could have a non-visible text box on your form, then do something like the following in the after update event of your text32

Me.TextBoxName.BackColor = Me.txtBackColorField

It could save you a lot of select cases, and hassle in the future if anything changes. I am not sure it will work, it's just an idea I had.

 

[denileigh]

I appreciate everyone help with this - I will be SUCH a hero if I can make this work.

Kryst51 - thanks for the idea - I am open to anything but still just above the novice level so let me make sure I understand.

Are you basically saying to make an extra box with an if then statement?

I wish conditional formatting would work but it doesn't seem to be doing it.

 

[Kryst51]

I appreciate everyone help with this - I will be SUCH a hero if I can make this work.

Kryst51 - thanks for the idea - I am open to anything but still just above the novice level so let me make sure I understand.

Are you basically saying to make an extra box with an if then statement?

I wish conditional formatting would work but it doesn't seem to be doing it.

Here is an example of what I mean, granted I have no idea what your data looks like, but you should get the gist of the idea here.

 

[Rich]

Okay, I put the code below into the format section of the detail on the report(label) as per this link:
http://www.access-programmers.co.uk/forums/attachment.php?attachmentid=31935&d=1272576420

In the box that's displayed you should have [Event Procedure] your code then belongs in the vb window displayed for that event

 

[pbaldy]

Where I had "TextBoxName", you need the actual name of your textbox.

 

[denileigh]

I GOT IT ALL!

THANK YOU ALL SO MUCH FOR YOUR HELP!!!

*hugs*

 

[GregoryWest]

Where I had "TextBoxName", you need the actual name of your textbox.

Does the Me. part change too? I am working on something like this also. Basically need to change a text box to red if a flag is set.... Sounded easy when I started :banghead: