MS Access How To Setup A Timer For Inactivity

In MS Access how would I setup a timer for Inactivity?

Such as:
If no activity for XXXXX time Then
This happens
End If
Exit Sub

Open in new window


Thanks for the help!
LVL 1
Dustin StanleyEntrepreneurAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PatHartmanCommented:
I can't elaborate.  I'm on my way out but you would use the Timer event.  look in help or search google.
0
Dustin StanleyEntrepreneurAuthor Commented:
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

Open in new window

0
Fabrice LambertFabrice LambertCommented:
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.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
If you are tying to Close a Form due to inactivity Peter's (free) Inactive Shut Down Control works very well ... has a db you can download with code and to test with.  You likely can adopt it to your needs.

http://www.peterssoftware.com/isd.htm
0
Dustin StanleyEntrepreneurAuthor Commented:
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
0
Dustin StanleyEntrepreneurAuthor Commented:
Thanks Joe. I have to leave for tonight but I will look into this more.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
FYI:

How to: Detect User Idle Time or Inactivity
https://msdn.microsoft.com/en-us/library/bb243861(v=office.12).aspx

 with code...just drop it in.

Jim.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Dustin StanleyEntrepreneurAuthor Commented:
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?
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<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.
0
PatHartmanCommented:
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.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
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
0
Hamed NasrRetired IT ProfessionalCommented:
See if you can make use of this EE link:
Shutdown Access Database After Certain Idle Time
0
Dustin StanleyEntrepreneurAuthor Commented:
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.
0
Dale FyeOwner, Developing Solutions LLCCommented:
Checking for inactivity is relatively easy, take a look at the attached database.

When you open the database, frm1 opens, then opens frm_Inactive (I would normally open this as hidden, but for purpose of this demo, I left it visible), and then sets the focus back to one of the controls in frm1.

The one downside of this technique is that a user could actually change the record that they are in on the subform and as long as they don't change the control that has the focus, frm_Inactive would not record that as a change by the user.  For that reason, I've added a couple of lines of code to the current event of the subform, which will force frm_Inactive to reset the values and timer.  You would have to do this for the Current event of any form that is bound to a recordset, so I would probably advise moving that code to a standard code module so you would only need one line of code, rather than three.

However, the real challenge you have to deal with is what do you want to do when that time exceeds your desired timeout.  I've simply inserted a messagebox into the code to advise you that it has been more then 2 minutes since the form/control focus has changed. Since message boxes are dialogs, you cannot programmatically close them, so you might want to replace that with a form of your own, which you can close at will, or if the user doesn't respond within a specified period of time.  You might want to also want to allow the user to click on a "reset" button to restart the inactive timer.

If you want to force the user out of your application due to lack of action, you will have to elegantly close any open forms, and add some logic to determine whether you want to save unsaved forms or not.  When I do this, I loop backwards through the Forms collection, check to see whether the form is dirty and if so, check to see whether the current entries meet the requirements for saving the current record.  I do this by including a PUBLIC function in each form called PassesChecks.  I can call this function for each open form with syntax like:
If forms("frmName").Dirty = false then
    'do nothing
Else 
    AllowSave = Forms("frmName").PassesChecks
    if AllowSave then 
        Forms("frmName").Dirty = false
    Else
        Forms("frmName").Undo
    end if
end if

docmd.close acform, forms("frmName")

Open in new window


HTH
Dale
Inactive.accdb
0
PatHartmanCommented:
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.
0
Dustin StanleyEntrepreneurAuthor Commented:
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.
0
Dustin StanleyEntrepreneurAuthor Commented:
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

Open in new window

0
Dale FyeOwner, Developing Solutions LLCCommented:
Dustin,

You are on the right track.  My PassesChecks functions look something like:

Private Function PassesChecks(Optional DisplayMessage as Boolean = False) as Boolean

    Dim intMissing as integer 
    Dim strMsg as string

    if Trim(me.txt_Field1 & "") = "" Then
        strMsg = iif(strMsg <> "", vbcrlf, "") & "Field1"
        intMissing = intMissing + 1
    end if

    if Trim(me.txt_Field2 & "") = "" Then
        strMsg = iif(strMsg <> "", vbcrlf, "") & "Field2"
        intMissing = intMissing + 1
    End if

    if Trim(me.txt_Field3 & "") = "" Then
        strMsg = iif(strMsg <> "", vbcrlf, "") & "Field3"
        intMissing = intMissing + 1
    End if

    if strMsg = "" Then 
        PassesChecks = true
    Else
        PassesChecks = false
        If DisplayMessage = true then
            strMsg = "The folllowing field" & iif(intMissing = 1, " is ", "s are ") & " required " & vbcrlf _
                         & "and" & iif(intMissing = 1, " is ", " are ") & "missing data:" & vbcrlf & strMsg
            msgbox strMsg, vbokonly, "PassesChecks failed"
        End if
    End if
                     
End Function

Open in new window


I call these functions from within the BeforeUpdate event of each form, like:
Private Sub Form_BeforeUpdate(Cancel as integer)

    Cancel = NOT PassesChecks(True)
    if Cancel = false then
        me!ModifiedBy = fosusername()
        me!ModifiedDT = Now()
    End if

End sub

Open in new window

which will cancel the update if any of the required controls are not filled in, and will display the warning message.  But calling it from outside the form, you would not need to pass in the DisplayMessage and it would simply return a True or False value to the code that is looping through the forms collection.
0
PatHartmanCommented:
Thanks for the clarification Dustin.  There is a big difference between concurrent and serial users.
0
Dustin StanleyEntrepreneurAuthor Commented:
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

Open 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
      

Open 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

Open 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
      

Open in new window

0
Dustin StanleyEntrepreneurAuthor Commented:
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!
0
Dale FyeOwner, Developing Solutions LLCCommented:
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
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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.
1
Dale FyeOwner, Developing Solutions LLCCommented:
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.
0
Dustin StanleyEntrepreneurAuthor Commented:
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.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<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.
0
Dustin StanleyEntrepreneurAuthor Commented:
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)?
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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

Open 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

Open 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.
0
Dustin StanleyEntrepreneurAuthor Commented:
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

Open in new window



Thanks a ton for the help and ideas. It means a lot and is extremely helpful. It gets me thinking.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.