Background Color in a Field on a Report! 1

[proximity]

I have a text box on a report which displays information from a query. The report displays records that match the following criteria: RO-A, NL-A, RI-A. There are hundreds of records that match these criteria.

The output looks like this, each column representing a field from the underlying query:

123/4567 9999999 RO-A
123/4568 9999999 NL-A
123/4569 9999999 RO-A
123/4570 9999999 RI-A
123/4521 9999999 RO-A

How do I get it to display all the RO-A with a yellow background, all the NL-A with a blue background and all the RI-A with a green background? I'm sure it is dead simple, and I apologise if it has been answered before (but I can't find the answer

I am using Access 97

 

[CosmoKramer]

In the On Format event of the detail section, put code like this:

If Me.Yourfield = "RO-A" Then
   Me.YourField.BackColor = vbYellow
ElseIf Me.YourField = "NL-A" Then
   Me.YourField.BackColor = vbBlue
ElseIf Me.yourField = "RI-A" Then
   Me.YourField.BackColor = vbGreen
Else: Me.YourField.BackColor = vbWhite
End If

 

[proximity]

Hi Cosmo,

Looking at the code, it makes total sense. However, when I run the report, it's looking for a macro called 'me'. Any ideas?

 

[CosmoKramer]

OK, then try this:

If [Yourfield] = "RO-A" Then
   [YourField].BackColor = vbYellow
ElseIf [YourField] = "NL-A" Then
   [YourField].BackColor = vbBlue
ElseIf [YourField] = "RI-A" Then
   [YourField].BackColor = vbGreen
Else: [YourField].BackColor = vbWhite
End If

Let me know if this helps.....

 

[proximity]

Hi Cosmo,

Again, the code looks just the job, but once more it is asking for a macro "if [YourField]="RO-A" Then [YourField]" etc, etc......

 

[CosmoKramer]

Did you put this code in the On Format event of the detail section??

 

[proximity]

Yes, just checked it again to be sure, but yes!

 

[CosmoKramer]

Can you copy & paste your code here so we could take a look at it??

 

[proximity]

Yes:

This is what I have amended it to:
If [status] = "RO-A" Then
[status].BackColor = vbYellow
ElseIf [status] = "NL-A" Then
[status].BackColor = vbBlue
ElseIf [status] = "RI-A" Then
[status].BackColor = vbGreen
Else: [status].BackColor = vbWhite
End If

This is placed in the On Format box with the appropriate report section highlighted . . .

 

[CosmoKramer]

That code looks fine to me....What is the entire text of the error message you get looking for the macro??

 

[proximity]

Here it is:

Cat 60 Database can't find the macro "If [status] = "RO-A" Then [status]"

The macro (or its macro group) doesn't exist, or the macro is new but hasn't been saved.

 

[CosmoKramer]

Can you send me a zipped copy of your db so I can take a look at it??

sw3540@yahoo.com

 

[proximity]

I wish I could! Unfortunately, it is confidential company property and I cannot pass the data onto a third party

Apart from that, even zipped up and optimised it comes in at 11mb .

I will keep fiddling around with it - it has surely got to work somehow!!!

 

[CosmoKramer]

This is placed in the On Format box with the appropriate report section highlighted . . .

In the On Format box, did you:

Click on the ellipses (...), then in the Choose Builder box, select Code Builder, then click OK

Then paste the code under the following header:

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

 

[proximity]

I tried that way a few minutes ago and the report will run fine, no error messages, but without color! Previously, I had just pasted it into the box

 

[proximity]

Got it!!!! The code works a treat. All I had to do was remove the color already showing in the properties box and off it went! It looks fab. Many, many thanks Cosmo - you are genius incarnate!

 

[CosmoKramer]

Thanks for the kind words...I'm just glad I could help....