VB Code in repot not working!

[laccess]

Hi All,

Wondering if you can help. I'm fairly new to access and VB code so I cant quite figure where this is going wrong.

In a report I have put some code to the OnLoad event.

I'm wanting to highlight a field (combo) red if the current date is later than that of another field.

My problem is that the field back colour is changing no matter what the result is. I have tried adding a Else statement in with a random colour but this made no difference either.

Any help would be appreciated.

Code is below.


Option Compare Database

Private Sub Report_Load()

Dim OverduePlant As Date
Dim IngRed As Long
Dim IngNormal As Long


OverduePlant = ExpectedOffHire

IngRed = RGB(255, 0, 0)

IngNormal = RGB(900, 0, 0)

If Date = OverduePlant Then

PlantItem.BackColor = IngRed

Else

PlantItem.BackColor = IngNormal

End If
End Sub
[/SIZE]



[/FONT]

 

[Ranman256]

Put a stop point,f9, on the IF line.
Check the values of the variables. (Hover the cursor over the word)
See if its behaving correctly.

 

[Frothingslosh]

Instead of doing that, have you considered using conditional formatting? You can set up the formatting for the control you want to change, change the condition to 'Expression Is', and in the box, but the comparison. Such as

Date() > [OtherField]

 

[laccess]

Hi Guys,

Thanks for your reply's but i'm not really sure on how to apply either of them.

I'm all up for using the easiest method as I feel like I'm hitting a nail with a mash hammer.

Frothingslosh, On the report the item I'm trying to change is a combo box created by the query.

Is this where I use the code you suggested?

I thought I would have to put this into one of the Event properties?

Thanks again.

 

[Frothingslosh]

No.

Select the combo box, then on the ribbon at the top of the screen, make sure you're on the 'Design' tab. In the 'Font' group on that tab, you should see a button labeled 'Conditional Formatting'. The directions I gave you are used there - it allows you to change the formatting of the field based on the value in that field or any other field in the report, and it only applies those changes to the records that meet the criteria you specified.

So if you change it to 'Expression Is' and put

Date() >= [OverduePlant]

in the area provided (assuming that OverduePlant is the field you want to compare), then all lines where the value in OverduePlant is less than or equal to the current date will be formatted.

Then just select the red background or whatever you want for that condition, and you should be good to go.

 

[pbaldy]

The problem with your code is its location. It should be in the format event of the section containing the controls (presumably the detail section). Conditional Formatting is a good option, but I wanted to point out the problem with the code.

 

[laccess]

Thanks for this, this is the one that got it done. I tried re-locating the code into the format_detail option but that didn't work either way.

This also opened my eyes to the Conditional Formatting options in access.

Thanks again.

No.

Select the combo box, then on the ribbon at the top of the screen, make sure you're on the 'Design' tab. In the 'Font' group on that tab, you should see a button labeled 'Conditional Formatting'. The directions I gave you are used there - it allows you to change the formatting of the field based on the value in that field or any other field in the report, and it only applies those changes to the records that meet the criteria you specified.

So if you change it to 'Expression Is' and put

Date() >= [OverduePlant]

in the area provided (assuming that OverduePlant is the field you want to compare), then all lines where the value in OverduePlant is less than or equal to the current date will be formatted.

Then just select the red background or whatever you want for that condition, and you should be good to go.

 

[apr pillai]

When you want to do some other fancy work on your Report check this out:
Highlighting Report