MS Access How To Setup A Timer For Inactivity

I can't elaborate.  I'm on my way out but you would use the Timer event.  look in help or search google.

I have searched very much and they all seem to be used for a single form. But is there a way to use the Timer across the whole Front End?

Thanks for the help!

I have found a similar answer on Youtube but have not got it to work completely:

Option Compare Database
'Option Explicit

Dim StartCount As Integer

Private Sub Detail_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)
    'MouseMove Event must Set on Detail Form "Form Body" where it is working Area of form
    txtmouse = X & " / " & Y
    btnShow.Caption = 0
    StartCount = 0
End Sub

Private Sub Form_KeyPress(KeyAscii As Integer)
'KeyPress Event can run after Set .KeyPreview = True
     If KeyAscii <> 0 Then
        txtKey = KeyAscii
        StartCount = 0
        btnShow.Caption = 0
     End If
End Sub

Private Sub Form_Load()
'Command to enable KeyPress Event
    Me.KeyPreview = True
End Sub

Private Sub Form_Timer()
'Set Form Timer Interval Event to 1000 to make it run as clock
'Use StartCount Variable to do as Increment until any value you need  "now Suppose 1 as 1 Seconds"
    StartCount = StartCount + 1
    btnTimer.Caption = Format(Now, "ss")
    btnShow.Caption = StartCount
    If (StartCount = 60) Then
        MsgBox "You have Leave your PC Idle in " & btnShow.Caption & "Seconds"
        DoCmd.Close
        DoCmd.OpenForm "frmLoginScreen"
    End If
End Sub

Select allOpen in new window

My question is:
Why would you want to do such things in MS Access ? It isn't really tailored for timed events.

As for checking if the user is active or not, leave that responsibility to Windows, or sys admins.
Your application has probably other things to do, and adding this kind of "security" will only make it more complex than it need to be.

Fabrice:
I thank you for the input but log story short I have multiple users using the same front end on the same PC under the same Windows USer account. I do not want the user to forget to logout and be responsible for other users errors. Thanks

Thanks Joe. I have to leave for tonight but I will look into this more.

Thanks Jim. I read that before posting this question but was still confused. Do I have to use that on every single form or is there a way to use a function or something across the whole database at once?

Will that hidden form work for all the other forms as well?

<<Will that hidden form work for all the other forms as well?>>

  Yes.  Drop the code into a form and open it hidden at start up.   Every time the timer fires, it looks at the active form and control.   If there is no changed after xx minutes, then the user is idle.

Jim.

I have multiple users using the same front end on the same PC under the same Windows USer account.

Verrrrrry bad idea.  You should split the app into FE and BE and give each user his own personal copy of the FE.  Your setup is corruption waiting to happen.

In having used Peters Forced Shut Down app (similar, but different than the InActive app) ... he has taken a LOT of not so obvious things into account.  You can download the sample db and see how it all works.  And of course, you can just drop in the code as well like the Microsoft KB.

Check them both out

Thank you all for the help as I am going to look into both of the options here today as much as I can.

As for Pat

You should split the app into FE and BE

My DB is split with my BE on another PC.  But I have multiple users on the same PC, FE, and Windows profile.

But I have multiple users on the same PC, FE, and Windows profile.

Under no conditions should multiple users open the same physical file.  Every user needs his own personal copy.  There are lots of easy ways to distribute FE copies.  I use a batch file.  If you need help developing a batch file, let me know.

Have you ever noticed that Access changes the update date of an .accdb after you close it?  The reason is that in its normal course of operation, Access actually updates the file.  So, allowing multiple users to open the same physical file means that multiple users will be saving updates to the database.  So, even if you are not using bad techniques such as make tables and modifying form layouts on the fly, etc, Access will still find a reason to update the database and that is what leads to the corruption.

Pat I thank you for the extra help but I just want to make sure we are on the same page here.
You said

Under no conditions should multiple users open the same physical file.

Every PC has its own FE copy of the database. When I say multiple users using the same FE. I mean that one person uses the FE on a PC and then they leave. Another person comes along and then uses that same PC with that same FE. There is never more than 1 person using the same FE file on the same PC.  If there are 2 people at the same time using the the BE of the database then there are 2 PCs and each PC has a FE file of its own.

Please let me know if we are on the same page or not. I would like any input to enhance security and reduce corruption.

Thanks.

