Cancel Unload Clears Form

[mbentley]

I've set up a form to check for certain data errors, and prevent the user from leaving fhe form if one is encountered. It works fine except that when I cancel the FormUnload event, the database automatically undoes all of the user's (dirty) changes to the record. I've circumvented this by storing the state of the form in multiple variables before the FormUnload event is cancelled and repopulating the form after, but that's cumbersome. Is there another way around this?

 

[Jack Cowley]

You should do your data validation in the Forms Before Update event. There you either return the user to the control with the wrong data or move on if the data is valid. Using this property you should not have the problems you are having now.

hth,
Jack

 

[mbentley]

The data validation is actually done in each field's BeforeUpdate Event. What I am trying to do in the form's Unload Event is to prevent the form from closing if the user enters invalid data in the last field and tries to immediately close the form. No data validation is done in the form's Unload Event.

Ironically, if data validation was done in the Unload Event, the data would actually be saved before the event, and would not be lost when the event was cancelled.

 

[Jack Cowley]

The problem with the controls Before Update event is that you can skip the field and your code will not be triggered. If your code is in the Forms Before Update event you can check all the controls before the form is updated and then save or undo the data as necessary.

hth,
Jack

 

[mbentley]

I realize that. I'm pretty familiar with the event sequence. Without getting into a long story about the logic behind the form, trust me when I say that the BeforeUpdate event of the fields covers the all the possible errors I'm looking for.

I agree that it's generally better to link data validation to the form's BeforeUpdate event, for the reason you state, but I have my reasons for using the fields' BeforeUpdate events in this case. I don't want to get into it here, as it's beyond the scope of this problem. Besides, that shouldn't have an impact on the problem I'm describing.

Thanks for your help. Any other thoughts?

 

[Jack Cowley]

Hmmm. I must be missing something obvious because if I use simple code like this in the forms UnLoad event:

If IsNull(Me.MyField) Then
Cancel = True
Me.MyField.SetFocus
End If

none of the controls on the form are undone if I try to close the form. What am I missing?

Jack

 

[mbentley]

I think the question is, what am I missing?

The code is a little more complicated than that. I'm sure this will all seem overcomplicated, but it has to do with preventing the form from closing only when suspicious data has been entered, but still allowing the user to close the form after a warning, just in case the data is actually correct. Because data integrity is a grey area in this case, I have to allow this. That said, here's how it works:

If the user enters suspicious data, the field's BeforeUpdate event offers the user the choice to save their changes or not. If they choose to save their changes, the event is allowed to proceed and everything unfolds normally. If they choose not to save, then BeforeUpdate is cancelled. A global boolean variable (booStopNavigation) is set to tell subsequent code that spurious data was entered and the user chose to correct it.

If the user was trying to move off the record, either by changing records or closing the form, a 2169 error is triggered (cannot save record). In the form's Error event code, I tell it to bypass the error (Response = acDataErrContinue). If the user was trying to move to another record, this stops them from moving on it's own, which is a happy coincidence. In this event, the dirty fields all retain their current entries.

If they were trying to close the form; however, this does not prevent the close event. So, I then tell the form's Unload event to cancel if booStopNavigation is set to True, which would indicate that the user had chosed to correct the error. After this booStopNavigation is reset to False so that the user can close the form normally if an error isn't triggered. (There is actually a tiny hole in this logic that would cause the user to have to click to close button twice in some very rare cases).

When the code runs through this way, all of the dirty fields on the form are reset. I checked if the form's Query event is firing, and it's not.

Clear as mud?

 

[Jack Cowley]

Yikes! Lots of stuff going on... What about using an unbound form and then when everything is copasetic save the data to the table?

A am much better at solving your type of problem if I can fiddle with the actual code so doing it in my brain is not all that productive. I'm sorry that I don't have a solution for you based on your current code....

Jack

 

[mbentley]

I think that would be more cumbersome than just storing the dirty values in variables and repopulating. Thanks for trying. I'm guessing that it's just one of those little Access glitches. No biggie.

 

[Jack Cowley]

You are surely the expert with your program so go with what works best for you! Good luck with your project and I'm sorry that I did not have a solution for you.

Jack