Delete Query

[SageOne]

How should I construct a delete query, to only delete the last record in a
table?

I have a form directly tied to a table. The form displays fields for an
application for employment. I want to add a command button on the application
form tied to a delete query to delete the current (last) record if the person
decides to stop filling out the application in the middle filling it out. I
want to avoid having the person manually erase each field if they decide to
stop filling in the App in the middle of filling it out.

 

[Lord Kelvan]

delete from table where last(tableid)

should do it

but last is a funny word and shouldnt be trusted

regards
kelvan

 

[Lord Kelvan]

after reading the second part if you are using the form wizard for the
form you can use

DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70

if you are using DAO you can use recotdset.cancel

but if you are using the form and updating each field as you go
through use my last post

but find which of the methods you are using first ebcause most likly
you are not creating a redord till you click save if you are using the
form wizard so try

DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70
on a button

 

[John Spencer]

If you haven't saved the record that is bound to the form. You can use

Me.Undo to wipe out the changes (which on an unsaved record means that the
record will not be saved)

If you have saved the record you can use, the following code to delete the
currently displayed record.
DoCmd.RunCommand acCmdDeleteRecord

Both of the above methods assume that you have one table that is displaying
the information or that you have cascase delete turned on for the related tables.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[SageOne]

John,

Thank You for your response. Where do I copy that into? I tried to copy it
into my command (on click) and in my code builder. Neither Worked.

 

[Lord Kelvan]

put it in the on click event on a button and label the button cancel
or something

 

[SageOne]

Now I have had to change things. Since there were so many fields in the
employment application, I have had to break the application down into 7
sections. Each form ties to a query relating to a certain section. They are
linked with an autonumber created every time a new application is filled out.
do I need a new command to delete a record through a query?

 

[Lord Kelvan]

are you talking about deleting an existing record or one in the
progress of being created

 

[SageOne]

An existing record. I basically need to create a delete query to delete the
last row of the table ( which would mean it would be the last record
entered). If I set my delete query to show the last of my auto number then
will pull up multiple records because all of the records of the other fields
are not exactly the same with the exception of the auto number.

I just need to figure out how to set a delete query to delete the last
record of a table assuming i have multiple fields with varying record values.

 

[Lord Kelvan]

delete from table where primarykey = last(primarykey)

or

delete from table where primarykey = max(primarykey)

 

[SageOne]

This sounds like what I need. I tried it out but I get an error message
telling me that I cannot put an aggregate function in the criteria field of
the delete query.

 

[John Spencer]

DELETE
FROM Table
WHERE PrimaryKey =
(SELECT Max(PrimaryKey)
FROM Table)

Of course that is dangerous since the primary key is not guaranteed to be
sequential, so you could be deleting the wrong record. And you now seem to be
talking about records in multiple tables that need to be deleted.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County