Dale Thanks for the input on the closing of the forms and forcing the user out. This is exactly where I was heading with this. I looked over your DB and I didn't see any of the Public Functions you mentioned called PassesChecks. I am interested in this. Do you have an example of it?
Thanks for the help!
would it be like:

Public Function PassesChecks ()

If txtbox1 & "" = "" then
msgbox "Failed"
Else
End if

If Txtbox2 & "" = "" Then
msgbox "Failed"
Else 
End if
Exit Function

Select allOpen in new window

Thanks for the clarification Dustin.  There is a big difference between concurrent and serial users.

This is what I came up with in the big picture. I still have to set up the CheckPasses as Dale mentioned but I hope this helps future visitors.

In all Forms I have:

• frmLoginScreen (Which is a login Form)
• frmDetectIdleTime (Main Timer to track for inactivity)
• frmDetectIdleTimeAutoLogOutMsgbox (The form that is made to look like a messagebox with 2 buttons...Yes and No...The form says you are about to be logged out due to inactivity. Do you want to stay logged in.)
• frmDetectIdleTimeCloseDatabase (This is the second timer that times down to see if the user has chosen yes or no and if not it closes the database.)

I started out using the link Jim provided: https://msdn.microsoft.com/en-us/library/bb243861(v=office.12).aspx

frmLoginScreen :

Option Compare Database
Option Explicit

Private Sub btnLogin_Click()
On Error GoTo ErrorHandler

    Dim rs As Recordset

    Set rs = CurrentDb.OpenRecordset("Employees", dbOpenSnapshot, dbReadOnly)

    rs.FindFirst "EmpUserNm='" & Me.txtUserName & "'"

    If rs.NoMatch = True Then
        'Me.lblWrongUser.Visible = True
        'Me.txtUserName.SetFocus
        MsgBox "Something is wrong!" & vbNewLine & "Either the user name or password are incorrect. Please try again.", vbCritical, "Says!"
        Exit Sub
    End If
    'Me.lblWrongUser.Visible = False
    
    If rs!EmpPassword <> Nz(Me.txtPassword, "") Then
        'Me.lblWrongPass.Visible = True
        'Me.txtPassword.SetFocus
        MsgBox "Something is incorrect!" & vbNewLine & "Please try again.", vbCritical, "Says!"
        Exit Sub
    End If
    'Me.lblWrongPass.Visible = False
     DoCmd.OpenForm "frmHomePage" 'Open Home Page Form
      Me!txtEmpID.Value = rs!EmpID
       DoCmd.SetWarnings False
        DoCmd.OpenQuery "qryUserActivityLoggedIntoDatabase" 'Adds a record to UserActivityLog Table that the user logged into the database.
         DoCmd.SetWarnings True
          Me.Visible = False
           DoCmd.OpenForm "frmDetectIdleTime", , , , , acHidden 'Open the Hidden Timer Form
          
Exit Sub

ErrorHandler:
DoCmd.SetWarnings True
 Application.Echo True
  MsgBox "Error " & Err.Number & ": " & Err.Description & " in " & _
   VBE.ActiveCodePane.CodeModule, vbCritical, "Error"
End Sub


Private Sub Form_Current()

On Error GoTo ErrorHandler

 Me!txtEmpID = "" 'Clear the EmpID Value
 
Exit Sub

ErrorHandler:
 MsgBox "Error " & Err.Number & ": " & Err.Description & " in " & _
   VBE.ActiveCodePane.CodeModule, vbCritical, "Error"
End Sub

Private Sub Form_Unload(Cancel As Integer)

On Error GoTo ErrorHandler

 DoCmd.SetWarnings False
  DoCmd.OpenQuery "qryUserActivityLoggedOutOfDatabase" 'Adds a record to UserActivityLog Table that the user logged Out Of the database.
   DoCmd.SetWarnings True
   
Exit Sub

ErrorHandler:
 DoCmd.SetWarnings True
  MsgBox "Error " & Err.Number & ": " & Err.Description & " in " & _
   VBE.ActiveCodePane.CodeModule, vbCritical, "Error"
End Sub

Select allOpen in new window

frmDetectIdleTime :

Option Compare Database
Option Explicit

