Automatically close database when idle (ActiveForms work but not ActiveQueries)

You also have to check for Reports if you have any, i.e. you have to check for Screen.ActiveReport. Also, I don't believe you have to check for the ActiveControl because the control is in one of the forms and you already check for the ActiveForm.

Something is wrong. Still won't work for an Active Query, I have tried a few different combinations of coding. Here is the Last:

Private Sub Form_Timer()
Static OldControlName As String
Static OldFormName As String
Static OldDataSheetName As String
Static ExpiredTime


Dim ActivecontrolName As String
Dim ActiveFormName As String
Dim ActiveDataSheet As String
Dim ExpiredMinutes

On Error Resume Next

ActivecontrolName = Screen.ActiveControl.Name
ActiveFormName = Screen.ActiveForm.Name
ActiveDataSheet = Screen.ActiveDataSheet.Name
Me.txtActiveForm = ActiveFormName
Me.txtActiveDataSheet = ActiveDataSheetName


If (OldControlName = "") Or (OldFormName = "") _
    Or (ActiveFormName <> OldFormName) _
    Or (ActivecontrolName <> OldControlName) _
    Or (ActiveDataSheetName <> OldDataSheetName) Then
    OldControlName = ActivecontrolName
    OldFormName = ActiveFormName
    ExpiredTime = 0
Else
    
    ExpiredTime = ExpiredTime + Me.TimerInterval

End If
    ExpiredMinutes = (ExpiredTime / 1000) / 60
    Me.txtIdleTime = ExpiredMinutes
    
If ExpiredMinutes >= 4.5 Then
    ExpiredTime = 0
    'MsgBox "There was no activity in the last 10 minutes! Program will close"
    
    DoCmd.OpenForm "CountDown"
    
    

End If
End Sub

Select allOpen in new window

You may be able to resolve this with code but users should NEVER be able to open queries directly.  All interaction should be via forms and reports.  You can use forms in DS view if users like the look of a query.

The biggest danger of presenting raw query data to users is that unless you have done something to cause it to be not updateable, users can simply change or delete data in a query without any validation so you have completely lost control of the data.

Ya, I have approved them making changes to the queries when necessary. Certain situations call for it. But... I discovered that I can convert my queries into forms. I have a navigation menu therefore I can force them to now open queries. Just need to convert them all now. Hopefully changes can still be made to them if necessary if I can’t solve detecting active queries for the auto close timer.

Our technicians need to also sort data in the queries to search for upcoming jobs and make additional notes if jobs that are due cannot be performed. Each technician has gone through appropriate training of the database that I setup for them. My only real issue is them leaving the database open and other users logging into their separate accounts and overwriting the data of others or just not being able to access at all because someone else left it open somewhere. I’m sure there was probably a better way for me to have set the database up in the first place but it has evolved so much since it’s original intent. It’s now a one stop shop and still growing. We have a lot more plans for it. I just released version 2 of it on the 1st to keep them out out things that they don’t need to be into. And issued user logins. I need to figure the activity log next. Then barcode scanning. Oh also I have two separate forms where they enter the serial numbers. One is a maintenance log the other is a Preventative Maintenance log, this PM log is tied to an asset manager to keep track of main components. But the Maintenance log has these same components that get swapped out at times. I’d like to tie these in together so that swaps update the asset manager. Lots of plans and things to figure out all while performing my regular duties as a Sr. Technician. Pat you have been the most amazing help to me getting me through so much of this. I am learning so much and I sincerely try to do my best before reaching out. So far I have only had one question that I wasn’t able to solve because I don’t think there is a real solution due to the way my database is setup.

Sorry for the ramble but this database has really turned into a passion for me, it’s my baby now. :)

Hi Tuvi,
I have just tried that code. It attempts to run on both the Form and the Query but it freezes. It goes from 0 to 0.01666666667 then stops counting.

Private Sub Form_Timer()
Static OldName As String
Dim ActiveName As String

On Error Resume Next
   
ActiveName = Screen.ActiveDataSheet.Name
ActiveName = Screen.ActiveForm.Name
ActiveName = Screen.ActiveReport.Name

If OldName <> ActiveName Then
   OldName = ActiveName
   ExpiredTime = 0
Else
   ExpiredTime = ExpiredTime + Me.TimerInterval
End If
    ExpiredMinutes = (ExpiredTime / 1000) / 60
    Me.txtIdleTime = ExpiredMinutes
    
If ExpiredMinutes >= 4.5 Then
    ExpiredTime = 0
    'MsgBox "There was no activity in the last 10 minutes! Program will close"
    
    DoCmd.OpenForm "CountDown"
    
    

End If
End Sub

Select allOpen in new window

ah missed it again. will try in a minute

Yay! that did the trick! PerfectlyThank you Tuvi greatly for your help.  This really means alot :)

Glad to be of help. :)

Found a glitch. The query doesn't reset the counter when the user is active inside it. It will time out while in use. your code has given me progress though to work with. I'll keep playing with it...

