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

Hello All!

I have a new issue that I need to solve with automatically closing my database when idle. I was very excited to have originally gotten it to function. I created a idle timing form that triggers another form to open after 4.5 minutes. the new 'CountDown' form gives the user 30 seconds to respond before closing the database out (Total 5 Minutes). All was great up to this point. When a co-worker came in, I showed him the new feature and discovered that when I had an active query open the 'DetectIdle' form became unresponsive. So basically it only detects forms and not queries. I need to find a way to include queries in the 'DetectIdle' form coding that I have. Is this possible? Users often leave the database open on the queries when searching information and walk away preventing issues with other users when accessing the database. I appreciate any assistance you all can provide me with.
Thank you!

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


Dim ActivecontrolName As String
Dim ActiveFormName As String
Dim ExpiredMinutes

On Error Resume Next

ActivecontrolName = Screen.ActiveControl.Name
ActiveFormName = Screen.ActiveForm.Name
Me.txtActiveForm = ActiveFormName



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

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

End If

End Sub

Open in new window

Michael RoseberrySenior Electronics TechnicianAsked:
Who is Participating?
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.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You can check for Screen.ActiveDatasheet:

Static OldDataSheetName As String

Dim ActiveDataSheet As String
ActiveDataSheet = Screen.ActiveDatasheet.Name

If (OldControlName = "") Or (OldFormName = "") _
    Or (ActiveFormName <> OldFormName) _
    Or (ActivecontrolName <> OldControlName)
    Or (ActiveDataSheetName <> OldDataSheetName)
0
tuviCommented:
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.
0
Michael RoseberrySenior Electronics TechnicianAuthor Commented:
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

Open in new window

0
Powerful Yet Easy-to-Use Network Monitoring

Identify excessive bandwidth utilization or unexpected application traffic with SolarWinds Bandwidth Analyzer Pack.

PatHartmanCommented:
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.
0
Michael RoseberrySenior Electronics TechnicianAuthor Commented:
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.
0
Michael RoseberrySenior Electronics TechnicianAuthor Commented:
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. :)
0
tuviCommented:
You forgot to set OldDataSheetName = ActiveDataSheet just before the line ExpiredTime = 0.

Anyway, you don't need that many variables. One is enough.

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

Open in new window

0
Michael RoseberrySenior Electronics TechnicianAuthor Commented:
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

Open in new window

0
Michael RoseberrySenior Electronics TechnicianAuthor Commented:
ah missed it again. will try in a minute
0
tuviCommented:
This works on my computer:

Private Sub Form_Timer()
   Static OldName As String
   Static ExpiredTime As Long
   
   Dim ActiveName As String
   
   On Error Resume Next
   
   ActiveName = Screen.ActiveDatasheet.Name
   ActiveName = Screen.ActiveForm.Name
   ActiveName = Screen.ActiveReport.Name
   
   If OldName = ActiveName Then
      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

Open in new window

0
Michael RoseberrySenior Electronics TechnicianAuthor Commented:
Yay! that did the trick! PerfectlyThank you Tuvi greatly for your help.  This really means alot :)
0
tuviCommented:
Glad to be of help. :)
0
Michael RoseberrySenior Electronics TechnicianAuthor Commented:
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...
0
Michael RoseberrySenior Electronics TechnicianAuthor Commented:
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.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
0
Michael RoseberrySenior Electronics TechnicianAuthor Commented:
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.
0
Michael RoseberrySenior Electronics TechnicianAuthor Commented:
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.
0
Michael RoseberrySenior Electronics TechnicianAuthor Commented:
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

Open in new window

0
Michael RoseberrySenior Electronics TechnicianAuthor Commented:
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.
0
Michael RoseberrySenior Electronics TechnicianAuthor Commented:
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

Open in new window

0
PatHartmanCommented:
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.
0
tuviCommented:
Your final codes differ from mine in the only global variable ExpiredMinutes that I eliminated from my codes (because of lack of info) I thought that was redundant.
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
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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 ...
0
Michael RoseberrySenior Electronics TechnicianAuthor Commented:
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
0
Michael RoseberrySenior Electronics TechnicianAuthor Commented:
I Updated the solutions etc... Hope it went through correctly :)
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
Databases

From novice to tech pro — start learning today.