Sub Form_Timer()
         ' IDLEMINUTES determines how much idle time to wait for before
         ' running the IdleTimeDetected subroutine.
         Const IDLEMINUTES = 10
         
         Static PrevControlName As String
         Static PrevFormName As String
         Static ExpiredTime

         Dim ActiveFormName As String
         Dim ActiveControlName As String
         Dim ExpiredMinutes

         On Error Resume Next

         ' Get the active form and control name.

         ActiveFormName = Screen.ActiveForm.Name
         If Err Then
            ActiveFormName = "No Active Form"
            Err = 0
         End If

         ActiveControlName = Screen.ActiveControl.Name
            If Err Then
            ActiveControlName = "No Active Control"
            Err = 0
         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 than 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 IDLEMINUTES?
         ExpiredMinutes = (ExpiredTime / 1000) / 60
         If ExpiredMinutes >= IDLEMINUTES Then
            ' ...if so, then reset the expired time to zero...
            ExpiredTime = 0
            ' ...and call the IdleTimeDetected subroutine.
            IdleTimeDetected
         End If
      End Sub
      'Once the idle time has been reached this is what happens
      
      Sub IdleTimeDetected()
      
  On Error GoTo ErrorHandler
        
'Now let the User know they are about to be auto logged out.
 DoCmd.Close acForm, Me.Name 'Close this form. This stops the timer.
  DoCmd.OpenForm "frmDetectIdleTimeAutoLogOutMsgbox" 'Open the Msgbox form.
   DoCmd.OpenForm "frmDetectIdleTimeCloseDatabase", , , , , acHidden 'Open the Hidden Timer Form so if the msgbox form is not clicked the database will be automatically closed.
    Beep ' Make a Beep sound for the messagebox form.
   Exit Sub

ErrorHandler:
DoCmd.SetWarnings True
  MsgBox "Error " & Err.Number & ": " & Err.Description & " in " & _
   VBE.ActiveCodePane.CodeModule, vbCritical, "Error"
End Sub
      

Select allOpen in new window

frmDetectIdleTimeAutoLogOutMsgbox :

Option Compare Database

Private Sub btnNo_Click()

On Error GoTo ErrorHandler

 DoCmd.SetWarnings False
  DoCmd.OpenQuery "qryUserActivityLoggedOutOfDatabase" 'Adds a record to UserActivityLog Table that the user logged Out Of the database.
   DoCmd.SetWarnings True
    DoCmd.Close acForm, "frmDetectIdleTimeCloseDatabase" 'Close the Hidden Msgbox Timer form. This stops the Timer that will close the Datatabase.
     DoCmd.Close acForm, Me.Name 'Close this form"
      Forms!frmLoginScreen.Visible = True
       Forms!frmLoginScreen!txtUserName.Value = ""
        Forms!frmLoginScreen!txtPassword.Value = ""
         Forms!frmLoginScreen!txtEmpID.Value = ""

Exit Sub

ErrorHandler:
DoCmd.SetWarnings True
 MsgBox "Error " & Err.Number & ": " & Err.Description & " in " & _
   VBE.ActiveCodePane.CodeModule, vbCritical, "Error"
End Sub

Private Sub btnYes_Click()

On Error GoTo ErrorHandler

DoCmd.Close acForm, "frmDetectIdleTimeCloseDatabase" 'Close the Hidden Msgbox Timer form. This stops the Timer that will close the Datatabase.
 DoCmd.Close acForm, Me.Name 'Close this msgbox form.
  DoCmd.OpenForm "frmDetectIdleTime", , , , , acHidden 'Open the Hidden Timer Form thattracksthe users activity.

Exit Sub

ErrorHandler:
DoCmd.SetWarnings True
 MsgBox "Error " & Err.Number & ": " & Err.Description & " in " & _
   VBE.ActiveCodePane.CodeModule, vbCritical, "Error"
End Sub

Select allOpen in new window

frmDetectIdleTimeCloseDatabase :

Option Compare Database
Option Explicit

Sub Form_Timer()
         ' IDLEMINUTES determines how much idle time to wait for before
         ' running the IdleTimeDetected subroutine.
         Const IDLEMINUTES = 1
         
         Static PrevControlName As String
         Static PrevFormName As String
         Static ExpiredTime

         Dim ActiveFormName As String
         Dim ActiveControlName As String
         Dim ExpiredMinutes

         On Error Resume Next

         ' Get the active form and control name.

         ActiveFormName = Screen.ActiveForm.Name
         If Err Then
            ActiveFormName = "No Active Form"
            Err = 0
         End If

         ActiveControlName = Screen.ActiveControl.Name
            If Err Then
            ActiveControlName = "No Active Control"
            Err = 0
         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 than 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 IDLEMINUTES?
         ExpiredMinutes = (ExpiredTime / 1000) / 60
         If ExpiredMinutes >= IDLEMINUTES Then
            ' ...if so, then reset the expired time to zero...
            ExpiredTime = 0
            ' ...and call the IdleTimeDetected subroutine.
            IdleTimeDetected
         End If
      End Sub
      'Once the idle time has been reached this is what happens
      
      Sub IdleTimeDetected()
      
  On Error GoTo ErrorHandler
        
        'The user never responded. So we are closing the database.
    Application.Quit 'Close the Database
       
   Exit Sub

