Confirm Quit

[WorkingVBA]

Hi,

I found this code in the forum to confirm closing the database. For some reason it works fine the first time around if you close MS Access by clicking the X (i.e. the quit is canceled), but if you do it again it goes ahead and closes anyways. Anyone know what is happening?

Thanks for your help

Private Sub Form_Unload(Cancel As Integer)
    Dim varResp As Variant
    ' this sub closes the program while leaving Access open
    On Error GoTo err_handler
    varResp = MsgBox("If you want to EXIT Access completely, press YES. " & vbCrLf & _
                     "If you want to CLOSE the program while leaving Access open, press NO." & vbCrLf & _
                     "If you want to CANCEL and go back to the program, press CANCEL.", vbYesNoCancel, "Close Confirmation")
    Select Case varResp
    Case vbYes
        DoCmd.Quit
    Case vbNo
        CloseCurrentDatabase
    Case vbCancel
        Cancel = True
        Exit Sub
    End Select
    Exit Sub

err_handler:
    If Err.Number = 2763 Then
        Exit Sub
    Else
        If MsgBox(Err.Description & vbCrLf & "Do you wish to continue?", vbCritical + vbYesNo + vbDefaultButton1, "Error number: " & Err.Number) = vbYes Then
            Resume Next
        Else
            Exit Sub

        End If
    End If
End Sub

 

[ct2013]

What i done was just made a new form called frmShutDown and in there put "are you sure you want to quit" and added buttons Continue, Cancel...

Seems to be doing a great job, i am now in the process of disabling the MS Access x button so that users must shut through my dialog!

 

[CJ_London]

Why not just disable the close button (Form>Properties>Format>Close Button) and create your own quit button on the form - you'll finf it under the application category in the button creation wizard

 

[WorkingVBA]

Thanks, It is not the form's close button that is causing the problem, it is the MS Access window's close button that users inadvertently click to close a form, which when they do closes the data base instead. It is this I would like to prevent. So if you are exiting the application (database) in whatever way it just asks, "are you sure you want to exit?" before it actually does.

i am now in the process of disabling the MS Access x button so that users must shut through my dialog!

A viable option, ct2013, but how do you do that?

 

[CJ_London]

Ah Sorry, Well you could always hide the access database window - providing you don't want users to have access to the ribbon and/or sight of the objects

 

[WorkingVBA]

Ah Sorry, Well you could always hide the access database window - providing you don't want users to have access to the ribbon and/or sight of the objects

No I don't want to go that drastic, just a message confirming if you really want to exit (VS accidentally hitting a close button)

I find it hard to believe that this is not some default setting in the database options.

 

[CJ_London]

I don't know of any MS products that do - Excel for example will only prompt if you have made some unsaved changes, so will Access if you have made changes to a record but not saved them - perhaps that is another option for you. Have a simple table, open in a hidden form, make some changes but don't save them - but you'll probably end up with a message you don't want.

 

[spikepl]

Be specific. What is the answer supplied after the first click on X .. and 2nd ?

 

[WorkingVBA]

Be specific. What is the answer supplied after the first click on X .. and 2nd ?

Spike thanks for joining the conversation.

The first time you close MS Access by clicking the MS Access window X (Close) you get the right response:

    varResp = MsgBox("If you want to EXIT Access completely, press YES. " & vbCrLf & _
                     "If you want to CLOSE the program while leaving Access open, press NO." & vbCrLf & _
                     "If you want to CANCEL and go back to the program, press CANCEL.", vbYesNoCancel, "Close Confirmation")

When you click CANCEL it goes back to the form (so far, so good). If you click it a 2nd time it just exists and bypasses the forms "On_Unload" event. Complete code is at the top of the post.

 

[David R]

Have you tried setting a breakpoint and stepping through to see why the second one isn't checking again?

 

[spikepl]

Are you sure? I just plugged your routine into a DB and it works just fine - after clicking on X and then Cancel everything remainsd as before. Repeatedly.

 

[WorkingVBA]

Are you sure? I just plugged your routine into a DB and it works just fine - after clicking on X and then Cancel everything remainsd as before. Repeatedly.

Thanks for checking Spike,

And yes I am sure, other wise I would not have posted this
I think, based on your findings, that there is something else going on. I will check and see if I can figure it out. I will post any updates.

 

[Adam McReynolds]

When I use

DoCmd.Quit (acQuitSaveAll)

it closes the database and Access at the same time, if I use

DoCmd.CloseDatabase

it would close the database but leave access open. I would think you could use a simple Yes/No MsgBox to give a warning before closing. Not sure if this helps at all but just giving my 2 cents since people always help me on here.

 

[WorkingVBA]

Adam, thanks to you as well for joining in.

I figured out part of the problem:

First I was using a DoCmd.quit on the form's Exit button. Cancel = True does not stop that.

Second, CloseCurrentDatabase (when you click no to close the form and leave the database open) acted strange. I did not know it was a DoCmd method which might explain its erratic behavior. Instead of worrying about closing the form and leaving the database open I am only focusing on two conditions: if the user confirms the exit (i.e. vbYes) or if they don't, in which case we continue closing the form and database. I will post my final code, though I am leaving here in 30 minutes.

have a great weekend all, and I'll be seeing you on Monday.

 

[WorkingVBA]

OK,

Just a quick update. This seems to have taken care of the problem and it works like a charm. If I need to explain what is going on, drop me a response but I've got to go now. Have a great weekend.

Private Sub btnExit_Click()
    ' Exit button will reset the button and close the form
    On Error GoTo btnExit_Click_Err
    ' Reset toggle button after clicking
    btnExit = False
    ' Close the form
    DoCmd.Close
    
btnExit_Click_Exit:
    Exit Sub

btnExit_Click_Err:
    ' Call ProcessError (Module: mdlUtilCode)
    If Err.Number <> 2501 Then 'Close form was canceled
        MsgBox Err.Number & ": " & Err.Description, vbCritical
        Debug.Print "SubRoutine: btnExit_Click) (frmRebalMaster) " & Err.Description
    End If
    Resume btnExit_Click_Exit
    
End Sub

'-------------------------------------------------------------
Private Sub Form_Unload(Cancel As Integer)
    Dim vMsg As String
    'Dim varResp As Variant
    ' this sub closes the program while leaving Access open
    On Error GoTo err_handler
    
    vMsg = "Are you sure you wish to EXIT and close the application?"
    vMsg = MsgBox(vMsg, vbExclamation + vbYesNo)
    ' If user responds Yes
    If vMsg = vbNo Then
        Cancel = True
    Else
        DoCmd.Quit
    End If
    
    Exit Sub

err_handler:
    If Err.Number = 2763 Or Err.Number = 2501 Then
        Exit Sub
    Else
        If MsgBox(Err.Description & vbCrLf & "Do you wish to continue?", vbCritical + vbYesNo + vbDefaultButton1, "Error number: " & Err.Number) = vbYes Then
            Resume Next
        Else
            Exit Sub

        End If
    End If
End Sub