Date Validation Gives Uncontrollable Error

[TastyWheat]

I have code in a BeforeUpdate subroutine of a text box to validate the date. The user can choose to keep or discard the value but the warning only appears if the date is in the future or at least a month old. This is my code:

Private Sub txtDate_BeforeUpdate(Cancel As Integer)
    Dim dtCurrent As Date
    Dim objResult As VbMsgBoxResult
    
    If (Trim(txtDate) = "" Or IsNull(txtDate)) Then
        Exit Sub
    End If
    
    objResult = vbYes
    dtCurrent = CDate(txtDate)

    If (dtCurrent > DateTime.Date) Then
        objResult = MsgBox("This date is in the future.  Are you sure you wish to leave this value?", vbYesNo, "Warning")
    ElseIf (DateDiff("m", dtCurrent, DateTime.Date) > 1) Then
        objResult = MsgBox("This date is at least one month old.  Are you sure you wish to leave this value?", vbYesNo, "Warning")
    End If
    
    If (objResult = vbNo) Then
        Cancel = True
    End If
    
End Sub

It works fine when I answer "Yes" but if I hit "No" I get this message: The value in the field or record violates the validation rule for the record or field.
This control doesn't have a validation rule. In fact, there are no validation rules in any of the controls on this form nor on any of the attributes in the control source (table). The date is a required attribute of the table though. I can't hide this extra error message by turning off warnings because it happens after I exit the subroutine, so I wouldn't be able to turn the warnings back on. I also can't trap this error because it occurs after the subroutine ends.

If possible I would want the text box to be cleared and be focused if the user selects "No" to discard the date.

 

[Smart]

Try placing an Exit Sub after
Cancel = true

 

[TastyWheat]

I added that bit and it still gives me the error. I don't need the code to work exactly like I want it to. However, as long as the user enters "No" to my warning message I would like the date box to maintain the focus. If I add SetFocus commands to BeforeUpdate, AfterUpdate, or LostFocus events I either get an error or it just doesn't work (and skips to the next control).

 

[TastyWheat]

Success!

Okay, I made a little workaround. Here's my updated code:

[COLOR="blue"]Private Sub[/COLOR] txtDate_AfterUpdate()
[COLOR="blue"]On Error GoTo [/COLOR]Err_txtDate_AfterUpdate
    
    [COLOR="blue"]Dim[/COLOR] dtCurrent [COLOR="blue"]As Date[/COLOR]
    [COLOR="blue"]Dim[/COLOR] objResult [COLOR="blue"]As VbMsgBoxResult[/COLOR]
    
    [COLOR="Green"]' Stop if control is empty[/COLOR]
    [COLOR="blue"]If[/COLOR] (Trim(txtDate) = "" [COLOR="blue"]Or[/COLOR] IsNull(txtDate)) [COLOR="blue"]Then[/COLOR]
        [COLOR="blue"]Exit Sub[/COLOR]
    [COLOR="blue"]End If[/COLOR]
    
    objResult = vbYes
    dtCurrent = [COLOR="Blue"]CDate[/COLOR](txtDate)

    [COLOR="Green"]' Check if date is too far ahead or behind[/COLOR]
    [COLOR="blue"]If[/COLOR] (dtCurrent > DateTime.Date) [COLOR="blue"]Then[/COLOR]
        objResult = MsgBox("This date is in the future.  Are you sure you wish to leave this value?", vbYesNo, "Warning")
    [COLOR="blue"]ElseIf[/COLOR] (DateDiff("m", dtCurrent, DateTime.Date) > 1) [COLOR="blue"]Then[/COLOR]
        objResult = MsgBox("This date is at least one month old.  Are you sure you wish to leave this value?", vbYesNo, "Warning")
    [COLOR="blue"]End If[/COLOR]
    
    [COLOR="green"]' Start timer if user chose to cancel changes[/COLOR]
    [COLOR="blue"]If[/COLOR] (objResult = vbNo) [COLOR="blue"]Then[/COLOR]
        Form.TimerInterval = 10
    [COLOR="blue"]End If[/COLOR]
    
End_txtDate_AfterUpdate:
    [COLOR="blue"]Exit Sub[/COLOR]
    
Err_txtDate_AfterUpdate:
    MsgBox Err.Description

[COLOR="blue"]End Sub[/COLOR]

I also added this subroutine for the timer:

[COLOR="Blue"]Private Sub[/COLOR] Form_Timer()
[COLOR="Green"]' Timer is turned off; focus is set on date control; date control is cleared[/COLOR]

    Form.TimerInterval = 0
    txtDate.SetFocus
    DoCmd.RunCommand acCmdDelete
[COLOR="Blue"]End Sub[/COLOR]

The BeforeUpdate, AfterUpdate, and LostFocus events apparently take place before the Tab/Enter is processed. I don't know how to cancel the Tab/Enter key being processed so I just setup the timer to set the focus after the Tab/Enter occurs.