←Older revision | Newer revision→
Form Event Quirks
Saving a Bound Form's Record
Here's a simple one. Save the bound form's record. There's two ways it can be done, each one line of code. One uses the Dirty property, the other uses the DoCmd.RunCommand action:
If Me.Dirty Then Me.Dirty = False
This works great, until you Cancel the BeforeUpdate event. Then you get errors.
In the case of setting the Dirty property to False, you get thrown back error 2101: "The setting you entered isn't valid for this property". This is because you're still attempting to force Dirty to False, even though the BeforeUpdate is saying no, I'm not allowing it.
In the case of DoCmd.RunCommand, essentially it's the same thing, but instead of error 2101, you get error 2501: "The DoCmd action was cancelled".
The workaround is fairly easy - a matter of trapping the returned error:
Public Function SaveBoundFormRecord(frm As Form) As Boolean
If Not frm.Dirty Then
SaveBoundFormRecord = True
On Error Resume Next
frm.Dirty = False
SaveBoundFormRecord = Not CBool(Err.Number)
Catching the Form Close
Here's a fun one. The objective here is to capture and cancel a form closure irregardless of the method used to close the form (including the close button in the control box, or of the application itself).
Often times developers will opt to disable or remove the close button from a form, usually due to the fact that there's not an extreme amount of control when the user clicks the button, whereas if they provide a custom close command button, you can start validating the close long before the form starts doing anything of it's own accord.
The real problem with a form closing isn't so much cancelling the closure of the form itself, but rather handling the record save if it happens to be dirty when the user clicks that troublesome X in the top right corner of the form. The closing of the form can be cancelled in the Form_Unload() event. Cancelling the unload event actaully re-initializes the form, as by the time it gets there some unloading work has already been done, and we have no way to stop it.
The documentation tells us that the event sequence for a form closure will go like this:
That's a safe bet. However, there's a little more to it when the form is Dirty. By the time the Unload event gets around to firing, the BeforeUpdate event has already run. Therefore, if we have a dirty record, the event sequence may resemble this:
This is reasonable enough, but there's a slight problem in the fact that you have no control over stopping the Unload event from the BeforeUpdate event, should it be required. If the BeforeUpdate event is cancelled due to some validation error (or for any other reason), this does not halt the sequence of events. In this case, Access gives us an error somewhere between the BeforeUpdate event and the Unload event - this error is not one that's trappable with a conventional error handler in a VBA procedure. Furthermore, we don't have the convenience of being able to tell why the BeforeUpdate event is running. There's no way to discern that it may have been triggered from a closure. At best, we can trap the error 2169 that is raised somewhere between the BeforeUpdate event and the Unload event. Now our event sequence for a Cancelled BeforeUpdate event on a dirty for closure looks like this:
There isn't a ton we can accomplish in the Error event of the form: the sequence cannot be cancelled from here, but there is two things we can accomplish. The first is to get rid of that pesky Access Error dialog saying that the form cannot save the record at this time. The second is that we can set some module level flag that the Unload event can read and act upon. Using the sequence of events, we can then notify the user that they're attempting to close the form, but the record was not able to be saved, and prompt them to continue closing the form or not.
Option Compare Database
Private m_bCantSaveOnClose As Boolean
Private Sub Form_BeforeUpdate(Cancel As Integer)
'some validation check
Cancel = True
Private Sub Form_Error(DataErr As Integer, Response As Integer)
If DataErr = 2169 Then
Response = 0
m_bCantsaveOnClose = True
Private Sub Form_Unload(Cancel As Integer)
If m_bCantSaveOnClose Then
If Msgbox("Can't save record - continue to close?", vbOkCancel) = vbCancel Then
Cancel = True
This is pretty close, but it's not quite 100% flawless yet: regardless of anything we can do, the form's state will either be saved or reverted back to it's last saved state. We are not able to maintain the dirty state of the form without some seriously cumbersome code workarounds. Even though we may opt to cancel the BeforeUpdate, which generally leaves us with a dirty form, by the time the Unload event comes around all the unsaved changes have been reverted and there is nothing we can do to stop it. Part of the unload operations that takes place before we are able to interact with the Unload event is recordset processing. Part of the Unload Cancel's "re-initialization" is placing the cursor of the recordset at the record where it was before the form began unloading. Even if there were a Redo command available to us, by this time in the sequence it would probably not do much good.
As for those seriously cumbersome code workarounds, it's not impossible to track the changes on every control and store them in module level variables, thus providing a way to replace those values to the their respecive controls after the Unload event is Cancelled. However, this is not exactly a straightforward task either... see the On Dirty heading of this page for more information regarding that.
On Dirty... It's Not Very Clean
The OnDirty event is supposed to trigger as the form's data is dirtied. A common use for this would be to toggle a save or undo button's enabled property, showing the Save button when the form is dirty, and disabling it if the form is not dirty.
In theory this sounds nice, but in practice there's a slight drawback: any controls that are updated programmatically do not trigger the Dirty event. This makes it rather difficult to use for the above mentioned purpose(s), and in fact very difficult to rely on in general. Without explicitly calling the event directly after any bound control value is made from code (this is not a practical approach), there's not a lot to be done about it.
One may think that if we were to use the AfterUpdate() event of controls to call the Dirty event specifically, any control that was changed from it's previous value would force a trigger of the Dirty event. Alas, this is not the case, as the AfterUpdate event of controls shows the same behavior as the Dirty event of a bound form: it does not fire when control values are changed programmatically.
There is another workaround aside from calling the dirty event from every place in your code after you change the value, but whether it's worth it or not is up to the developer. A control's BeforeUpdate event will fire regardless of whether it was changed through the UI or programmatically. This gives us a reliable hook to tell when a control is changed (the only one, in fact: the Change event of a control is not triggered by programmatic changes either). We can use the BeforeUpdate, after a fashion, to find out if the bound form was dirtied. In theory this can be accomplished simply enough by checking the value of Cancel directly before the event procedure exits (after any cancellations would have been made). If Cancel = 0 and the control is bound, the form is going to be dirty.
However, this is not a problem-free solution either. Examining the BeforeUpdate event in such a fashion will tell us whether the form is really going to be dirty or not, but what we cannot find out from here is whether the BeforeUpdate event was driven from the UI (in which case the Dirty event will fire on it's own), or from your code (in which case the Dirty event will not fire at all). Luckily, this problem is one that we can either implement a longer workaround to, or make a calculated risk decision.
The calculated risk decision is to opt to call the Dirty event directly from the end of the BeforeUpdate event, if the update was not cancelled. The risk is that the Dirty event will now be called twice for every time a bound control is updated using the UI. This may or may not be a problem, depending on the code you wish to run from the Dirty event, but certainly it is something that we must be aware of.
The longer workaround, which takes care of the problem above, is to completely disregard the Form's OnDirty event, and create your own. Given the problems and half-solutions mentioned above, this may be the best method (we can probably safely disregard the fact that the Dirty event gives us a Cancel argument: this should be handled in the BeforeUpdate event of any required control rather than the Dirty event).
The solution itself is simple enough: create a procedure, call that procedure from the end of every BeforeUpdate control event that has not been cancelled, and there you have it: a mock Dirty "event" that works all the time.
Of course, all these solutions require that you have BeforeUpdate code for every single bound control on your form. This isn't necessarily a problem, but it could certainly be a hindrance. Form modules will tend to get rather cluttered in such a fashion. Alternatively, with a bit of fancy class work, we can handle every bound control's BeforeUpdate event with only a few lines of code in the Form's module, and relatively few lines of code in a separate object - but we should ask ourselves if all the work is worth it so that we can enable and disable a save button or undo button...