Cleanly Preventing Form Closure on a Dirty Record

[Steve101]

Well this one has got me totally baffled, so I'm hoping someone has an easy answer.

Here's the general scenario: A simple form frmForm1 is bound to a table tblTable1. The user makes the current record dirty by editing one of the bound controls on the form. He she then clicks on the form's Close Window button or the Access Close button.

Here's what I WANT to happen: Because the record is dirty, I want to prevent closure of the form (and of course the application). Now I know that I can make the form modal, and optionally hide the control buttons, and take control of the Alt F4 keys and so on, but WHY wont the following code to the whole job:

Dim AllowFormExit

Private Sub Form_Error(DataErr As Integer, Response As Integer)
Response = acDataErrContinue
End Sub

Private Sub Form_Unload(Cancel As Integer)
If Not AllowFormExit Then
Cancel = True
End If
End Sub

Private Sub btnClose_Click()
AllowFormExit = True
DoCmd.Close
End Sub


Notes:
(a) The AllowFormExit variable just allows me to close the form. Otherwise, the Form_UnLoad event prevents any closure of the form (as is my intent in this example).

(b) The Cancel in the BeforeUpdate event SHOULD prevent any record from being changed.

(c) The "Response = acDataErrContinue" statement in the form_error event prevents the following error from being displayed: "You can't save this record at this time. Microsoft Access may have encountered an error while trying to save a record. If you close this object now, the data changes you made will be lost. Do you want to change the database object anyway? (yes) (no).

(d) If I comment out the above statement, then the above message is displayed when I press the Close Window button on the form. Responding No, leaves the record dirty, which is my intended result.

(e) If I leave the "Response = acDataErrContinue" statement in the Form_Error event, then the message is suppressed, the window is left open, but the CURRENT RECORD IS SAVED (ie. seemingly taking the course of the (Yes) option associated with the above message.

Now here's the big question; Is their any way in which I can get Access to take the No option (ie. leave the record dirty) without the prompt I dont seem to be able to intercept this message in any way; even if I cancel the beforeupdate event.

I've probably made a hash of trying to explain this problem, but if anyone has any ideas on effectively getting Access to TOTALLY IGNORE any clicks on the Form or Windows Close buttons, I'd really like to hear them (or is suppression of these buttons the only solution?)

Many thanks,
Steve

 

[thornmastr]

Steve,

I used your sample to test with. No matter what I tried, I could not carry the dirty characteristic forward, the end result was identical to doing a me.undo. However, consider the well crafted typical Access form. When you close the form, what does it do. It checks to see if the form is dirty. If it is, it tries to update the underlying record. It checks for a before update event and activates it. For whatever reason, the event is cancelled. Now we are in the unload event. It sees a dirty record, and because of what you have done it knows you are not going to finish the form closing process.It offers you a choice of what to do. The message is misleading and inaccurate, but in essence it is asking you how you want to handle this. If you respond no, it leaves you dirty and if not, before it does anything else, it requeries the form which resets you. While I don’t like not being able to trap and ignore the error, I understand the rationale behind it.

I don’t like it. But I can’t find a way around it. Anyone else???

Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com

 

[Steve101]

Thanks for the effort Robert. Confirmed what I thought. I'm a bit surprised that the cancel on the UnLoad event does not prevent further triggering of the rest of the activity associated with closing down the window. I would have expected that it would, and then we would'nt have this problem.

Well, you and I know and understand these mechanics, but unfortunately an average user shouldnt need to be exposed to such things, and whilst the chance is quite small, he/she might decide to close down a dirty form whilst going for a cup of coffee or whatever.

Anyway, would be nice to see if anyone else has something to offer on this front.

Another 'nice to have' would be some more control on a form's "OnError" event. It seems like you can control the display of the error message, but not the actual handling of the error itself. This is a pity.

Anyway, be well
Regards,
Steve

 

[thornmastr]

According to Microsoft, if we need this type of control, the correct way to get it is to set the close button form property to no and then to create both a close button, a save button, and an undo button and to exit immediately out of the close button event if the form is dirty.

Personally, you already know my views about save buttons so you know how enthusiastic I am about the MS cure all.
Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com

 

[Steve101]

As I understand it (recalling a past thread, and having tried it at the time), even if you suppress the Close button on the form, if you maximise the form, then it re-appears; at any rate, even if you suppressed it, then probably you'd still have to deal with the keyboard equivalents. One way or another you end up doing crude software gymnastics all because you can't cancel the close /unload window event properly; seems a pretty basic problem to me.
Ah well, such is life.

 

[krsherm]

Steve, maybe I'm missing something here but being Dirty is like being pregnant - you're either dirty or not - regardless of the validity of the data entered by the end user. If you want to deny someone the opportunity to close a form that has been dirtied, you're going to have some very upset users... (assuming that the purpose of this form is to update data). If you don't want the data on the form to be edited, then use the properties availabe (allowedits, etc)...

If I'm off track, I apologize, but I don't understand why you would provide an form to a consumer with editable data, and then deny the closing of the form if data was edited. Am I missing something?

Rock ON!

Kevin

 

[SgtJarrow]

Steve,

Unless I am misunderstanding what it is you want to do....I think the below should work for you....it is a set of code and property changes to "control" the closing of a form....and I modified the bolded section to check for a dirty....maybe you can do something with it....

=======================================================

1. Set the CloseButton propert of the form to No. You can alternately set the ControlBox property to No, which removes both the 'x' and the min max buttons.

2. In the declaration's area of the form's VBA code, define the followinf variable:

Public OK2Close As Boolean

3. In the form's OnLoad event, place the following code:

OK2Close = False

4. On your form should be a button to close the form (or Quit the application if on the Main screen). In the OnClick event for this button, place:

If Me.Dirty = True Then
Exit Sub
End If
OK2Close = True
DoCmd.Close '(or DoCmd.Quit for Main screen)

5. In the form's Unload event, place:

Cancel = Not OK2Close
It's not important that someone else can do in one step what it took you ten to do...the important thing is that you found a solution.

Robert L. Johnson III, A+, Network+, MCP
Access Developer/Programmer

 

[Junior1544]

i've had a similare situation... but i already had a close button made, so i modified the data there... i had certain numbers that could be edited on the form in unbound text box's, i needed them saved in a different table, and it's unrelated so i used a simple recordset to add the number value's into this other table befor saving, and the close either closes if the form is clean, or askes if the person wants to cancel any changes if the form is dirty...

the only solution i've been able to come up with for your problem is to go into the properties for the form, disable the record selectors, the close button, and maybe some thing else... then making my own buttons for what i needed(gladly, i don't need the record selectors)... so i just made a save record button, an undo changes button, and a close button...

