Controlling exiting of Access

Question

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 disappeared.

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?

Answer

Shawn,

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....

HTH,
Shamil

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