Conditional formatting - almost there but not quite! (1 Viewer)

[RCheesley]

Hi all,

Wonder if you can help, I'm trying to get text boxes to conditionally format depending on the dates which are updated automatically when a field is completed.

So far I have the following (please feel free to point out if there's a better way of doing it - I'd like to have it happen when the record shows rather than afterupdate but haven't got around to figuring this out yet!

Private Sub SUIDateOfAwareness_AfterUpdate()
Me.SUIDeadlineInitialInvestigation.Value = (Me.SUIDateOfAwareness.Value + 1)
Me.SUIDeadlineSHAInformed.Value = (Me.SUIDateOfAwareness.Value + 3)
Me.SUIDeadlineIOReport.Value = (Me.SUIDeadlineInitialInvestigation.Value + 14)
Me.SUIDeadlinePanelDecided.Value = (Me.SUIDateOfAwareness.Value + 14)
Me.SUIDeadlinePanelHearing.Value = (Me.SUIDateOfAwareness.Value + 42)
Me.SUIDeadlineFinalReport.Value = (Me.SUIDeadlinePanelHearing.Value + 14)
Me.SUIDeadlineSHAReport.Value = (Me.SUIDateOfAwareness.Value + 63)
 
'The code above works fine
 
If Me.SUIDeadlineInitialInvestigation.Value > Now() And IsNull(Me.SUIDateInitialInvestigation.Value) Then
     Me.SUIDateInitialInvestigation.BackColor = vbRed
If (Me.SUIDeadlineInitialInvestigation.Value > Now() - 7 < Now() And IsNull(Me.SUIDateInitialInvestigation)) Then
    Me.SUIDateInitialInvestigation.BackColor = -39662
If Not IsNull(Me.SUIDateInitialInvestigation) Then
    Me.SUIDateInitialInvestigation.BackColor = vbGreen
Else
     Me.SUIDateInitialInvestigation.BackColor = vbWhite
End If
End If
End If
End Sub

I used the colour code for the middle one just to try it out - had also tried with vbYellow.

The logic is:

If the current date is greater than the deadline and the date box isn't filled, go red (i.e. overdue)

If the current date is within 7 days of the deadline and the date box isn't filled, go orange (-39662)

If the current date is more than 7 days before the deadline then go white

If the completed date is filled in (i.e. it's been done) then go green

Thanks in advance,

Ruth

 

[DCrake]

You need to use either the ElseIf or a Select Case statement

You if's are all being performed sequentially.

If Me.SUIDeadlineInitialInvestigation.Value > Now() And IsNull(Me.SUIDateInitialInvestigation.Value) Then
Me.SUIDateInitialInvestigation.BackColor = vbRed
ElseIf (Me.SUIDeadlineInitialInvestigation.Value > Now() - 7 < Now() And IsNull(Me.SUIDateInitialInvestigation)) Then
Me.SUIDateInitialInvestigation.BackColor = -39662
ElseIf Not IsNull(Me.SUIDateInitialInvestigation) Then
Me.SUIDateInitialInvestigation.BackColor = vbGreen
Else
Me.SUIDateInitialInvestigation.BackColor = vbWhite
End If


David

 

[RCheesley]

Ahar, it's working now!

What would be the best event to have this trigger on? The date calculations seem to be fine as the only time they need changing is if the date is changed (which is never should be after it is first entered)

Ideally, any time any of the dates are edited, and whenever the record is displayed (i.e. when the form is opened, or when browsing through the records) the vba should run to re-check the dates.

Ruth

 

[RCheesley]

Hmm, the plot thickens.

It's working, but I have several boxes I need to apply the same logic to. It's bombing out on anything past the first two sets (I have only posted the first three sets as there are quite a few!):

'This code changes the colour for the Initial Investigation box depending on deadline date
If Me.SUIDeadlineInitialInvestigation.Value < Now() And IsNull(Me.SUIDateInitialInvestigation.Value) Then
     Me.SUIDateInitialInvestigation.BackColor = vbRed
ElseIf (Me.SUIDeadlineInitialInvestigation.Value > Now() - 7 < Now() And IsNull(Me.SUIDateInitialInvestigation)) Then
    Me.SUIDateInitialInvestigation.BackColor = 39662
ElseIf Not IsNull(Me.SUIDateInitialInvestigation) Then
    Me.SUIDateInitialInvestigation.BackColor = vbGreen
Else
     Me.SUIDateInitialInvestigation.BackColor = vbWhite
End If

'This code changes the colour for the SHA Informed box depending on deadline date
If Me.SUIDeadlineSHAInformed.Value < Now() And IsNull(Me.SUIDateSHAInformed.Value) Then
     Me.SUIDateSHAInformed.BackColor = vbRed
ElseIf (Me.SUIDeadlineSHAInformed.Value > Now() - 7 < Now() And IsNull(Me.SUIDateSHAInformed)) Then
    Me.SUIDateSHAInformed.BackColor = 39662
ElseIf Not IsNull(Me.SUIDateSHAInformed) Then
   Me.SUIDateSHAInformed.BackColor = vbGreen
Else
     Me.SUIDateSHAInformed.BackColor = vbWhite
End If



'This code changes the colour for the Investigating Officer's Report box depending on deadline date
If Me.SUIDeadlineIOReport.Value < Now() And IsNull(Me.SUIDateIOReport.Value) Then
     Me.SUIDateIOReport.BackColor = vbRed
ElseIf (Me.SUIDeadlineIOReport.Value > Now() - 7 < Now() And IsNull(Me.SUIDateIOReport)) Then
    Me.SUIDateIOReport.BackColor = 39662
ElseIf Not IsNull(Me.SUIDateIOReport) Then
    Me.SUIDateIOReport.BackColor = vbGreen
Else
    Me.SUIDateIOReport.BackColor = vbWhite
End If

I have tried commenting out the third set and leaving the rest, tried commenting out the second and leaving the third, but the third+ sets never work .. I'm sure its something stupid but I even tried copying the first one, and just changing the names (using the vba editor's autocomplete to make sure I didn't make any typo errors.

The error occuring is Compile Error: Method or data member not found and it highlights the .BackColor = section on the third set of statements.

Is this something to do with using the same thing more than once, or have I made yet another noobie error?

Ruth

 

[RCheesley]

Any ideas on this? I wonder if there's a better way to do it, perhaps using a module, but not sure?

 

[RCheesley]

Figured it out - I had a box which I was going to use for decoration purposes - the controls whcih were within the box didn't work! I deleted the box, deleted those text boxes and re-made them and it works fine now

Ruth