Soft Delete Records (to an archive)

  • Thread starter Thread starter Jackie Bauer via AccessMonster.com
  • Start date Start date
J

Jackie Bauer via AccessMonster.com

I want to build into a form a way store deleted records in an archive table
for 30-days. Records may get deleted by accident or on purpose, but I want
a way to fall back in-case this happens.

So, the record would be deleted from the User's view and from the main
table, but added to an archive table.

Has anyone done anything like this before? What do I need to do?

Thanks,
Jackie
 
I would put a command button on the form with the caption "Archive"
In the On Click event, put code that would copy the value in the fields of
the regular table to the archive table and delete the row from the regular
table, then do a move next on the regular table so the next row shows in the
form, then a requery.
 
Hi Klatuu,

Do you have code that would do this? I've read a little on openArgs.
Would I use that method... how?.

Thanks,
Jackie
 
No, it has nothing to do with OpenArgs. OpenArgs is only for when you are
opening a Form. The code should go in the On Click Event for the button you
want to use to archive the record and delete if from main table. I will give
a quick example, but none of the names will be correct because I don't know
what they are and the code may have errors because I don't have a way to test
it. I do have the code at home in some old apps, but the apps that I work on
here don't have anything like it.

Private Sub cmdArchive_Click()
Dim rstArchive as Recordset
Dim strSQL as String

set rstArchive = dbopenrecordset("tblArchive") ' Open the table
With rstArchive
.Addnew 'Add a blank record
.Field1 = Me.txtBox1 'Copy the values of the form controls to
the
.Field2 = Me.txtBox2 'corresponding fields
.Update 'Update the changes
End With
strSQL="delete * from tblMaster where [mactivity] = '" & _
Me.txtKeyValue & "'" ' txtKeyValue is whatever unique value will
identify
'the row to be deleted
rstArchive.close 'Close the table
set rstArchive = Nothing 'Clear the reference to the recordset
End Sub
 
Hi Jackie,

The simplest way to do this is not to bother.

Instead, just add to the table a Date/Time field called DateDeleted,
with a defalt value of NULL. Modify the queries behind your forms etc.
to include the Where criterion
DateDeleted IS NULL

Then to "delete" a record, just set its DateDeleted field to Date(). The
records will remain in the table but will not be shown to the user or
retrieved by your queries. If you really want to dispose of them
permanently after 30 days, you can do so with a query that deletes
records where
DateDeleted <= Date() - 30
 
I'll try it. But, would I use the same code if some doesn't use a delete
button and deletes a record or a field by mistake?

I'm looking for a catch bucket for records that are deleted. Maybe the way
to go is to block deletes to happen only via the button........
 
Blocking deletes except by the Archive button is a good idea. Other than
that the only issue is someone deleteing the data from a text box. Then that
data element would be empty in the archive as well
 
Thanks John! That's extactly what I'm looking for.
It works great. Thanks again............
 
Back
Top