Deleting a record when that UniqueID already exhist 2

[WallT]

I use the following code to stop users from entering a date on a record when that date has already been used in another record:

If Not IsNull(DLookup("[ActivityDate]", "[tblActivitySheet]", "[ActivityDate]=#" & Format([ActivityDate], "yyyy-mm-dd") & "#")) Then
MsgBox "There is already an Activity Sheet for this date, please search by date to locate..."
Cancel = True
End If

The record in the table is set to "No Duplicates", so I am trying to trap the error and add a custom message.

This works so far. The problem I am having is that I would like to make the Date field Null and "Undo" or "Delete" that record that was started once this error is trapped like this.

Right now the user realizes that date already exhist once the above MsgBox tells him so. So he then wants to leave this record and go to the record that already exhist with that date. But since this record has already started, it wont let him leave until he finishes completing this record...ie mandatory fields and a new date entered...etc...

 

[ProgramError]

If the record has already been added to your table then a simple delete query looking at the last record should be enough.
Is the entry for bound to a table then you should unbind it and use an append query to add the data, only when the data is correctly entered.

Hope this helps
Ian M

Ian Mayor (UK)
Program Error
Always make your words sweet and nice. Because you never know when you may have to eat them.

 

[Remou]

Which event are you using? Have you tried Before Insert or Before Update?

http://office.microsoft.com/en-us/access/HP051867611033.aspx

 

[PHV]

Why not simply add Me.Undo in the If body ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[GoinDeep]

Thanks PHV. That is one I did not try. I knew Cancel wans't right...I just never thought of something that simple.

 

[WallT]

Thank you