Conditional Formatting on Initial Load of Form

[redman08]

I have a situation where, on loading a (new) record, I want to do some conidtional formatting (using VBA) to set the background for some date fields ("RAG Status" - Red/Amber/Green, depending on other values in recordset).

When tabbing through the fields I can get the various events to react correctly and set the background colour, as they do when dates are updated.

It's only only when the (new) record is loaded that I can't find the right event to handle the formatting correctly.

Any ideas?

Apologies if this appears to be too easy, or stupid on my part.

 

[KenHigg]

So a user adds a record and when they move off of that new record the formatting doesn't work?

 

[redman08]

The user selects a record (on the form header) via a key entry, the record is selected and the data is displayed on the form detail.

I want the backgrounds determined by some VBA behind the date fields at this initial stage. At the moment, I can't find an event to do this. Once you start to go through each field, or amend a field, the 'BeforeUpdate' / 'AfterUpdate' events work ok and conditional formatting occurs.

 

[KenHigg]

Hum... The seems the standard conditional formatting feature should work for this. You are using the built-in feature aren't you?

 

[redman08]

Because of 'future-proofing', I'm using VBA controlled by events behind the fields rather than standard conditional formatting. Although that should make things easier, don't you think!

I'm calculating 'number of days' between various dates in the recordset and then, depending on the values determined, I set the background/foregound colours.

 

[KenHigg]

Um... I've never tried to mimick the built in conditional formatting feature with vba. Not sure how to do that.

 

[redman08]

Because of 'future-proofing', I'm using VBA controlled by events behind the fields rather than standard conditional formatting. Although that should make things easier, don't you think!

I'm calculating 'number of days' between various dates in the recordset and then, depending on the values determined, I set the background/foregound colours.

 

[wiremonkey22]

This may or may not be the best way to do it but it works
this uses an after update and a form current with the same code good luck

Private Sub date1_AfterUpdate()
Select Case Me.date1
Case Date
Me.date1.BackColor = RGB(255, 0, 0)
Case Date - 1
Me.date1.BackColor = RGB(0, 255, 0)
Case Date - 2
Me.date1.BackColor = RGB(0, 0, 255)
Case Else
Me.date1.BackColor = RGB(255, 255, 255)
End Select
End Sub

Private Sub Form_Current()
Select Case Me.date1
Case Date
Me.date1.BackColor = RGB(255, 0, 0)
Case Date - 1
Me.date1.BackColor = RGB(0, 255, 0)
Case Date - 2
Me.date1.BackColor = RGB(0, 0, 255)
Case Else
Me.date1.BackColor = RGB(255, 255, 255)
End Select
End Sub

 

[gemma-the-husky]

the form's current event fires for every change of record, but if you go to the new record, then newrecord is true

so in current event you can say

if me.newrecord then
etc

 

[redman08]

I've tried using the "Form_Current()" event (I think I've tried most logically-sounding events), but it dies. I have to save all the dates from the form in an array and because of null data in some dates, it crashes.

 

[redman08]

Thanks guys for all your contributions. Eventually found the solution.

I put my 'check dates' coding (to establish the background colour) immediately after I've established that a record has been found, i.e. when searching for my key field, I get a 'key found' situation.

Sounds obvious now.