Delete records to a diff table

[Geordie2008]

Afternoon All,

My user occassionally inputs incorrect data and wishes to delete it from the main table.

They sometimes input the same new employee twice with slightly diff spellings etc. When they notice this they want to be able to open the form with the incorrect data in it and delete it from the form (so they can check they are deleting the correct duplicate first).

I would like to use a button at the bottom of the form that is a delete button. I would like on pressing the button, the database to go away and check both the "current" and the "audit" tables for the said unique "ID" (an autonumber) and then insert a copy of this data into a deletions table and then remove all traces from the "Audit" and "Current" tables.

I cant find an example of a db that does this within the forum's....

I know how to do a StrSQL "INSERT INTO" for the data that is currently held in my form.... can I use this for running a query first and then inserting the query results into the "deletions" table?


Arghh..... dont know where to start with this.....

Thanks,
Mandy

 

[ajetrumpet]

Check out my Simultaneous Record Entry database in the sample database section for some syntax to use for the INSERT INTO command line.

As far as the button deleting the current record of a form, you can use this:

function test()

dim db as database, rs as recordset

set db = currentdb
set rs = me.recordsetclone

with rs
  .findfirst "[autonumber field] = " & me.autonumbertextbox
  .delete
end with

  me.refresh
  me.requery

rs.close
set rs = nothing
set db = nothing

end function