Access 95 issues (I know my company are cheepskates) (1 Viewer)

[iain1978]

Hi,

I was wondering if you out their can give me advice.

I have a company DB that I've been asked to create and I'm having problems with conditional formatting. I'm using access 95 so I'm having to do this with code I believe (if there is an easier way I'm all ears)

Basically I have a report that has a field called Rag status and there is three values Red, Orange and Green.

I want my report to highlight these fields back ground colour to the respective status but I cant seam to get any where. I can type the code

If Me.RAGStatus = "Green" Then
Me.RAGStatus.BackColor = 65280

But this just changes every Rag status box the colour Green no matter what text is entered. I have then tried this code to try and solve it

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

If Me.RAGStatus = "Green" Then
Me.RAGStatus.BackColor = 65280
Else
If Me.RAGStatus = "Red" Then
Me.RAGStatus.BackColor = 255
Else
If Me.RAGStatus = "Orange" Then
Me.RAGStatus.BackColor = 33023
End If

End Sub

But it is not much help. Can you please advise how I can get this done before I lose the will to live

 

[KenHigg]

I'm not sure I can explain this very well but I'll try.

1. In the underlying query you do three new col, maybe ragstatusgreen, ragstatusblue and ragstatusred. Then you use iif() something like IIf([ragstatus] = "red", "Red",""). for each of these new cols.

So in the query you have something like:

ragstatusred:IIf([ragstatus] = "red", "Red","")

2. So now you have whatever flds your query returns, along with three flds where they will either have the color name or nothing.

3. Then on your report you place three txt boxes, one on top of the other, that have these three flds as the source. Make the back grounds transparent and you should either see one of the three colors. Of course then you simple format the txt of each txt box to the appropriate color...

???
Ken

 

[Rich]

Change the ForeColour instead, it's easier