MS Access - Table where all deleted items go to

0
votes

I need a table where all deleted items from my main table go. I found the easiest way to do this is to copy my main table, paste the table and change the name. In my main table I went to the design tab, Create Data Macros, after delete. In the set field I put the name of the field, and for the value I put [Old].[ID] etc. My problem is this works great until I try to add in my "comments field" to the list. My comments field data type is "Long Text".

Somethings I have tried: deleting all calculated fields and adding them back in as short text (worked for some fields), Deleted all default values, deleted all validation rules.

Expected result would be for my deleted record from my main table to show up into the deleted records table. I receive no error message when deleting a record. It simply doesn't add to the to the deleted table. Thanks for looking.

1
ms-accessms-access-2013
do you have a backend database like sql server? Are you proficient with VBA? - Doug Coats
My data base is split between backend and front end. I am able to use coding. Coming up with it on my own is fair. Thanks - Matt Lane
Deleting records should be a rare event. Why not just have a field to flag record as 'inactive'. This can be a yes/no or a date/time. - June7
Please share the macro AXL. You do this by going to the macro, then selecting all, and then pasting it to a text editor. Also, see if there are any errors in your USysApplicationLog table. - Erik A
Thanks. I like this idea. I will create an inactive design. This way users can bring back the record - Matt Lane

1 Answers

2
votes

Why copy tables? Create a yes/no field On your table called "Deleted." Then you can run a query for or against that field depending on if you want to see the deleted data or not. Add the field to existing queries that shouldn't show these, and set its criteria to false.