469,315 Members | 1,847 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,315 developers. It's quick & easy.

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

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?
Mar 9 '11 #1

✓ answered by 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.

10 18225
TheSmileyCoder
2,321 Expert Mod 2GB
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.
Mar 9 '11 #2
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.
Mar 9 '11 #3
TheSmileyCoder
2,321 Expert Mod 2GB
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.
Mar 9 '11 #4
NeoPa
32,173 Expert Mod 16PB
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?
Mar 10 '11 #5
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?
Mar 11 '11 #6
NeoPa
32,173 Expert Mod 16PB
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 :

Expand|Select|Wrap|Line Numbers
  1. Private strUser As String
  2.  
  3. Private Sub Form_Open(Cancel As Integer)
  4.     strUser = Nz(Me.OpenArgs, "")
  5. 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.
Mar 11 '11 #7
NeoPa
32,173 Expert Mod 16PB
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.
Mar 11 '11 #8
beacon
579 512MB
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:
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private Declare Function GetSystemMenu Lib "user32" (ByVal hWnd As Long, _
  5.     ByVal bRevert As Long) As Long
  6.  
  7. Private Declare Function EnableMenuItem Lib "user32" (ByVal hMenu As _
  8.     Long, ByVal wIDEnableItem As Long, ByVal wEnable As Long) As Long
  9.  
  10. Const MF_GRAYED = &H1&
  11. Const MF_BYCOMMAND = &H0&
  12. Const SC_CLOSE = &HF060&
  13.  
  14. '------------------------------------------------------------
  15. ' SetEnabledState
  16. '
  17. ' This function calls the CloseButtonState and ExitMenuState
  18. ' subroutines included in this module
  19. '------------------------------------------------------------
  20. Public Function SetEnabledState(blnState As Boolean)
  21.     Call CloseButtonState(blnState)
  22.     Call ExitMenuState(blnState)
  23. End Function
  24.  
  25. '------------------------------------------------------------
  26. ' ExitMenuState
  27. '
  28. ' This subroutine enables/disables the Exit and Close options
  29. ' under the File menu
  30. '------------------------------------------------------------
  31. Sub ExitMenuState(blnExitState As Boolean)
  32.     Application.CommandBars("File").Controls("Exit").Enabled = blnExitState
  33.     Application.CommandBars("File").Controls("Close").Enabled = blnExitState
  34. End Sub
  35.  
  36. '------------------------------------------------------------
  37. ' CloseButtonState
  38. '
  39. ' This subroutine enables/disables the Close button on the
  40. ' title bar (top, right-hand corner of the screen)
  41. '------------------------------------------------------------
  42. Sub CloseButtonState(boolClose As Boolean)
  43.     Dim hWnd As Long
  44.     Dim wFlags As Long
  45.     Dim hMenu As Long
  46.     Dim Result As Long
  47.  
  48.     hWnd = Application.hWndAccessApp
  49.     hMenu = GetSystemMenu(hWnd, 0)
  50.     If Not boolClose Then
  51.         wFlags = MF_BYCOMMAND Or MF_GRAYED
  52.     Else
  53.         wFlags = MF_BYCOMMAND And Not MF_GRAYED
  54.     End If
  55.  
  56.     Result = EnableMenuItem(hMenu, SC_CLOSE, wFlags)
  57. End Sub
  58.  
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:
Expand|Select|Wrap|Line Numbers
  1. '------------------------------------------------------
  2. ' cmdEnable_Click
  3. '------------------------------------------------------
  4. Private Sub cmdEnable_Click()
  5.     Call SetEnabledState(True)
  6. End Sub
  7.  
  8. '------------------------------------------------------
  9. ' cmdDisable_Click
  10. '------------------------------------------------------
  11. Private Sub cmdDisable_Click()
  12.     Call SetEnabledState(False)
  13. End Sub
  14.  
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
Mar 11 '11 #9
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).
Jan 23 '12 #10
NeoPa
32,173 Expert Mod 16PB
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.
Jan 23 '12 #11

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

4 posts views Thread by Keith-Earl | last post: by
2 posts views Thread by Matthias H. | last post: by
4 posts views Thread by =?Utf-8?B?RGVhbiBHYXJyZXR0?= | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by harlem98 | last post: by
1 post views Thread by Geralt96 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.