Link to home
Start Free TrialLog in
Avatar of frimy
frimyFlag for United States of America

asked on

Access On Timer

Happy new year All,

I created a small form and in the On timer event I added the following code to close the database at 12:00:00 automatically.
Time Interval = 1000
And added into the autoexec macro to open as a hidden form.
The code works fine.
But if the form is working in the  background I cant delete any records and have other issues.
Please let me know what I can do.
Thanks

Dim wTime As Date

    wTime = Format("12:00:00 AM", "Short Time")
    Me.Clock = Time
    If Me.Clock = wTime Then DoCmd.Quit acQuitSaveAll
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

what is your purpose of making the app to shutdown at 12:00 AM?

instead of having it close automatically, just present a message to save and close the app at 12:00 AM
Avatar of frimy

ASKER

If the users would listen it would't be a problem.
I want to make sure the system is shutdown when it's not being used.
ohh.. try the method from this link ShutDown On Idle
I'm actually very interested in your question, as I'm thinking of doing something the same.  For example, before systems are shutdown for patching/maint. tasks.  I'd also prefer to have to no locks on the DB during the over night backup.  Users really do leave stuff open all weekend if you let them.

The "background" / hidden form - is it  modal = True or False ?   (just a wild guess)

Here are three links I would start to read up on:

http://accessdatabasetutorial.com/2014/04/30/microsoft-access-form-designs-using-vba-with-on-timer-event/

https://msdn.microsoft.com/en-us/library/office/ff192530.aspx
https://msdn.microsoft.com/en-us/library/office/ff836371.aspx
Avatar of frimy

ASKER

I have tried already to use one method from www.peterssoftware.com.
But I had the same problem with deleting records.
So figured to try something very simple as I did.
It looks like if a timer is running in the background then something gets locked.
did you try the ShutDown On Idle?
Avatar of frimy

ASKER

yes
You have it checking once per second that it is not midnight.
That's not really very good
I can't delete any records and have other issues.
Timer bleeds into all VBA code.
Try editing code while a Timer event is firing :o
The debugging is set off to a heartbeat.
Very annoying.

So, what you really need is to have that form calculate the time between opening and midnight, and set the TimerInterval to fire at around 11:55 PM, and THEN to fire once per second until it closes
SOLUTION
Avatar of Nick67
Nick67
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Nick, that's a great idea!  I've always resorted to having the timer run once a minute to check the time.  From now on I'll use a variation of your method:
Private Sub Form_Open(Cancel As Integer)
    Const timeToRun As Date = #11:00:00 PM#
    Me.TimerInterval = DateDiff("s", Time(), timeToRun)  * 1000

End Sub

Open in new window

Ron
@IrogSinta
Thank you,
And thank you for making the code much more elegant

@frimy
Dumb question: if you tuen off the timer on your hidden dummy form all the problems, stop?
So it is certain that the timer is the culprit?
Avatar of frimy

ASKER

Yes, if I close the form the problem stops.

I already changed the code to our method and it works beautiful.
Can you explain a little what you did with the time Interval and how can I change to a different time zone like 10, 11 o'clock.
Also, since you're a pro, maybe you can figure out how to check, if the users don't do any activity for a period of time to quit the DB.
Thanks
You are the best!
The TimerInterval property is how frequently (in milliseconds) the Timer event occurs.
You ideally only want your timer event to happen once -- around midnight to close the application.

So both my code and @IrogSinta's code calculate the difference in time between the time the form opens and the time you want the event to happen.
You set the TimerInterval to that value in millseconds, and you're golden.

Private Sub Form_Open(Cancel As Integer)
    Const timeToRun As Date = #11:00:00 PM#
    Me.TimerInterval = DateDiff("s", Time(), timeToRun)  * 1000
End Sub


@IrogSinta hardcoded the value in bold to be 11:00 PM
If what you want is to have your app close at 11:59 PM Eastern Standard Time, I am sure there are ways to retrieve the machine's time zone and adjust that hard-coded date.

But that should really be a new question :)
<maybe you can figure out how to check, if the users don't do any activity for a period of time to quit the DB.>

see http:#a41410612
Umm...
Since we just got rid of a Timer event running on one second intervals what sense does suggesting adding a form and event with
1. Create a blank form that is not based on any table or query and name it DetectIdleTime.
2. Set the following form properties:    OnTimer: [Event Procedure]
   TimerInterval: 1000
                                    
NOTE: The TimerInterval setting indicates how often (in milliseconds) the application checks for user inactivity. A setting of 1000 equals 1 second.
make?

The idea behind it is to log active form and active control once per second, and if they do not change for a set period of time, declare the app inactive.
But the timer event is what we want to get away from.
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
You had wondered about other time zones.
And I had need of a sample for my own purposes
Adjust these two values

Const timeToRun As Date = #6:00:00 PM#
Const DelayFromLastActivity As Long = 5

To adjust when the app starts to look to auto-close and how many minutes of inactivity after that time will cause it to close.

Flanged in are the API timezone functions so you could specify a time in GMT and have the app do the action at the appropriate local time (I haven't done the converting, you can if you want that functionality)

Nick67
AutoClose.mdb