BeforeUpdate on a non-bound field doesn't work?

[Maury Markowitz]

I'm trying to prevent users from entering report dates in the future.
There's two primary dates, start (month end) and end (typically
yesterday or today). To prevent them from adding odd dates, I added a
BeforeUpdate handler like this...

Private Sub MonthDateField_beforeUpdate(Cancel As Integer)
If Me.MonthDateField > date Then
answer = MsgBox("You are entering a report date in the future.
Are you sure this is right?", vbYesNo)
If answer = vbNo Then
Cancel = True
Undo
Exit Sub
End If
End If
end sub

When I enter a bogus value, next year in this case, the msgbox popped
up as expected, but when I clicked "no" to reset the date back to what
it was, nothing changed. The cursor didn't move, the highlight didn't
change, nothing.

Any ideas? I can check the validity later in the code, but I prefer
immediate warnings.

Maury

 

[Allen Browne]

Use the Exit event of the unbound control:

Private Sub MonthDateField_Exit(Cancel As Integer)
Dim strMsg As String
If Me.MonthDateField > Date Then
strMsg = "You are entering a report date in the future." & _
vbCrLf & "Are you sure this is right?"
If MsgBox(strMsg, vbYesNo + vbDefaultButton2) = vbNo Then
Cancel = True
Me.MonthDateField = Null
End If
End If
End Sub

(I don't think you want both Cancel and setting to null, but take your
pick.)