Auto Shut Down

Hi

I want to implement an auto shut down in Ms Access if certain conditions are met as below:

If datediff("n", dtLastUpdated, Now) > 3 Then
DoCmd.Quit
Exit sub
ElseIf datediff("n",dtLastUpdated,Now)<> 3 Then
DoCmd.Quit = Cancel
Exit Sub
End If

(1) I keep on get an error debug error, how best should I do it

The below code I have made some change because it was giving some errors on the following:
(a) User undefined set ctrl = frm.ActiveControl ( So I changed Static Control as Control to Static Ctrl as Control)
(b) Again it appears like this part require the form controls to be active for the code to work. But there are situations where the users have not opened a single form but are just on the switch board, then what happens???????
(c) I have also tried to add on top the Static rptCount as int with Static rpt as Report , but still no avail
(d) Where there is int , I have also put Integer but still nothing seams to be working

(e) The problem is around the last part of the code:

'Check to see if use is on same form
'If not, save form and exit because there has been activity
if (frm Is Nothing) then
set frm = Screen.ActiveForm
dtLastUpdated = Now()
Exit Sub
Elseif frm.Name <> Screen.ActiveForm,Name Then
set frm = Screen.ActiveForm
dtLastUpdated = Now()
Exit Sub
End If

'Now check to see if they are on the same control
if (ctrl is nothing) Then
set ctrl = frm.ActiveControl
dtLastUpdated = Now()
exit sub
elseif ctrl.name <> frm.ActiveControl.name Then
set ctrl = frm.ActiveControl
dtLastUpdated = Now()
Exit Sub
End If

That is where it fails to execute properly , surprisingly , when you compile after changing Control to Ctrl it does compile very well , but executing it fails.

Private Sub Form_Timer()

Static frm as form
Static frmCount as int
Static rptCount as int
Static Control as Control
Static dtLastUpdated as date

'Check to see whether the number of open forms or reports has changed
if forms.Count <> frmCount then
frmCount = forms.Count
dtLastUpdated = Now()
Exit Sub
elseif reports.Count <> rptCount then
rptCount = reports.Count
dtLastUpdated = Now()
Exit sub
End If

'Check to see if use is on same form
'If not, save form and exit because there has been activity
if (frm Is Nothing) then
set frm = Screen.ActiveForm
dtLastUpdated = Now()
Exit Sub
Elseif frm.Name <> Screen.ActiveForm,Name Then
set frm = Screen.ActiveForm
dtLastUpdated = Now()
Exit Sub
End If

'Now check to see if they are on the same control
if (ctrl is nothing) Then
set ctrl = frm.ActiveControl
dtLastUpdated = Now()
exit sub
elseif ctrl.name <> frm.ActiveControl.name Then
set ctrl = frm.ActiveControl
dtLastUpdated = Now()
Exit Sub
End If

Kindly try it and see where I'm making a mistake , otherwise the code is very code , many thanks to owner

Regards

Chris

Scott McDaniel (EE MVE )

First: Please don't "stack" your questions. You've posed 2 very different issues in the same question, and many Experts simply will not participate in these types of questions. You stand a MUCH better chance of resolution if you post these issue separately.

This code:

If datediff("n", dtLastUpdated, Now) > 3 Then
DoCmd.Quit
Exit sub
ElseIf datediff("n",dtLastUpdated,Now)<> 3 Then
DoCmd.Quit = Cancel
Exit Sub
End If

doesn't really make sense, and there is no Cancel argument for DoCmd.Quit. Can you explain exactly what conditions would cause the application to Quit? You're comparing dtLastUpdated to Now, but you the determine if it is either (a) greater than 3 or (b) anything other than 3. From that logic, you'd just want it to be EXACTLY 3:

If datediff("n", dtLastUpdated, Now) <> 3 Then
DoCmd.Quit
End If

Next:

"Control" is a reserved word in Access, so I'm not surprised Access chokes on this line:

Static Control as Control

That should be Static ctrl As Control, as you mentioned.

What exactly does "executing it fails" mean? Does it run, but not do what you expect? Or does it error out?

Fabrice Lambert

Can you explain what you want to achieve with your timer ?

A solution build around the observer design pattern might be more appropriate than a timer driven one.

Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.Sc

ASKER

Okay how do I implement the shutdown if the condition (If datediff("n", dtLastUpdated, Now) > 3) is met or is not met????????????????

Regards

Chris

Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.Sc

ASKER

When there is no activity after 3 minutes then the system should shut down automatically but if there is still some activity it should not shut down.

Regards

Chris

Fabrice Lambert

what about simply logging whenever an event occur (click / open / close / change ect ect) the date / time into a simple table (with a single field: LastTime of type datetime), then your timer event will only have to retrieve the single record, check for timeout and close if needed ?

    '// call this whenever an interresting event occur
