Delete Query with Cancel Option

[bballhermit]

I have several forms in my system that manage such things as employees, projects, contracts, etc. In each of these management forms, I want to have a delete query. However, I want to, instead of directly deleting a record, maybe make a backup copy in a temporary table. When the user leaves the form, therefore, I want them to have the option to "close and save" or "cancel and revert back." Is the best way to do this to make a copy of the deleted record(s) in a temporary table, which can then be restored if the changes are canceled? Then, if the changes are saved, delete the temporary table holding the deleted records? If this is the best way to do it, could somebody explain how or point me to a resource? If not the best way, please explain a better way? I'm new to Access. Thanks.

 

[the_net_2.0]

that is one way, or maybe a better way would be to execute the sql statement in code, after a conditional is issued to the user.

that way, you have more control over it, and it's structured in terms of stability and sustainability.

 

[bballhermit]

Could you describe that method more, or point me to a tutorial or reference on it? I'm really pretty new to Access... So, somehow just store temporarily the IDs of the records to delete in a queue, and if the user selects "save and close," then run the delete query, else clear the queue? Thanks.

 

[gemma-the-husky]

the best way - is don't delete

just set a "hide" flag - and filter out the hidden records in normal use.

that's the general way to do stuff - you don't want to delete old records you spent ages creating. for one thing it stops you being able to research old stuff.

 

[the_net_2.0]

there is no such thing as a "que" in Access. Dave and/or Gemma is right though. For any business, records should always be kept, typically for audit trail purposes, if nothing else.

But if that doesn't apply to you, which for some people it doesn't, use:

currentdb.execute "sql statement here"

in vba code to run this sort of thing.

and as far as what you reference as a "que", what you really should do with something like that is give the user a listbox or something to display the names, id's, or whatever you want. There is no ''que'' option, so you'll have to figure out another way.

The listbox would work fine if you had, say, a multi select listbox and allowed the user to select the record id's for deletion. then one of two buttons to be clicked...OK or CANCEL. If OK, loop the listbox selections and run the sql once per selection.

that is not iron clad, in terms of perfection, but it was meant to give you some idea as to how to go about doing something like this.

 

[bballhermit]

hmm, ok, could you expand more on the hide functionality for me? does the hide apply to a whole record? does that then trickle down through relationships in other tables? say i have a project that is related to a specific contract. if i hide the contract, will the project be hidden too? could you point me to a good tutorial or resource on how to use this feature? thanks.