ErrorHandler:
DoCmd.SetWarnings True
  MsgBox "Error " & Err.Number & ": " & Err.Description & " in " & _
   VBE.ActiveCodePane.CodeModule, vbCritical, "Error"
End Sub
      

Select allOpen in new window

I tell ya this was a good toss up between Jim and Dale but Jim you got to it quicker and your answer was straight to the exact question.

Dale your extra input was amazing!

Thank you Joe for the link. I looked through those and he has some very interesting things for sure. I will probably be using a few of them in the future.

Thanks hnasr for the link this helped me tune somethings up.

Thanks Pat for the input and concern you have for me. It lets me know you care.

Thanks Fabrice Lambert for the advice. This one really made me think and in the future I am going to use this advice for some other applications but I don't  think it fits well here.


THANK YOU ALL FOR THE HELP! WITHOUT YOU I COULDN'T DO IT!

Dustin,

If you are puttting all of that in each form, you are really overdoing it, and are really going to bloat your application with code that is unnecessary.  Put it all in one form (frm_Inactive) and let that one form do all of the tracking for the rest of the application.

Furthermore, if you decide you have to change something, you have to change it in every form.

Work smarter, not harder.

Dale

It's also best to have a minimal amount of timers firing off in the background.

 The other advantage to a single form; if it's the first form open, it will be the first to close by user action (i.e. clicking the "X" of the main access window) or the DB being closed.  Because of that, you can control the shutdown (and even cancel it if you want) of Access.   I have a check box control on the form cbxOKToClose, which I set when a user clicks the normal "Exit".   If they close the app using the main window close, the flags not set, and I cancel the close.    You can also do things like check for open forms and tell them to close them first.

 The other thing you can build in here is an admin utility to boot out people.   As part of the timer event, call a sub that checks a flag (record in a table, field, file on disk or whatever) and if found, tell the user to close and logout.  Also don't let anyone in if the flag is set, etc.

 All kinds of stuff you can do with a hidden form.    In my apps, it's called frmGlobal and I do a lot of different things with it.

 Another is maintaining a constant connection to the BE(s).   frmGlobal opens recordsets into all my back ends, and closes them when it closes.

Jim.

Dustin,

Jim's comment:
 "Another is maintaining a constant connection to the BE(s).   frmGlobal opens recordsets into all my back ends, and closes them when it closes."
is important because if you don't have a persistent connection to the BE database, Access is forced to open and close that file and create the associated laccdb file every time you open a recordset or open a bound form.  Most professional developers do this to ensure that those files are connected to the database at all times to speed up the application.  And if you have multiple BE files, you should connect to all of them.

Thank you for the input and I will be looking into this for sure. As for the two separate timers the way I have it set up there is only one timer at a time going. It closes one timer then opens the next. I hope this makes sense. Otherwise I couldn't think of a way to do a different action for the same timer after the time has been reached. This is the reason for two forms with separate timers.

<<Otherwise I couldn't think of a way to do a different action for the same timer after the time has been reached. >>

  User the timer event for the minimum value of how often you want to make checks, but not for timing the event(s) themselves.  For example, set the timer for one minute.

Then In the OnTimer event, do:

  Call DetectIdleTime
  Call DetectIdleTimeCloseDatabase
  Call CheckForDatabaseShutdown

 So every minute, you will be checking for three conditions.    On the first, you might warn the user at 5 minutes.   The second, at 30.    So every minute when the timer fires, your asking "Has five minutes elpased?" for the first, and "Has thirty elapsed?" for the second.

 You accomplish that by having two controls on your form:

  txtLastDetectIdleTime
  txtLastDetectIdleTimeCloseDatabase

 which hold the last time you saw activity.  Then using DateDiff(), you can compare now() against the control.

  So once a minute, you do:

   Has activity occured?   If yes, you update txtLastDetectIdleTime with Now().   If no, then is NOW() - txtLastDetectIdleTime > 5 minutes?

  The 3rd call might not be time based at all, but checking a flag that an admin sets to force users out as I mentioned in my other comment.

  In setting it up this way, you can have one timer do a number of checks.  You don't need one timer for each check.

