I am trying to control exiting from Access. If a form is open, and there is
information in the fields, a normal close will bring up error 2169, which is: if
you close you will lose any usaved information, do you want to close? Yes/ No,
with No sending you back to the form with no changes, and Yes blanking the form
and closing Access. I want to do the same thing, only I want to control the
error message. If I capture the error in the Error event for the form, I can
replace the default message, but then I cannot give a choice to go back to the
form without changes. Access continues on to the unload event. I can stop the
exiting here, but by the time it gets to this event, all the data in my form has
What I need is some way to stop the data from being toasted after the Error event, or someway to stop the Error event and go back to the form before the data is lost.
Has anybody solved this problem?
Enclosed is a sample code solution for single form case ( for form/subform it should be more complex) - it seems too tricky to be more than "academic" MS Access 97 investigation - IMHO you'd better use MS Access 97 standard functionality and wait for the next realease in which hopefully MS solves this problem...
As far as I can see the key of the problem is that Form_Error is called even if you cancel Before_Update event when you click close (x) buttoncino in the right top corner of the form or MS Access window or select Close entry of form's system menu...
BTW, you can also hide system menu and buttons, form title, hook and suppress Ctrl+F4 and Alt+F4, hide close (x) button of MS Access itself (using API I guess) and draw custom close (x) button on the form - but again this seems too tricky to be true solution...
Probably I don't see simple and effective solution - if you or somebody here knows or finds it - please send it me - it would be accepted with a lot of thanks....
P.S. CanClose flag idea is "stolen" from David Hare-Scott's message posted here 18/6/1998 but used opposite way...
P.P.S. The code (behind form):
Private mblnCanClose As Boolean Private mcolCtlsValues As Collection Private Sub Form_Open(Cancel As Integer) mblnCanClose = True End Sub Private Sub Form_BeforeUpdate(Cancel As Integer) 'Debug.Print "BeforeUpdate" ' put your data validation code here 'If DataArentValid(me) Then ' Cancel = True 'End If End Sub Private Sub Form_Error(DataErr As Integer, Response As Integer) 'Debug.Print DataErr If DataErr = 2169 And mblnCanClose = True Then If MsgBox("Would you like to discard edited data and close form ?", _ vbQuestion + vbYesNo) = vbYes Then mblnCanClose = True Response = acDataErrContinue Else mblnCanClose = False Response = acDataErrContinue ' Get texbox control values to restore them in ' Unload event Dim ctl As Control Set mcolCtlsValues = New Collection For Each ctl In Me.Controls Select Case TypeName(ctl) Case "TextBox": mcolCtlsValues.Add ctl.Value, ctl.Name Case Else End Select Next End If End If End Sub Private Sub Form_Unload(Cancel As Integer) If mblnCanClose = False Then mblnCanClose = True Cancel = True If Not mcolCtlsValues Is Nothing Then Cancel = True Dim ctl As Control ' Restore textboxs' control values For Each ctl In Me.Controls ' On error resume next is used to suppress field ' validation errors while restoring their values On Error Resume Next Select Case TypeName(ctl) Case "TextBox": ctl.Value = mcolCtlsValues(ctl.Name) Case Else End Select Err.Clear Next Set mcolCtlsValues = Nothing End If 'MsgBox "You cannot close the dirty form this way" End If End Sub Private Sub cmdClose_Click() If Me.Dirty Then MsgBox "Save edited data before closing form", vbExclamation Else mblnCanClose = True DoCmd.Close End If End Sub
Copyright © 1999-2008 by Shamil Salakhetdinov. All rights reserved.