How to control exit from the Access application

[Petrol]

My DBMS has a main switchboard which is opened by autoexec and remains open for the life of the session; it contains an "Exit Database" button which performs some housekeeping such as logging the event, before executing the Application.Quit.

The problem is that some users are closing the application by using the "X" close button on the application title bar, thus bypassing the closing housekeeping. So I have tried to inhibit that by setting a global flag in the cmdDatabaseExit OnClick procedure and checking this flag in the Switchboard.Unload procedure. If the flag isn't set, I want to display a message and cancel the Unload action. However I am thwarted by the fact that Access closes the switchboard form when I want to switch to design view, so my Unload procedure prevents this. I have tried numerous ways around this, using switches to handle the re-entry to the procedure etc, but nothing works. Am I trying to do the impossible, or is there a better way to solve the problem?

[NeoPa]

Hi Petrol.

There are many ways to achieve this in Access but fundamentally they come down to setting the flag to allow closing when you need to switch into Design mode & don't want the database to close down completely. Alternatively, you may want a separate flag set that allows switching explicitly. I'm going to assume that you don't want your users being able to take advantage of this.

So, in the absence of any code posted I'll assume your setup is very similar to what I will show, but with some minor, but important, changes.

Let's start with the dimensioning code in one of your standard modules :

Public blnAllowClose As Boolean
Public blnAllowDesign As Boolean

I assume you already have the first, or similar, but the second is an addition.

In your switchboard code you'll need something like :

Private Sub Form_Unload(Cancel As Integer)
    If Not blnAllowClose _
    And Not blnAllowClose Then
        Cancel = True
        Exit Sub
    End If
End Sub

Clearly you already have code behind your "Exit Database" button that handles setting blnAllowClose (or your equivalent flag) so we're just left now with the harder bit of managing to set blnAllowDesign in such a way that only you have access to it.

One reliable way in most circumstances is to use the UserName or Account ID as returned by the operating system. You can see more on this at Function to Return UserName (NT Login) of Current User. This would be my recommendation as long as it is reliable for you. One reason it might not be is, say, if a family member were using the database using your Account ID. In most cases though this works well. In such a case blnAllowDesign would be unnecessary and the earlier code would become :

Private Sub Form_Unload(Cancel As Integer)
    If Not blnAllowClose _
    And (sys_OrigUserID() <> "{YourAccountID}") Then
        Cancel = True
        Exit Sub
    End If
End Sub

If this doesn't work for you then an alternative is to define a place on your switchboard that is known only to you and capture an Event based on that Control or area of the Form. For instance, say you had a Control on your Form with foreground = background, so essentially invisible, and you captured the Double-Click Event on that Control you may have some code such as below to handle silently switching between allowing Design Mode and disallowing it :

Private Sub lblInvisible_DblClick(Cancel As Integer)
    blnAllowDesign = (Not blnAllowDesign)
End Sub

This is based on the principle that only you would know to do that so all others would be blocked from switching to design mode.

[jimatqsi]

When the close button in the titlebar is clicked, any open forms are closed before the application exits. You should be able to solve this problem by simply adding some code to the OnClose event of the switchboard form. Run the same code that gets run when the "Exit Database" button is clicked.

[NeoPa]

Hi Jim.

If you check his OP again you might notice he's already using that approach. It's how to disallow closing from the 'X' as well as allowing him, as a developer, to get into design mode that he's looking for help with now.

Hopefully my earlier post explains exactly how to do that.

-Ade.

[Petrol]

Thank you, NeoPa, that would work, provided the shutdown is handled correctly. I'll explain the remaining problem and solution (which I'm sure you will have already thought of) for anyone else coming this way:

Actually I already had something equivalent to what you suggest, except that I used a multi-valued global variable which was set to 1 by the "EnableDesignView" procedure and 2 by the ExitDatabase proc. The code in my Switchboard.Unload procedure was something equivalent to

Private Sub Form_Unload(Cancel As Integer)
'
'   Procedure to inhibit unload/close of the switchboard unless DatabaseExit or EnableDesignView buttons clicked
'
Select Case gblSwitchboardUnloadOption
Case 0     ' No buttons have been pressed, so it must be that the user is closing the application
    MsgBox "To ensure an orderly shutdown, please use the 'Exit Database' button to close the applcation.", , "                        No !"
    Cancel = True
Case 1     ' "Design View" button pressed
           ' Do nothing, and allow the switchboard to close
Case 2     ' "Exit Database" button pressed
           ' Do nothing, and allow the switchboard to close   
End Select
 
End Sub

