A Deleted Record Can't be Recovered?

[bostonfrog]

The people who do data input on my soon-to-be released DB want the ability to undo a deletion of a record. I included a confirm message box to prompt them if they're sure they want to delete it, but they'd like to get it back. It seems that posts here from January 2003 indicated that a deleted record canNOT be recovered; I am not talking about a deleted table or a series of records, just the one they had just deleted accidentally. I am not a high-level Access programmer by any means.
I included a "Save record" button on the form, but might there not be some way of writing the current record to a temp table and retrieve the record information back from the temp table using another command button, e.g. Restore Record ? Maybe not. Any ideas appreciated.

 

[sirkenj]

I have a similar situation, here is what I did:

1. Copy / Paste the table containing the records you are deleting. When you paste, select the option to only paste the structure, not the data.

2. Create an update query which will copy the record selected on your form from your main table into your new table.

3. Create a macro which will:
a. MsgBox to confirm deletion
b. Run update query to copy data
c. Delete selected record
d. Refresh or close form

4. You can then do the same process outlined above, but with the query updating in the other direction to "un-delete" these records. (from the "deleted" table into the original table)

I code my records with the date deleted and username of who deleted them as they are being moved from my original table into the "deleted" table as well. The date is useful because you will need to either manually, or programatically clean out old records from the deleted table, or it will keep growing and growing. (I attach another query to the "OnClose" event of the form which will delete any records from the "deleted" table which are older than 6 months.

 

[PaulBricker]

Well, a lot depends on how the user goes about deleting the records. In a form, unless you have it set to Datasheet View, it's not real simple to delete a whole record so I'm wondering what they anticipate happening. With that in mind, you can archive records to another table using an append query and then run some code to delete the appended record but again, it's going to depend on how you really want to handle it. If the people are going into queries and tables to delete records then you basically have a real problem on your hand.

Paul

 

[bostonfrog]

Sirkenj,
I more or less get the drift of how you did this, but I would like to know how you create an update query to just update the current record on the form, not all the records in the table? Wouldn't you want to use an APPEND Query to append this record to your new empty table with the same structure? Is it possible for you to email me a zip of your file, or at least the parts of it I need to understand, so I can see how this was accomplished? Thanks.

Michel
mtresfort@partners.org

 

[Gazonice]

Hi,

I have databases with a built in 'recycle bin'.

All it actually is, is an extra checkbox field in a table. If a users deletes a field he is simply putting a tick on the box, and a simple query omits that record from display, unless the tick is removed from the checkbox field (done through a form I call the recycle bin).

Hope this helps to provide a simple solution!

Garry

 

[ALEKSJAY]

That is a great idea, Gazonice