Sorry but neither does the Form. The code I had before reset the counter as the user navigated through the Form. Now It does not. It just keeps counting down until shutdown. so it's not detecting idle, its just counting down to close. The only time it resets is when the user changes to another Form or Query. I need it to reset as it was before as they change between fields.

Queries don't have event sinks, so there's no way to control what happens in them.

Your original code also checked the ActiveControl, which I think was the trigger for activity within the Form or Report. The new function removes that. I personally believe you should put back in those checks, and reinstate the individual checks, for no other reason than legibility.

Also, you won't be able to capture those types of events in a Query, since there's no event model. Best you can do is determine if a user switches from the Query to something else. You could create a form that would provide the user with the tools needed to filter data, if that's all the users are doing with those queries. That could be a big task, of course.

Ya, I've been playing with it. I have come to the same conclusion that the Query won't reset the counter. I have already gotten the Form to reset again a few minutes ago by adding the control back. now I am trying to get the Detect Idle to display the Active form title in the DetectIdle Time Form field that I have using    Me.txtActiveForm = ActiveFormName but it locks up the counter (But I guess that's not really important). I also licked how before I could use 4.5 for 4 1/2 minutes instead of the milliseconds. I need to play with that also because it is disabling the counter when I use my old code.

I may attempt to use the query to form method as well to see which I like better. I'll get the Pro's and Con's from each.

I had it working to reset the counter on the forms but now I messed it up and can't get it back :/ so frustrated...

Private Sub Form_Timer()

Static OldName As String
Static OldControlName As String
Static ExpiredTime As String
   
   
Dim ActiveName As String
Dim ActivecontrolName As String
   
On Error Resume Next
   
    ActiveName = Screen.ActiveDataSheet.Name
    ActiveName = Screen.ActiveForm.Name
    ActiveName = Screen.ActiveReport.Name
    ActivecontrolName = Screen.ActiveControl.Name
   
If OldName = ActiveName Then
    OldControlName = ActivecontrolName
    ExpiredTime = ExpiredTime + TimerInterval
   
Else
    OldName = ActiveName
    ExpiredTime = 0
End If
   
    txtIdleTime = ExpiredTime / 60000
   
   ' 270000 milliseconds is 4.5 minutes
    If ExpiredTime > 270000 Then
        MsgBox "Idle too long"
        ExpiredTime = 0
        txtIdleTime = 0
    End If
    
End Sub

Select allOpen in new window

Ok I got the Control to Work. Just Need to get the Internal Timer to work when I enter 4.5 instead of 270000 Milliseconds for 4 1/2 minutes.

Ok. I finally got it working. DetectIdleTime Form now also displays the Active Form, Active Field and Elapsed time. The form also resets between Query, Form and Form Fields. Timer is set for 4.5 minutes then opens a popup window for 30 seconds which gives options to 'Keep Database Open' or 'Close Database' before closing the database automatically. (Total of 5 minutes of inactivity)

Here is the final code:

Private Sub Form_Timer()

Static OldName As String
Static OldControlName As String
Static ExpiredTime As String
   
   
Dim ActiveName As String
Dim ActivecontrolName As String
   
On Error Resume Next
   
    ActiveName = Screen.ActiveDataSheet.Name
    ActiveFormName = Screen.ActiveForm.Name
    ActiveName = Screen.ActiveForm.Name
    ActiveName = Screen.ActiveReport.Name
    ActiveName = Screen.ActiveControl.Name
Me.txtActiveForm = ActiveFormName
Me.txtActiveField = ActiveName

If OldName = ActiveName Then
    OldControlName = ActivecontrolName
    ExpiredTime = ExpiredTime + TimerInterval
   
Else
    OldName = ActiveName
    ExpiredTime = 0
End If
    ExpiredMinutes = (ExpiredTime / 1000) / 60
    Me.txtIdleTime = ExpiredMinutes
   
If ExpiredMinutes >= 4.5 Then
    ExpiredTime = 0
    txtIdleTime = 0
        
    DoCmd.OpenForm "CountDown"
    
End If
End Sub

Select allOpen in new window

You're welcome Michael.

Our technicians need to also sort data in the queries to search for upcoming jobs and make additional notes if jobs that are due cannot be performed. Each technician has gone through appropriate training of the database that I setup for them. My only real issue is them leaving the database open and other users logging into their separate accounts and overwriting the data of others

As we've already mentioned, you can't control anything if you allow the user direct access to the data.  Training goes only so far.  You will have much more control if you use forms.  Using forms will allow you to control who updates what and also restrict the updates to valid values.

You've been given a band aid to solve your question but you still have a problem.  Do not let it become infected.

Are you sure you intended to accept ONLY your comment as a solution? I believe that several Expert comments assisted you in resolving your issue ...

Sorry, No I didn’t mean to. I tried to update it from my phone. I will change it when I get back to the computer to make sure it’s done correctly. Thanks for letting me know

I Updated the solutions etc... Hope it went through correctly :)