Add a record to a recordset

[Zygor]

I have an unbound form that is tied to an ADOBD recordset in Access 97.

I have an add new button that blanks out the 2 data fields and allows data entry. 1) Holiday name and 2) txtDate.
on the before update of the date field, I check to see if that date is already accounted for.

Private Sub txtDate_BeforeUpdate(Cancel As Integer)

If IsNull(Me.txtDate) = False Then
If DLookup("[Date]", "Holiday", "[Date] = #" & Me.txtDate & "#&quot > 0 Then

Cancel = True
MsgBox "That date is already entered and cannot be entered again."

End If
End If
End Sub

At this point I want to be able to clear the date from the form.
I've tried forms!Holiday!txtDate = null and that produces the following error.

***********************
Run-time error '-2147352567 (80020009)':
The macro or function set to the BeforeUpdate or ValidationRule property for this field is preventing Access from saving the data in the field.
***********************

There is no validation rule set for the field.

I could really use some insight.

Thanks!

 

[JerryDennison]

Try the following instead:

Private Sub txtDate_BeforeUpdate(Cancel As Integer)

If IsNull(Me.txtDate) = False Then
If DLookup("[Date]", "Holiday", "[Date] = #" & Me.txtDate & "#&quot > 0 Then

Cancel = True
MsgBox "That date is already entered and cannot be entered again."
Me.txtDate.Undo

End If
End If
End Sub

 

[Zygor]

I appreciate he suggestion. I tried it but it did absolutly nothing. Not even an error.

 

[JerryDennison]

Did it undo the field for you at all? Try the following then:

...
Me.txtDate.Undo
Me.txtDate = Null
...