VBA to create a temporary copy of a record prior to deleting

[dcdimon]

I don't know if there's a way to do this that won't require a page full of scripting, but I'd like to create an auto backup function to enhance my record deletion button. What I'd like is to be able to backup the record they're deleting into a temporary table (a copy of the original) in case they realize they need to retrieve it after pressing the Yes-Delete-This button.

My thought is that I'd periodically clean out the backup table based on a length of time - say anything older than 15 days - but keep newer records available in case there's been a mistake made.

I'm hoping there's a way to select the record, copy it and then paste it into the backup table. However, I don't want to have to spend days writing the script or trouble shooting it becuase, to be honest, the user is prompted multiple times to verify the record really should be deleted.



I have seen a few examples of copying records, but those have involved only partial records and I want to copy every field for the record into an exact duplicate of the original table.

Is this possible?

DD

[pbaldy]

Sure, just run an append query with a criteria. That said, I generally wouldn't actually delete a record, just flag it somehow. That lets you exclude it from forms or reports but get it back easily.

[dcdimon]

I had thought about just creating a field to tag as deleted - or change the .tag property to equal deleted to keep the records from showing. I have a working process to append and delete the record.

However, I think I'm going to try to tagging the record using the same process, rather than deleting the record. I think there's a reason I didn't go the tag and filter route, but since I can't remember why at the moment I'll try this. I would rather keep the data, just in case, since keeping data takes up almost no space and it's easy to ignore it on the data-out side.

Thanks for the help.

[pbaldy]

I'm not sure the tag property is appropriate, as I'm not aware of a record-level tag property. I'd have either a Yes/No or a text field, depending on your needs.

[dcdimon]

I'm not sure the tag property is appropriate, as I'm not aware of a record-level tag property. I'd have either a Yes/No or a text field, depending on your needs.

I realized that right after I sent the last post.

I use the .tag property in a verification process as data's entered and forgot that it doesn't attach to the data, must the control. I'm using a y/n field to ignore the record.

I'm using a listbox as an inform query - with a requery on the button click to remove any ignores from being selectable. The one thing I'm not sure about is whether I need to do a save or something before the requery to make sure that the y/n toggle is being seen by the listbox query.

DD

[pbaldy]

Testing should reveal that, but probably. If so:

If Me.Dirty Then Me.Dirty = False

is one way to save the current record on the form.

[dcdimon]

Testing should reveal that, but probably. If so:

If Me.Dirty Then Me.Dirty = False

is one way to save the current record on the form.

I added screen.activeform.requery to the script for the button - after setting my y/n checkbox to y - and it updates the Ignore field and then sets off the listbox requery, removing the record from view. That's what I was looking for.

Thanks for the help.

DD

[pbaldy]

No problemo.