Full Version: delete records on form close
UtterAccess Forums > MicrosoftŪ Access > Access Forms
forbrichsj
Is there a way to have a macro delete all records that were created when a form is closed?
freakazeud
Hi,
why would you want to do that?
You can certainly execute a delete SQL statement or stored query e.g.:
Delete * From YourTable"
But I'm not sure why you would want to do that. Do you want to control the storage of the information in the underlying source table? You might want to use the Undo method instead to roll back the record.
HTH
Good luck
forbrichsj
I am actually using a one record table as a place to store a number variable that the user will select depending on the last time they ran the report. Its 99 percent effective. I removed all navigation features from the page. One week for an unknown reason at the time I was getting duplicate entries in my reports and could not figure out why, considering I did nothing to the report that woudl cause such a thing to happen. Well in the most unlikely place that variable table had 2 records in it. I have no idea how it snuck in but it did. So everytime i close my program I want it to wipe all the values out so if that problem ever happens to users they will only have to restart the database and or this will completely solve the problem
freakazeud
Ok...sounds like a plan. You could use a Dcount() expression in the before update event of the form to count how many records the source holds and cancel the event if it is too many. But if you want to clear out the whole table you can do that, too.
urrentDb.Execute "Delete * From YourTable", dbFailOnError
HTH
Good luck
forbrichsj
This might sound dumb but where do I put that line of code?
On an event?
freakazeud
In an event procedure...open the form in design view and open its property dialog (double left click on the little black square in the top/left corner). Go to the event tab and find an appropriate event e.g. the unload or on close event. Select [event procedure] from the drop down and press the "..." This will open the VBA editor with the preformatted procedure for you. Add the code between the beginning and end.
HTH
Good luck
forbrichsj
wow, thank you so much. this has been such a problem for me. it is working perfectly
freakazeud
You're welcome.
Good luck on future projects!
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.