Jim.

When you say "all my back ends" and "And if you have multiple BE files, you should connect to all of them."

Do you mean multiple Access Files such as (.accdb) or do you mean multiple tables in a single BE Access file (.accdb)?

Multiple .accdb's.

You only need one connection to each.   What I do is open a record set that returns no records and I leave that open for the life of the app.  As Dale mentioned, this avoids the repeated logging out/in of the database, which has quite a bit of overhead. That looks like this:

Private Sub Form_Open(Cancel As Integer)

    ' Open backend table in JET based MDB/MDE for performance.
    'Set rstBE1 = CurDB().OpenRecordset("SELECT * FROM tbl_ActualCount WHERE 1=0")

End Sub

Select allOpen in new window

and then in the close of the hidden form:

Private Sub Form_Close()

10    On Error Resume Next

20    rstBE1.Close
30    Set rstBE1 = Nothing

End Sub

Select allOpen in new window

Depending on how your app is used, you may or may not notice a difference.   For some operations though, like refreshing table links, it can drop from minutes to seconds.

Jim.

Ok I got it all wrapped up into 1 form and 1 module now. If I keep adding to this form I will do the multiple modules to simplify it more but for right now it seems good because I am only checking for 2 things. (Initial Inactivity and then if the user responds to the message box form)

I have the time interval on my form set to 1 minute so this will give the user 1 minute to respond before it shuts down the application for security reasons.

Option Compare Database
Option Explicit

Public Sub DetectIdleTime()
        ' IDLESECONDS determines how much idle time to wait for before
         ' running the IdleTimeDetected subroutine.
         Const IDLESECONDS = 600000  'Every 60000 is a Minute. This is set for 10 Minutes.
         
         Static PrevControlName As String
         Static PrevFormName As String
         Static LastTimeActive
         
         Dim ActiveFormName As String
         Dim ActiveControlName As String
         Dim ExpiredSeconds

         On Error Resume Next

         ' Get the active form and control name.

         ActiveFormName = Screen.ActiveForm.Name
         If Err Then
            ActiveFormName = "No Active Form"
            Err = 0
         End If

         ActiveControlName = Screen.ActiveControl.Name
            If Err Then
            ActiveControlName = "No Active Control"
            Err = 0
         End If

         ' Record the current active names and reset LastTimeActive if:
         '    1. They have not been recorded yet (code is running
         '       for the first time).
         '    2. The previous names are different than 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
            Forms!frmDetectIdleTime!txtLastDetectIdleTime = Time()
         End If
           
          ' Does the total expired time exceed the IDLESECONDS?
         ExpiredSeconds = DateDiff("s", Forms!frmDetectIdleTime!txtLastDetectIdleTime, Time())
         
 'Check to see if the Auto Log Out Form is open and active warning the user they are about to be auto logged out.
If CurrentProject.AllForms("frmDetectIdleTimeAutoLogOutMsgbox").IsLoaded = True Then 'Check to see if the form is already open.
        'The user never responded. So we are closing the database.
    Forms!frmDetectIdleTime!OKToClose = True 'This is a flag on the hidden form to check if it is ok to close the database.
     Application.Quit 'Close the Database
Exit Sub
End If

         If ExpiredSeconds >= IDLESECONDS Then
            ' ...if so, then reset the expired time to zero...
            Forms!frmDetectIdleTime!txtLastDetectIdleTime = Time()
            ' ...and call the IdleTimeDetected subroutine.
            IdleTimeDetected
         End If
    End Sub
      
      Sub IdleTimeDetected()
      'Once the idle time has been reached this is what happens
  On Error GoTo ErrorHandler

'Now let the User know they are about to be auto logged out.
  DoCmd.OpenForm "frmDetectIdleTimeAutoLogOutMsgbox" 'Open the Msgbox form.
    Beep ' Make a Beep sound for the messagebox form.
   Exit Sub

ErrorHandler:
DoCmd.SetWarnings True
  MsgBox "Error " & Err.Number & ": " & Err.Description & " in " & _
   VBE.ActiveCodePane.CodeModule, vbCritical, "Error"
End Sub

Select allOpen in new window

Thanks a ton for the help and ideas. It means a lot and is extremely helpful. It gets me thinking.