How to get a report field to be a certain backcolor based on field criteria

[catbeasy]

I have a report that is bound to a table with an ssn field in it (the ssn is captured as a string value). The report has the ssn field bound to a text box.

I would like the text box back colour to turn red if the first 3 numbers of the ssn value is "999".

The code I wrote is on the On Activate event of the report and, though it doesn't give any errors, it doesn't turn the 999 ssn's text box back colour red..

Any help as to what I'm doing incorrect is appreciated..

code:

If Left(Trim(Me.txt_SubSSN), 3) = "999" Then
Me.txt_SubSSN.BackColor = vbRed
Me.lbl_SSN.BackColor = vbRed
End If

 

[pbaldy]

I would use Conditional Formatting rather than code, but the place for the code would be the format event of the section containing the controls being formatted.

 

[catbeasy]

I would use Conditional Formatting rather than code, but the place for the code would be the format event of the section containing the controls being formatted.

I put the code in the On Format section (in the Detail section where the controls are) and it sorta worked. On the first page it worked. There are 3 ssn's on that page and the third one starts with a 999 and is highlighted.

But on each ensueing page*, all the SSN's are highlighted...How do I get this to evaulate each page and not just the first one?

*The report is header is a field called PHID which starts a new page each time this field value changes

Also, how do you use conditional formatting. If that will solve my problem, then I'll use that.

Note: I'm using Access 97, not sure if CF is available. I looked in help and didn't see anything..

Thanks for your continued assistance!

 

[pbaldy]

I don't believe CF was available in 97, so I guess you do need to use code. With code, you have to include an Else clause to set the fields back to default.

 

[catbeasy]

I don't believe CF was available in 97, so I guess you do need to use code. With code, you have to include an Else clause to set the fields back to default.

OK, that worked. Much obliged!

 

[R.Pare]

I have tried everything I can find in an attempt to get a box in a report to change backcolor. I have even put complete gibberish in the code and nothing happens (no errors, report is generated). The "Has Module" option is set to yes.
The report gets info from a query right now, but I have tried grabbing data straight from the table also.
The Box's background I want to change is [STAT]. There are 15 different options [STAT] can be. [STAT] has either 3 or 5 characters.
This is in Access 2007, I cannot use CF because that limits me to default+3 conditions and I need 6.
I have tried the above example as well as the suggestions found at http://bytes.com/topic/access/answers/633381-conditional-formatting

Thanks in advance.

EDIT: Ok a coworker found my problem. FYI if you have trust center issues with VB code, you have to enable them when digging around with the code.