Deleting most recent record

[Dave31]

Hi

I have a form, that you enter detials on and when you select a field and enter detials in, it creates the autonumber! Now i believe the database saves this record to the table. If i select the Cancel button instead of Save, i want the entered data to be deleted from the table.
I have tried using the me.undo method, but that doesnt work, i Have tried just entering in the code DoCmd.RunCommand acCmdDeleteRecord but that doesnt delete the record either (i think it could be because the query is looking at two tables so cant delete the record, or i might be way off, all i know is it's not deleting).
I would like to run an SQL statement saying i would like to delete the last record from that table, how do i do this?
Or if anybody has any better ways, im all ears
Many Thanks

 

[Bat17]

If you are trying to undo the autonumber you cant. you should be able to Undo the record without saving it but the number will be skipped and you will get a gap in the numbering.

Peter

 

[Dave31]

yes, im not all that bothered about the auto numbering skipping, but currently, the record isnt deleting on an undo, or a record delete, thats why im hopeing an SQL delete can do it!

 

[Bat17]

the form should only be saving the record when you goto another record or close the form so your undo should work. Is there any other code that could be automaticaly saving the record?
Does the record clear properly if you use the Esc key?

Peter

 

[Dave31]

No, it only clears the last field entered when you press Esc! from looking at the table, as soon as you enter in detials in one field it assigns an auto number, and the record is saved in the table, regards of anything else.
Thats why if i cancel, im hoping theres an SQL statement that can delete the last record in the table.
I cant see any obvious code causing it to save. execpt, i have a sub form, and when i enter in one bit of detial, the focus jumps to this subform to populate a field, could this be the reason?

 

[Rich]

As soon as you enter the subform the main form data is saved

 

[Dave31]

yeah, i guessed that could be the only reason for it being saved. But does anyone have an answer?
Is there an SQL delete command to delete the last record in the table?

 

[Bat17]

DELETE Table1.id AS id FROM Table1 WHERE (((Table1.id)=(SELECT Max(Table1.id) AS id FROM Table1;)));

This should do it, altering names to suit.

But you say about two tables, will you need to clear related records from another table?

Peter

 

[Dave31]

i managed to do it now with the following:

Set db = CurrentDb

Set r = db.OpenRecordset("workProductTable")
   
r.MoveLast

            DoCmd.SetWarnings False
            r.Delete
            DoCmd.SetWarnings True
            DoCmd.Close

Many thanks for your help