Cancel saving a record (1 Viewer)

Good afternoon

Just a quick one today... wondered if anyone has any (nice, easy to understand) ways of stopping a record saving to a table.
I have a 'cancel' command button on my form but even when pressed the record still saves in the table as I have an autonumber field (so it just saves the autonumber and leaves the other fields blank)??
Any advice would be very welcome; I have had a look at other posts which describe setting a global variable and using the 'Cancel = True', with focus on the before update event - I did try this yesterday but couldn't get it to work properly; it was still saving the record in the table... I'm still in the testing phases of my database and keep ending up with hundreds of records unnecessarily

Many Thanks

I have also tried the following;

Private Sub CmdCancel_Click()
MsgBox "Are you sure you want to cancel this entry?", vbYesNo
If vbYes Then
DoCmd.RunCommand acCmdUndo
End If
End Sub

but I get an error message saying Run-time error '2046'; 'the command or action 'undo' isn't available now'??

Also tried
Private Sub CmdCancel_Click()
MsgBox "Are you sure you want to cancel this entry?", vbYesNo
If vbYes Then
DoCmd.RunCommand acCmdDeleteRecord
End If
End Sub

But I get the same thing?

I seemed to have solved this with a simple me.undo on the command buttons 'on click' event... seemed to simple... I'm sure I always think things are more complicated than they are... can anyone tell me - if it OK to use this... or is there a safer / better way?
My code now is:
Private Sub CmdCancel_Click()
MsgBox "Are you sure you want to cancel this entry?", vbYesNo
If vbYes Then
Me.Undo
End If
End Sub

The form is a sub form on the main form - the me.undo seems only to relate to the sub form which is perfect but could I be missing something???

you need to understand the access model. When you enter data with a bound form your data is automatically saved when you close the form/leave the record etc, and in various other ways.

to abort the save you have to undo the actions, before the write is committed. you can press escape, or simulate this action in code eg RunCommand acCmdUndo, among other ways.

however, once an autonumber is issued, access internally advances the counter, and the autonumber is not re-used. This should not matter, however.

what may be happening is that you are saving the record, and then trying to undo or delete the next ie a new record.

if you want to explicitly save or cancel new records, the best way is to vuse an unbound form, imo - but that gives you more work to do to manage the data.


-----
with regard to your last post, the form/subform makes a difference. what was possibly happennig was this - you edit an entry in the subform. As soon as you leave the subform and go to the mainform, the record is saved, and you can now no longer undo the changes. So adding the button to the SUBFORM, means that the subform always has the focus.

The easiest way to see all this, is to add record selectors to your form, at least during development. When a record is dirty (edited) the black triangle changes to a pencil. So you can see your record status changing as you do different things.

Thank you for this; I am finding a different scenario now actually as I also have a 'cancel' button the main form (this is a form for entering people's company / contact details - its a sales contact management database)... I have tried putting the same code on the 'cancel' button on this main form to see whether it would undo a change (e.g. if I altered a phone number and then half way through decided it was wrong and wanted to revert to the old one)... it wont work - it doesn't 'undo or 'revert' the changes at all???

Is this to do with the fact that as soon as I leave the control (i.e. the telephone field) to press the cancel button) the record has already saved the changes?

Ahhh, I think it's because the contact details are on a sub form - can I reference the sub form before I do the undo?

As I understand it it should only save the changes in the cache until you leave the record (whether by closing the form or by running the VBA code to save it), the same way that when inputting into a table directly the record is not saved until you move the focus out of that record and into another.

Do you currently have any before / after update events on the controls or the form itself?

:edit:

I tend to use unbound forms for most things myself for easier control of things like audit trails (for exactly the reason that it only makes changes to the data when I tell it to).