Public sub updateLastActivity()
    Dim db As DAO.Database
    Set db = CurrentDb

    Dim rs As DAO.Recordset
    Set rs = db.TableDefs("timerLog").OpenRecordset(dbOpenDynaset)

    If (rs.BOF and rs.EOF) Then
        rs.AddNew    '// recordset is empty, add a row
    Else
        rs.Edit            '// recordset isn't empty, edit the row
    End If
    rs.Fields("LastTime").Value = now
    rs.Update
    Set rs = Nothing
    Set db = Nothing
End Sub

Open in new window

Private Sub Form_Timer()
    Dim db As DAO.Database
    Set db = CurrentDb

    Dim rs As DAO.Recordset
    Set rs = db.TableDefs("timerLog").OpenRecordset(dbOpenSnapshot)
    
    Dim lastTime As Date
    lastTime = rs.Fields("LastTime").Value
    rs.Close
    Set rs = Nothing
    Set db = Nothing
    If(datediff("n", lastTime, now) > 3) Then
        Application.Quit
    End If
End Sub

Open in new window

Dale Fye

to be honest, I wrote that original code in my iPad, so I could not test it.

Check out the attached database. Open form "frm_AutoClose". It has a textbox which keeps track of the active form or control on a form, and does the countdown for you, in 10 second intervals.

Once that form is open, open one or two of the other forms and move your cursor from among the forms and controls.

Finally, just let it sit there and count down the 3 minutes, and watch it close all of the open forms. As I mentioned in the previous post thread, if you use subforms, you might have to modify the section of the code which closes out the forms to undo those subforms if they have the focus.
AutoClose.accdb

Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.Sc

ASKER

Okay

I have now fixed the code and it works very well see below:

Private Sub Form_Timer()
Static frm As Form
Static frmCount As Integer
Static rpt As Report
Static rptCount As Integer
Static dtLastUpdated As Date
If Forms.Count <> frmCount Then
frmCount = Forms.Count
dtLastUpdated = Now()
Exit Sub
ElseIf Reports.Count <> rptCount Then
rptCount = Reports.Count
dtLastUpdated = Now()
Exit Sub
End If
If DateDiff("n", dtLastUpdated, Now) > 20 Then
DoCmd.Quit
Exit Sub
End If
End Sub

For those who want to use it as well, please note the following:
(1) If you do validate your forms before update then you are safe you can you use the above code without problems
(2) If you do not validate your forms before saving data to your tables then your need to amend the shutdown procedures to (DoCmd.Quit acQuitSaveAll
(3) Finally if the intention of using this code is to do a clean backup, then you need to create also a surveillance form, the purpose of this un bond form is to check whether there are still some people in your network database, it will also help you to lock out those who may want to log in your database.
To get the this form , just buy the book called Real World Microsoft Access Database Protection and Security which was written by Garry Robinson, it has all the instructions how to create that form. I can guarantee you this book is good , its a must have it!

Regards

Chris

Dale Fye

i don't understand why you selected your solution as the solution to this question, when the code you used was taken from mine.

I do recommend that you look at the example database that I provided because just checking that the number of forms or reports open is not the solution to your Auto Close issue; that is only the first and easiest part. Your users will be really upset when they are entering data, moving from control to control, or between numerous different records on a single form for more than 3 minutes and you automatically close the application on them.

Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.Sc

ASKER

Sorry Sir;

First I would like to thank you for the code but on its own it require the following to work properly:

(1) All forms must be validated by a save button before being accepted in the tables , this is the correct analysis unless one understand this way then the code can be saving incomplete record, which means that the administrator will have a tough job deleting those incomplete record. That is the reason why it has to be done this way .

(2) I have also provided another option for those who may want to be saving incomplete records by simply using this (DoCmd.Quit acQuitSaveAll

However, I have truly acknowledged your effort its actually job well done , I have only taken the above measures to suite different requirements.Kindly also try your original code it will fail to work without some amendments , I still feel the testing was not done, if it was you could have definitely seen the current errors.

Regards

Chris

Scott McDaniel (EE MVE )

but on its own it require the following to work properly:

Our role is not to give you perfectly functioning code that exactly fits your requirements, but instead to help you arrive at the solution. If one of more Expert comments helps you arrive at that solution - and Dale's suggestion certainly did - then you should accept those comments as Assisted Solutions.

Remember that we are not sitting in front of your machine, and that we don't have all of your requirements, so we cannot know the "perfect" code to meet all those requirements. The best we can do is answer the questions asked.

Also, I agree with Dale 100% regarding the methods you're using for shutdown. As a user, I'd be significantly frustrated if my form died after have that 21 minute meeting with my boss ...

Finally, this command:

DoCmd.Quit acQuitSaveAll

Is intended to save all Objects, not data. Unless you're allowing users to design forms, then you're not accomplishing anything in the way of saving data with that call.

Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.Sc

ASKER

Thank you so much Scott , well received and noted.

Regards

Chris

ASKER CERTIFIED SOLUTION

Dale Fye

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial