Error 2115 When Resetting Date Field? 2

[Hakala]

Hi, everyone! I've searched the forums for other 2115 questions and can't find one that suits my particular question.

I've got a data entry form with some dates. Code validates that the dates are in logical order (not processed before received, for instance). If a date order error is found, I want the form to remove the date the user entered and give them a message box. The message box comes up just fine, but on trying to remove the incorrect date, I get the 2115 error. (Run-time error '2115':
The macro or function set to the BeforeUpdate or ValidationRule property for this field is preventing Microsoft Access from saving the data in the field.)

The code that runs is:

Private Sub txtSite2Rec_BeforeUpdate(Cancel As Integer)

    blnAddMode = False
    
    If DatePart("yyyy", txtSite2Rec) <> 2006 Then
        MsgBox "Please check the Site 2 Receipt date."
    ElseIf Not IsNull(txtSite1Sent) And txtSite2Rec < txtLodiSent Then
        MsgBox "Site 2 Receipt cannot be before Site 1 Sent Date."
        txtSite2Rec = Null
        Exit Sub
    End If
End Sub

blnAddMode is an identifier for me to tell the code further down if this is a new record. The date field txtSite2Rec has no validation rule, and I can't set zero length to Yes because it's a date field. However, if I remove the date manually, the form accepts that just fine. I tried .Undo instead and it stopped me from getting the error, but the erroneous date remained in the form. Help?

 

[PHV]

Replace this:
txtSite2Rec = Null
with this:
Cancel = True

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Hakala]

That's close! It stops the process but does not remove the date they entered. If I don't figure out how to return the date field to null, at least they can tell where the issue is.

Thank you!

 

[PHV]

You may also try to add this:
txtSite2Rec.Undo

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Hakala]

The date remains in the field, but with Undo, the cursor goes on to the next field. Sometimes I think Access VBA plays jokes on me.

 

[SuryaF]

1. I think you replaced Cancel=True with the txtSite2Rec.Undo.
You should have them both.

2. If the Undo method doesn't remove the value it's probably because your textbox is unbound (has no Control Source).