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.
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.