--James junior1544@jmjpc.net
Life is change. To deny change is to deny life.

 

[thornmastr]

That’s not what Steve or I want to do. What we are trying to accomplish is to insure that if a form is closed with an underlying dirty record, if the unload event fires and is then cancelled, the form and the underlying record remains in the dirty state. It does not; it reverts back to the clean state if you subvert the silly message you get from MS.

There is simply no clean way to do this.
Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com

 

[Steve101]

Thanks guys for all of your input. As Robert says, there's no simple way to simply get Access to ignore the click on the Close button.

Kevin, in terms of letting a User save any data he enters, well in my book, thats an absolute "no no". The "business rules" should govern the quality and integrity of the data that a system allows to be entered. These rules are typically built into an Access database via the "before update" events of the controls and/or form, and if they are not satisfied, then the save of the record is rejected with the appropriate diagnostic(s) presented to the user. At this point the user can elect to (a) fix the entries, or (b) cancel his changes to return the record to its previous state. Attempting to close a form with a dirty AND invalid record should be treated exactly the same way.

The other replies (James, Robert L) are all on the mark, but still dont fully set the trap. For example, even if the Close button (or other buttons) are never exposed, the Alt F4 keyboard equivalent is still available to close the application - and it does - and I'm not sure that it can be trapped by Autokeys (I get an "Illegal combination" type message; so - the plot thickens.

Cheers,
Steve

 

[SgtJarrow]

Steve,

I am now understanding what it is you are trying to do.....unfortunately, I can't help you all. After rereading the post, it seems you have a good grasp of the problem and I don't see any other solution myself.

As a brief aside, the procedure I post above with prevent ANY closing except by the method you provide....in other words, the little 'x', Alt+F4, or any other method you may have declared through the AutoKeys...You simply cannot close the form whatsoever....Heck, once I enable this method, I can't even switch from form view to design view on a form with this set up.... It's not important that someone else can do in one step what it took you ten to do...the important thing is that you found a solution.

Robert L. Johnson III, A+, Network+, MCP
Access Developer/Programmer

 

[Steve101]

Thanks for that Robert. As you say, you can control closure of the form the way you suggest; unfortunately however, there does'nt seem to be a way to prevent a pending save from happening when you attempt to exit the form or application. Injecting a BeforeUpdate event just brings up the horrible little message "You can't save this record at this time."; there does'nt seem to be a way to prevent or intercept this. the only avenue which I hav'nt really investigated is API calls.
Cheers,
Steve

 

[joshs1100]

I think I have found an answer to the problem here that seems to be working for me.

In the before update event, if any of my required fields are null, I first do a docmd.cancelevent to cancel the odbc error that pops up (not sure if you would have this if you don't use odbc linked tables, I get the error "ODBC--call failed.).

Then, in the form error, I have this code.

if dataerr = 2169 then sendkeys "%N", false

this selects the "No" option on the
""You can't save this record at this time. Microsoft Access may have encountered an error while trying to save a record. If you close this object now, the data changes you made will be lost. Do you want to change the database object anyway?"
Dialog box.