Running VBA code when a user closes database using (x)

[James Bowyer]

As in the (x) in the top right hand corner of the screen.

I have a log out and Exit button, which removes users from a list to let me know who is logged in and who isn't, however no matter how many times I ask, people still close the database using the (x), rather than logout.

Is there any way to run the same code when the database closes regardless?

[TheSmileyCoder]

I have a form frm_Startup that opens on startup, and does various actions. One of those actions is to open frm_ControlClose in hidden mode (not visible). The CLOSE event of frm_ControlClose has code attached to it, to logout the user. The idea is that when the user closes using the (x) all forms gets closed by access including the non-visible form.

[James Bowyer]

So if a user closes the database using (x) all forms with a on close stipulation actually run their vba?

I could attach an on close to the main screen, which is always open, and that would then run the logout code if this was the case.

[TheSmileyCoder]

Yup you could do that as well.

I sometimes have the main form close when viewing specific items, so thats why I do it that way with a seperate form.

[NeoPa]

Any form can be designed so that the code required to run when the form is closed is within Private Sub Form_Close(). If you also want and Exit or Logoff command button, then that can simply be added and allowed to Call DoCmd.Close, thus triggering the same code as is triggered by the (x). Does that make sense?

[James Bowyer]

Yep, makes perfect sense.

And it works too!

The only thing is, I currently use an OpenArgs to carry the username through the system, as for example, if I log in on someone's computer it gives me Admin tools on my database. I have got the on close function to work with Environ("USERNAME"), so it will work 99 times out of 100, as its mainly the other users that cause the problem! I tried it with Openargs though, and it didn't work, presumably as this is code that happens when the form is closed, and therefore the form loses all of its information.

Would there be any way of carrying a cell or openargs into this On Close method, so I can log out users that aren't using their own computer?

[NeoPa]

OpenArgs itself is only ever available to the Form_Open() procedure. I often use the following type of code to ensure it is available later on to other procedures of the same form :

Private strUser As String
 
Private Sub Form_Open(Cancel As Integer)
    strUser = Nz(Me.OpenArgs, "")
End Sub

PS. In your situation I'd be tempted to use a global (Public) variable defined in a standard (not associated with any type of object - Form; Report; etc) module. Due to the potential for sessions to get reset on errors though, I'd consider handling the situation where the value is no longer available.

[NeoPa]

A new question (Standard Modules - Public Variables) was asked in here but it has now been moved to its own thread as per the site rules.

[beacon]

Hi James,

My suggestion isn't exactly along the same lines as the other suggestions in the thread, but I've had similar problems with my databases and I decided to just disable the [X] so it would appear grayed out and not let the user select it.

The code isn't my own, but I have tweaked it slightly to include the 'Exit' and 'Close' options under the File menu.

The first step is to create a new module and insert the code below:

Option Compare Database
Option Explicit
 
Private Declare Function GetSystemMenu Lib "user32" (ByVal hWnd As Long, _
    ByVal bRevert As Long) As Long
 
Private Declare Function EnableMenuItem Lib "user32" (ByVal hMenu As _
    Long, ByVal wIDEnableItem As Long, ByVal wEnable As Long) As Long
 
Const MF_GRAYED = &H1&
Const MF_BYCOMMAND = &H0&
Const SC_CLOSE = &HF060&
 
'------------------------------------------------------------
' SetEnabledState
'
' This function calls the CloseButtonState and ExitMenuState
' subroutines included in this module
'------------------------------------------------------------
Public Function SetEnabledState(blnState As Boolean)
    Call CloseButtonState(blnState)
    Call ExitMenuState(blnState)
End Function
 
'------------------------------------------------------------
' ExitMenuState
'
' This subroutine enables/disables the Exit and Close options
' under the File menu
'------------------------------------------------------------
Sub ExitMenuState(blnExitState As Boolean)
    Application.CommandBars("File").Controls("Exit").Enabled = blnExitState
    Application.CommandBars("File").Controls("Close").Enabled = blnExitState
End Sub
 
'------------------------------------------------------------
' CloseButtonState
'
' This subroutine enables/disables the Close button on the
' title bar (top, right-hand corner of the screen)
'------------------------------------------------------------
Sub CloseButtonState(boolClose As Boolean)
    Dim hWnd As Long
    Dim wFlags As Long
    Dim hMenu As Long
    Dim Result As Long
 
    hWnd = Application.hWndAccessApp
    hMenu = GetSystemMenu(hWnd, 0)
    If Not boolClose Then
        wFlags = MF_BYCOMMAND Or MF_GRAYED
    Else
        wFlags = MF_BYCOMMAND And Not MF_GRAYED
    End If
 
    Result = EnableMenuItem(hMenu, SC_CLOSE, wFlags)
End Sub
 

Once you've got the code in there, click the save button at the top and name the module (I named mine modSetCloseState).

Then, create a form, something very basic, and add two command buttons to it. Name the first command button "cmdEnable" and the second one "cmdDisable". Add an [Event Procedure] to the Click event for both buttons and insert the following code:

'------------------------------------------------------
' cmdEnable_Click
'------------------------------------------------------
Private Sub cmdEnable_Click()
    Call SetEnabledState(True)
End Sub
 
'------------------------------------------------------
' cmdDisable_Click
'------------------------------------------------------
Private Sub cmdDisable_Click()
    Call SetEnabledState(False)
End Sub
 

When you run the form, if you click the "Disable" button, you should see the [X] become disabled in the top, right-hand corner, and, if you click on the File menu, you should see that the 'Exit' and 'Close' options have also been disabled. If you click "Enable", the options and the [X] button will become active again.

It's an alternative in case you want to go that route.

Hope this helps and good luck,
beacon

[BobVezz]

Don't know if you are sill looking at this. First rule of coding is to accept the fact that end users are dumb and you have to code around them. I have a similar situation. All I did was simply disable the "Close" button via the form's properties. Now they MUST use the Exit/Close button I privided. Hope this help(s)(ed).

[NeoPa]

May I suggest you read post #5 Bob, for a solution that doesn't require disabling standard features, yet still gives the same, and more, functionality.