The problem is that I then had to implement the application exit, so I had the Application.Quit command just before the end of the ExitDatabase event procedure. Unfortunately Access seems to empty the globals before closing the form, so by the time the Switchboard.Unload procedure was executed the multi-valued variable returned zero. The application shut down all right, but in the process the Unload procedure produced the warning message, which was displayed on the empty screen after the Access window disappeared!

I now have the Application.Quit statement in Case 2 of the above code. I still have a somewhat similar problem, in that I was trying to close the Switchboard form and reopen it in Case 1 of the above code (hence the comment in my OP about handling re-entry). However Access won't let me do that in the Unload procedure, so I have to be content with just clicking the DesignView button and then switching to design view on the tab as usual.

By the way, apologies for the slow reply. I used to receive emails when someone replied to my posts, but this no longer happens, so I was a bit slow realising you had responded. Thanks again.

[NeoPa]

Hi Petrol.

I'm confused as to why you'd want to run Application.Quit separately? Surely the Application.Quit would be what triggers the Form_Unload() Event procedure of the switchboard? That or trying to switch into Design Mode as a designer.

I can assure you that my systems don't suffer from any such undesirable side-effects as you've described.

By the way, your code should either determine the situation and only allow based on the correct value being set for gblSwitchboardUnloadOption - or if determining isn't necessary or possible - then you don't really need two different settings but a simple Boolean value will do.

[Petrol]

Hi NeoPa. You said "I'm confused as to why you'd want to run Application.Quit separately? Surely the Application.Quit would be what triggers the Form_Unload() Event procedure of the switchboard? That or trying to switch into Design Mode as a designer".

Yes, that is precisely what triggers the Form_Unload, and thus allows the form and then the application to be closed.

I think I see where the confusion exists. Are you assuming that I want the "ExitDatabase" button to simply enable the application's Close button on the title bar to work? That is, the user must click my button and then the X on the title bar? That seems clumsy to me. I wanted my button be the one-stop shop - that is, to do the final housekeeping and then honour the user's desire to close the system. That's why I used Application.Quit - initially in the ExitDatabase_Click procedure and more recently in the Switchboard_Unload procedure.

Is there another way to shut the Access application down under program control?

[Petrol]

By the way, how did you get the code segments to appear in that font? If I use bracketed CODE /CODE it puts it in a separate paragraph.

[NeoPa]

Hi Petrol.

Easiest one first - use ICODE (See FAQ - BB Code) ;-)

As to what I was assuming - no. I wasn't assuming that at all. I fundamentally agree with you that the Application.Quit() call should be from within the ExitDatabase_Click() event procedure. What confuses me is why you'd ever want to add it into the Switchboard_Unload() one. If that's written properly, and I saw no problems with your posted code, then it should behave as required without trying to run it again. Certainly similar code works perfectly for me in many places.

The only complication should be with switching across to Design Mode. It's hard to know exactly what you want but I would have a flag set somewhere triggered by something only you get access to. That could be an action only you know about or a checking of the AccountID to match yours. Once that's been triggered it should allow closing of the switchboard form, which thus allows you to switch into Design Mode.

[Petrol]

Thank you for your patience and persistence!
As you'll see from my Post #5, the method I was using was essentially the same as you suggested i.e. an ExitDatabase button and a AllowDesignView button - except that I was using a multi-valued flag instead of two booleans. The problem was what I stated just under the block of code in that post, namely, "Unfortunately Access seems to empty the globals before closing the form, so by the time the Switchboard_Unload procedure was executed the multi-valued variable returned zero. The application shut down all right, but in the process the Unload procedure produced the warning message, which was displayed on the empty screen after the Access window disappeared!"

In other words, the flag set to non-zero to allow the form to close was reset to zero when I executed Application.Quit in the ExitDatabase_Click procedure; so although the application quit, in its dying gasp the Unload procedure produced my "please use the Exit Database button" message. That was the reason I had previously moved the Application.Quit to the Unload procedure. I believe your code (second code block of Post #2) would have done the same if it had included the message.
I tested this by switching to the use of a boolean variable set True in the ExitDatabase procedure and debug.printing it on entry to the Switchboard_Unload procedure. It was zero. The ExitDatabase button exited the application, but then the "please use the ExitDatabase button" message was displayed.

For now, I can get around the problem by changing the button from “AllowClose” to "DisallowClose”. I will initialise it to True when the form is opened and reset it in the ExitDatabase and AllowDesignView procedures. I think this is inelegant, but for reasons of expediency I’ll adopt it. The only remaining problem is that now File > Save As doesn’t work, but that’s probably a topic for a fresh post.