[AccessD] brain fart

I posted a solution a while back in Access 97.  It'll work in Access
2000 and later, with one quirk.  The solution William posts, requires a
constant checking of the object with focus.  That's all well and good,
but what if someone is reading through a report....no focus would
change.  Or what if someone was entering a lot of data in one field?

My solution hooks into the mouse and keyboard events of the OS, for the
Access thread itself.  So when something is done (mouse moves, keyboard
typed), the 'last activity' date variable is reset.  Callbacks are very
low over head, so this shouldn't be any more or less of an overhead hit
then a control timer. 

Here's how to do this:  Create a module (modMouseAndKeyboardCallbacks)

Paste the following code in there:

Option Compare Database
Option Explicit
Public gblCurrentKBHookID As Long
Public gblCurrentMouseHookID As Long
Public gblLastActivityTime As Date
Private Declare Function CallNextHookEx Lib "user32" (ByVal hHook As
Long, ByVal ncode As Long, ByVal wParam As Long, lParam As Any) As Long
Private Type POINTAPI
    x As Long
    y As Long
End Type
Private Type MOUSEHOOKSTRUCT
    pt As POINTAPI
    hwnd As Long
    wHitTestCode As Long
    dwExtraInfo As Long
End Type
Function MouseCallbackProc(ByVal intCode As Integer, ByVal wParam As
Long, lParam As MOUSEHOOKSTRUCT) As Long
MouseCallbackProc = CallNextHookEx(gblCurrentMouseHookID, intCode,
wParam, lParam)
gblLastActivityTime = Now
End Function
Function KeyboardCallbackProc(ByVal intCode As Integer, ByVal wParam As
Long, lParam As Long) As Long
KeyboardCallbackProc = CallNextHookEx(gblCurrentKBHookID, intCode,
wParam, lParam)
gblLastActivityTime = Now
End Function

Now you have your callback procedures.  To use this, here's what is on
my sample form (which uses a timer (firing every second, but you could
change that to much higher for timeouts over a minute)) and a label
(lblInactivity):

Option Compare Database
Option Explicit
Private Const WH_KEYBOARD = 2
Private Const WH_MOUSE = 7
Private Declare Function UnhookWindowsHookEx Lib "user32" (ByVal hHook
As Long) As Long
Private Declare Function SetWindowsHookEx Lib "user32" Alias
"SetWindowsHookExA" (ByVal idHook As Long, ByVal lpfn As Long, ByVal
hmod As Long, ByVal dwThreadId As Long) As Long
Private Declare Function GetCurrentThreadId Lib "kernel32" () As Long
Private Sub Form_Load()
gblCurrentMouseHookID = SetWindowsHookEx(WH_MOUSE, AddressOf
MouseCallbackProc, 0, GetCurrentThreadId)
gblCurrentKBHookID = SetWindowsHookEx(WH_KEYBOARD, AddressOf
KeyboardCallbackProc, 0, GetCurrentThreadId)
End Sub

Private Sub Form_Timer()
Me.lblInactivity.Caption = "Inactivity Timer: " & Format(Now -
gblLastActivityTime, "HH:MM:SS")
End Sub

Private Sub Form_Unload(Cancel As Integer)
UnhookWindowsHookEx gblCurrentMouseHookID
UnhookWindowsHookEx gblCurrentKBHookID
End Sub

The form load events launch the 'hooks'.  The unload events release the
hooks.  And the form's timer event simply displays the current time
(gblLastActivityTime) variation from 'Now()'.  So if you just sit and
watch it, it will count up the seconds (and minutes).  Move your mouse,
or use your keyboard, and the timer resets to 0's.  Just replace the
timer event with the specified amount of time you want to allow for
inactivity (with a docmd.Quit).  Have this form launched (and hidden)
when your database opens, and whalla, you're done.

Now for the quirk.  Callbacks require the 'AddressOf' command.  In
Access 97, there was no native command for this.  Someone had come up
with a module that performed the AddressOf function (I think it was Ken
Getz...), so you could use callbacks in Access 97 with the help of this
module.  With the advent of Access 2000, however, it began using VBA 6
(subset of VB 6), which did have a native AddressOf function (this
function returns a pointer to a public function, which an Operating
System process could then use to 'call' a function in one of your
programs (such as alerting your program to when a mouse or keyboard
event occurred).  The quirk is, that since VBA is an interpreted
language, IF you have opened the VBE, and the callback is running,
Access will lock up.  So as long as you don't try to view, or edit code,
it will work fine.  (Since users shouldn't be doing this, callbacks can
be used in a production database, they just need to be disabled when
developing the database, to prevent them from locking up Access).

So I would just put something in, that allows you to prevent that
Inactivity 'form' from opening when you need to go in and look at code.
As long as you do that, the 'bug' won't affect you, and your end users
get the benefits of callback functions! 

Drew

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Rocky Smolin
Sent: Saturday, October 03, 2009 10:08 AM
To: 'Access Developers discussion and problem solving'
Subject: [AccessD] brain fart

Dear List:

I need to terminate an mdb if there has been no activity for a specified
number of minutes.  I remember there was a thread about this recently
but can't remember the subject so I can search the archives.  Does
anyone remember that?

MTIA

Rocky

 

The information contained in this transmission is intended only for the person or entity 
to which it is addressed and may contain II-VI Proprietary and/or II-VI Business 
Sensitive material. If you are not the intended recipient, please contact the sender 
immediately and destroy the material in its entirety, whether electronic or hard copy. 
You are notified that any review, retransmission, copying, disclosure, dissemination, 
or other use of, or taking of any action in reliance upon this information by persons 
or entities other than the intended recipient is prohibited.