How to close out Access when a computer is locked?

[normalice]

I found the vb that explains what to do to detect idle time and close out access automatically when idle and it works great, however this does not work if someone locks their computer but leaves access running.

is there a way to either get this working or to simply force this access database to close, and no other programs or databases, the moment a computer is locked?

the code used to close on idle is merely the microsoft-suggested code (http://support.microsoft.com/kb/128814)

[NeoPa]

I can't imagine how it can be possible to detect idle time without working when the keyboard is locked. If the keyboard is locked then the computer is idle.

Maybe if you posted the code we're supposed to be looking at then we may (only may - this should have been available in the question) be able to help.

[normalice]

The microsoft link in the original question suggests adding this code to a form that you force to open upon the start of Access, and further modify that form's properties to remain hidden so that no one closes it out. This is fine but it can be annoying if you are still trying to fine-tune some components of a database that is already seeing occasional use.

If you don't mind being less fancy for a spell, disable the code in your startup switchboard such that it does not close out when a new switchboard or form is opened. You might also need to disable the code that attempts to re-open the main switchboard in your secondary switchboard forms. Then, add this code to your main switchboard instead of a hidden form.

So, if you have work to do and can't risk being interrupted with an untimely close without save, you can just close the main switchboard and not worry about it - well, worry less about it: it is Windows we're talking about...

[NeoPa]

I used a modified version to test with :

Option Compare Database
Option Explicit
 
' conIdleThreshold determines how much idle time to wait
' for before running the IdleTimeDetected subroutine.
Private Const conIdleThreshold As Long = 5 * 60& * 1000&
 
Private PrevControlName As String
Private PrevFormName As String
Private ExpiredTime As Long
 
Private Sub Form_Timer()
    Dim ActiveFormName As String
    Dim ActiveControlName As String
    Dim lngErr As Long, lngIx As Long
 
    On Error Resume Next
    lngErr = 0
 
    ' Get the active form and control name.
    With Screen
        ActiveFormName = .ActiveForm.Name
        If Err = 2475 Then
            ActiveFormName = "No Active Form"
        ElseIf Err Then
            lngErr = Err
        End If
        ActiveControlName = .ActiveControl.Name
        If Err = 2474 Then
            ActiveControlName = "No Active Control"
        ElseIf Err Then
            lngErr = Err
        End If
    End With
    If lngErr Then
        ' ...Clear the timer event...
        Me.TimerInterval = 0
        Call MsgBox(lngErr)
        For lngErr = 1 To 60
            Beep
            For lngIx = 1 To 100
                DoEvents
            Next lngIx
        Next lngErr
        Stop
    End If
 
    ' Record the current active names and reset ExpiredTime if:
    '    1. They have not been recorded yet (code is running
    '       for the first time).
    '    2. The previous names are different from the current ones
    '       (the user has done something different during the timer
    '        interval).
    If (PrevControlName = "") _
    Or (PrevFormName = "") _
    Or (ActiveFormName <> PrevFormName) _
    Or (ActiveControlName <> PrevControlName) Then
        PrevControlName = ActiveControlName
        PrevFormName = ActiveFormName
        ExpiredTime = 0
    Else
        ' ...otherwise the user was idle during the time interval, so
        ' increment the total expired time.
        ExpiredTime = ExpiredTime + Me.TimerInterval
    End If
 
    ' Does the total expired time exceed the conIdleThreshold?
    If ExpiredTime >= conIdleThreshold Then
        ' ...if so, then reset the expired time to zero...
        ExpiredTime = 0
        ' ...Clear the timer event...
        Me.TimerInterval = 0
        ' ...and call the IdleTimeDetected subroutine.
        Call MsgBox("Triggered")
        For lngErr = 1 To 60
            Beep
            For lngIx = 1 To 100
                DoEvents
            Next lngIx
        Next lngErr
        Stop
    End If
End Sub

In my testing I found that the code actually did execute. I heard all the beeps then unlocked the screen to find the code at the Stop on line #81.

What causes you to believe the code isn't executing as intended?

[normalice]

i have it set to close after 7 minutes, but when someone leaves their computer locked with this open all day they come back to find it still open.

[NeoPa]

1. What does the closing code do (Please post it for me)?
2. Does it work as expected when the PC is not locked?

In the mean-time, check my version of the code on a test form to see if you come up with the same results as I do.

[normalice]

Sub IdleTimeDetected(ExpiredMinutes)
Application.Quit acQuitSaveNone
End Sub

that is the idletimedetected subroutine

It does work as expected when the computer is not locked, and I'll give the beep a shot.

[NeoPa]

The only thing to come to mind is that you don't clear the timer (Me.TimerInterval = 0), so the .Quit may fail (as the database has outstanding code running). It's probably a long-shot, but try it out anyway.

[normalice]

i just pasted your code in with the application.quit instead of the messagebox. so far it seems to be working just fine.

Thanks!

[NeoPa]

That code was only ever meant for testing. It should work, fundamentally, but it includes a whole bunch of unnecessary lines simply to help me determine what was what.

Here is a sanitised version, with the MsgBox() call where you would want to put the call to Application.Quit (or IdleTimeDetected()) :

Option Compare Database
Option Explicit
 
' conIdleThreshold determines how much idle time to wait
' for before running the IdleTimeDetected subroutine.
Private Const conIdleThreshold As Long = 5 * 60& * 1000&
 
Private PrevControlName As String
Private PrevFormName As String
Private ExpiredTime As Long
 
Private Sub Form_Timer()
    Dim ActiveFormName As String
    Dim ActiveControlName As String
 
    On Error Resume Next
 
    ' Get the active form and control name.
    With Screen
        ActiveFormName = .ActiveForm.Name
        If Err = 2475 Then ActiveFormName = "No Active Form"
        ActiveControlName = .ActiveControl.Name
        If Err = 2474 Then ActiveControlName = "No Active Control"
    End With
 
    ' Record the current active names and reset ExpiredTime if:
    '    1. They have not been recorded yet (code is running
    '       for the first time).
    '    2. The previous names are different from the current ones
    '       (the user has done something different during the timer
    '        interval).
    If (PrevControlName = "") _
    Or (PrevFormName = "") _
    Or (ActiveFormName <> PrevFormName) _
    Or (ActiveControlName <> PrevControlName) Then
        PrevControlName = ActiveControlName
        PrevFormName = ActiveFormName
        ExpiredTime = 0
    Else
        ' ...otherwise the user was idle during the time interval, so
        ' increment the total expired time.
        ExpiredTime = ExpiredTime + Me.TimerInterval
    End If
 
    ' Does the total expired time exceed the conIdleThreshold?
    If ExpiredTime >= conIdleThreshold Then
        ' ...if so, then reset the expired time to zero...
        ExpiredTime = 0
        ' ...Clear the timer event...
        Me.TimerInterval = 0
        ' ...and call the IdleTimeDetected subroutine.
        Call MsgBox("Triggered")
    End If
End Sub

[normalice]

hey, i had a follow-up question, and since I don't really understand some of the code you posted, i figured I should just ask directly.

In addition to the activecontrolname and and activeformname I would also like to include the activecontrolvalue: if that changes then also reset the counter.

I think it would go something like this, but I don't know what the question marks (????) would be. I've underlined the parts I added, and bolded/italicized the part for which I haven't a clue. Of course, the bold doesn't show up so well (the question marks should be a dead giveaway)...

Option Compare Database 
Option Explicit 
 
' conIdleThreshold determines how much idle time to wait 
' for before running the IdleTimeDetected subroutine. 
Private Const conIdleThreshold As Long = 5 * 60& * 1000& 
 
Private PrevControlName As String 
Private PrevFormName As String 
Private ExpiredTime As Long 
 Private PrevControlValue as string  
Private Sub Form_Timer() 
    Dim ActiveFormName As String 
    Dim ActiveControlName As String 
    Dim ActiveControlValue as String
    On Error Resume Next 
 
    ' Get the active form and control name. 
    With Screen 
        ActiveFormName = .ActiveForm.Name 
        If Err = 2475 Then ActiveFormName = "No Active Form" 
        ActiveControlName = .ActiveControl.Name 
        If Err = 2474 Then ActiveControlName = "No Active Control" 
        ActiveControlValue = .ActiveControl.Value
        If Err = ???? Then ActiveControlValue = "No Value"    
End With 
 
    ' Record the current active names and reset ExpiredTime if: 
    '    1. They have not been recorded yet (code is running 
    '       for the first time). 
    '    2. The previous names are different from the current ones 
    '       (the user has done something different during the timer 
    '        interval). 
    If (PrevControlName = "") _ 
    Or (PrevFormName = "") _ 
    Or (ActiveFormName <> PrevFormName) _ 
    Or (ActiveControlName <> PrevControlName) _
    Or (ActiveControlValue <> PrevControlValue) Then 
        PrevControlName = ActiveControlName 
        PrevFormName = ActiveFormName
        PrevControlValue = ActiveControlValue        
        ExpiredTime = 0 
    Else 
        ' ...otherwise the user was idle during the time interval, so 
        ' increment the total expired time. 
        ExpiredTime = ExpiredTime + Me.TimerInterval 
    End If 
 
    ' Does the total expired time exceed the conIdleThreshold? 
    If ExpiredTime >= conIdleThreshold Then 
        ' ...if so, then reset the expired time to zero... 
        ExpiredTime = 0 
        ' ...Clear the timer event... 
        Me.TimerInterval = 0 
        ' ...and call the IdleTimeDetected subroutine. 
        Call MsgBox("Triggered") 
    End If 
End Sub 
 

[Endkill Emanon]

This is simple if you have a network admin account. You ether go to or remote into the offending computer. Then log-out the person using the admin account. They'll lose their work but you'll get your DB back.

[NeoPa]

That's actually quite good thinking. A user may well stay in the same control and still be active. I like that.

You've made a pretty good stab at it I would say, but you probably want to treat the .Value as a Variant type, rather than as a String specifically. I've made some changes to your code, but not highlighted where they are. You'll need to copy and paste the whole lot.

Option Compare Database
Option Explicit
 
' conIdleThreshold determines how much idle time to wait
' for before running the IdleTimeDetected subroutine.
Private Const conIdleThreshold As Long = 5 * 60& * 1000&
 
Private PrevControlName As String
Private PrevFormName As String
Private ExpiredTime As Long
Private PrevControlValue as Variant
 
Private Sub Form_Timer()
    Dim ActiveFormName As String
    Dim ActiveControlName As String
    Dim ActiveControlValue as Variant
 
    On Error Resume Next
 
    ' Get the active form and control name.
    With Screen
        ActiveFormName = .ActiveForm.Name
        If Err = 2475 Then ActiveFormName = "No Active Form"
        ActiveControlName = .ActiveControl.Name
        If Err = 2474 Then
            ActiveControlName = "No Active Control"
            ActiveControlValue = Null
        Else
            ActiveControlValue = .ActiveControl.Value
        End If
    End With
 
    ' Record the current active names and reset ExpiredTime if:
    '    1. They have not been recorded yet (code is running
    '       for the first time).
    '    2. The previous names are different from the current ones
    '       (the user has done something different during the timer
    '        interval).
    If (PrevControlName = "") _
    Or (PrevFormName = "") _
    Or (IsNull(PrevControlValue)) _
    Or (ActiveFormName <> PrevFormName) _
    Or (ActiveControlName <> PrevControlName) _
    Or (ActiveControlValue <> PrevControlValue) Then
        PrevControlName = ActiveControlName
        PrevFormName = ActiveFormName
        PrevControlValue = ActiveControlValue
        ExpiredTime = 0
    Else
        ' ...otherwise the user was idle during the time interval, so
        ' increment the total expired time.
        ExpiredTime = ExpiredTime + Me.TimerInterval
    End If
 
    ' Does the total expired time exceed the conIdleThreshold?
    If ExpiredTime >= conIdleThreshold Then
        ' ...if so, then reset the expired time to zero...
        ExpiredTime = 0
        ' ...Clear the timer event...
        Me.TimerInterval = 0
        ' ...and call the IdleTimeDetected subroutine.
        Call MsgBox("Triggered")
    End If
End Sub

[NeoPa]

Endkill Emanon:
This is simple if you have a network admin account. You ether go to or remote into the offending computer. Then log-out the person using the admin account. They'll lose their work but you'll get your DB back.

I'm not sure what you're trying to say exactly, but the question was specifically asking about closing down when the database was idle. Kicking a user off would not fall into that category.

[normalice]

thanks again, NeoPa.

I haven't actually tried this yet, but I was wondering about a specific situation: if the active control is a button. Buttons don't have a value, but since the activecontrolname doesn't throw an error, it will try to save the activecontrolvalue - but attempting to do so on a button should throw an error, i think? Wouldn't erroring during the attempt to write the activecontrolvalue cause problems in the if statement that controls recording and resetting - thus causing the database to never close if a button remains the active control?

...

Okay, i tried it. the error it throws when a button is selected it 438. I'll just add that after the write and it'll be fine.

Thanks again for all your help!

[Endkill Emanon]

Logging out the user/customer(CX) gets them out of the database thus unlocking it and allowing others to get into it. I've had 3 different jobs where I've had at times to log someone off so that other CX can use a file or database.

Also If I remember correctly there's a way using ASP to interface the db. You could create a web-app this way and the only thing that has it locked is the server hosting it.

[NeoPa]

Sorry for the delay responding.

I think your idea is actually a very good one. There is a potential loophole there if an operator is continually working on data on the same form (particularly if there are only a few controls on it).

After line #29 you can add an If statement just like the preceeding ones that checks for Err = 438 and sets that variable (ActiveControlValue) to either a specific string or possibly even Null. That should work nicely for you.