change text color based on data (1 Viewer)

[SaraMegan]

Hi. I have a form with a subform laid out tabularly (is that a word?) so you can view multiple records in the subform for each record in the main form.

Is it possible to take one of the fields on the subform (a lookup field with three options) and color the text the record is in according to the result of that field? I wasn't planning on having that field visible.

I understand how to do an if statement for it or a select case, but I put my code in the Form_Open event. What it does is just look at the first record and color everything that way.

So how can I get it to give each record a different (specific) color based on this criteria? Any ideas? Is it possible?

This is just a silly little feature I thought would be helpful to the users to see some patterns at a glance. It's not hugely important, but it's this kind of thing that helps me learn. I kinda like learning.

Thanks in advance.

--Sara

 

[Dave Eyley]

It depends on the version of Access you are using. '97 has no feature that will do this but there are 'get arounds'. Search this topic on ther forum, I downloaded a good example a few weeks back.

If it's Access 2000, there is a feature called conditional formatting that I believe can used, although I don't have 2000. Again, do a search for it on the forum.

HTH

Dave E

 

[SaraMegan]

Okay, I've done a search (I did one before I posted, too...) and I'm still having trouble.

I've gone into the design mode of the subform and I've written this code:

Private Sub Form_Open(Cancel As Integer)
Select Case Me.CertifyID
Case 1
Me.PositionNo.ForeColor = 0
Me.PositionTitle.ForeColor = 0
Me.CostCenter.ForeColor = 0
Me.Office.ForeColor = 0
Me.Recvd.ForeColor = 0
Me.DatePosted.ForeColor = 0
Me.CloseDate.ForeColor = 0

Case 2
Me.PositionNo.ForeColor = 255
Me.PositionTitle.ForeColor = 255
Me.CostCenter.ForeColor = 255
Me.Office.ForeColor = 255
Me.Recvd.ForeColor = 255
Me.DatePosted.ForeColor = 255
Me.CloseDate.ForeColor = 255

Case 3
Me.PositionNo.ForeColor = 9868950
Me.PositionTitle.ForeColor = 9868950
Me.CostCenter.ForeColor = 9868950
Me.Office.ForeColor = 9868950
Me.Recvd.ForeColor = 9868950
Me.DatePosted.ForeColor = 9868950
Me.CloseDate.ForeColor = 9868950

End Select

End Sub

I'm thinking maybe I'm just putting the code in the wrong place? None of these fields are changeable, so I can't use an AfterUpdate or anything like that.

Again, this works, but it colors each record based on the first record's result... I'd like it to look at each record.

Anyone else have any thoughts?

Thanks

--Sara

Oh, and I'm using Access97

 

[Dave Eyley]

On your search, did you find the example attached below?
I got it from the forum a week or so ago.

Dave E

 

[SaraMegan]

Thanks, Dave

Only I don't think that's what I want.

I want to change the text color, not the background color. And the text I want to change is already bound to its own field, so can't be bound to the option buttons.

Thanks anyway.

I guess this just isn't something that Access97 can do?

--Sara

 

[Rich]

Technically it can be done and without code, you just have to overlay three copies of each control and change the control source dependant on your criteria, it can get messy though.