Which event to use on a form

[petern]

I have a calculated field on a form that takes the date value of another field and runs DateDiff to get the difference in days between the other field and today's date. ControlSource=Nz(DateDiff("d",[ApplDate_MatchEnd],Date()),0)

I now need to have a message box pop up when the difference is greater than -30 (There are thirty days or fewer to the end of the match or the match has expired). Which would be the appropriate event to put the code into? The form is a single form. Is this a form event or an afterupdate event on the calculated field?

[agibsonsw]

Hello.
I presume you could use the AfterUpdate event of the ApplDate_MatchEnd field. The MsgBox could then pop-up when they enter this date. Andy.
If Nz(DateDiff("d",[ApplDate_MatchEnd],Date()),0) > -30 Then
MsgBox "..."
End If
Andy.

[petern]

The calculated field doesn't kick in the afterupdate event. The form current event gave me what I needed. Thanks, anyways.

[HansV]

Andy's suggestion was not to use the After Update event of the calculated control (which will not fire indeed), but the After Update event of the control bound to ApplDate_MatchEnd. That should work, unless ApplDate_MatchEnd is a calculated control too.

[petern]

ApplDate_MatchEnd is not calculated but I need the msgbox code to run every time the record is accessed, not just when ApplDate_MatchEnd is changed. The form OnCurrent event seems to be the way to go.

[HansV]

In that case, I'd use both the After Update event of ApplDate_MatchEnd *and* the On Current event of the form. The former for when the user edits ApplDate_MatchEnd, the latter for when the user moves to a different record.

[petern]

That had crossed my mind but not settled anywhere useful as a thought . Thanks for reminding me.

Peter

[JohnH]

Another option here is just to use Conditional Formatting to either show the calculated field in a distinctive colour, or pehaps show something else distinctive.

I think I would find the msgbox popping up all the time (unless these events are rare) really annoying after a while.

[petern]

In this instance, the event is fairly rare and the goal of the department is to keep clients matched with volunteers, so if they aren't being matched, the worker needs to be annoyed! Hadn't thought of conditional formatting. I'll tuck that one away for future reference.