Today I had fun with Access events and closing the database with unfinished changes to a form that needed to stay open and prevent Access from closing.
The problem was that records were able to get to an invalid state by:
- Users re-opening a form to edit a new record. This would not let the routines complete and just overwrote the form with the newly selected record.
- Users closing the database either using a button or the system Access close button in the upper right corner of the main Access window.
I solved problem 1 by setting up the record selection forms to check if the form was already open. If so it tried to close it (which would trigger the validation routines and prevent the closure if it wasn’t valid). If the form was not valid, it would cancel the close routine and trigger an error in the record selection form code. I checked for this error and told the user to correct the form before choosing a new record and set focus to the form so they could do so.
Problem 2 was solved in a similar way. The application had a main exit button located on the main navigation form. I set up similar code in this button event to make sure the “order” form could be closed and if not (indicated by an error) I would cancel the application close sequence telling them to correct the form first.
The second part of problem 2 was to make sure that the system would not shut down even if the user elected to close the database by clicking on the window’s close button. To get this to work, I had to make sure that I added similar code to the other solutions into the main navigation form unload feature.
It does turn out that the window’s close button will close forms in different order depending on what’s currently open. In my case, if the navigation form was shutdown, it would force all other forms to close regardless which was causing problems with creating invalid order records, or rather, allowing them to be closed while they were still invalid.
Ultimately, my fixes will help, but there can still be situations where the form is closed with invalid records. This could happen if there is a hard crash of the computer or of Access, Or the user could kill the Access process using the task manager. So there is still a possiblity of corrupted records I will have to deal with in the future, but with my logging, I should be able to show the customer that the corrupted records are due to crashes rather than people navigating in the system in a way that they shouldn’t be able to do.