Timer for Macros

  • Thread starter Thread starter Rico
  • Start date Start date
R

Rico

I would like to solve an issue. I have two macros
that I would like to run automatically at various times
of the day. The first I want to run every 3 hours and the
second every morning at 9:00 am. I've been searching
everywhere for the correct coding to put in
the 'Condition' area. Please advise. Thanks in advance.

Rico
 
Rico,

As far as I know, to do this type of thing entirely within Access
requires the use of the On Timer event of a form which will be open at
all times (or at least at the time you want the macro to run). You
set the Timer Interval property of the form to a suitable value,
indicative of the degree of precision you need. For example, with
your 9:00am macro, if it is ok for it to start anytime between 9:00
and 9:10, then you would set the Timer Interval to 600000
(milliseconds). Whereas if it is imperative that it starts
immediately at 9:00:00 you would have to sert the timer to something
like 1000, but this has the disadvantage of a fair bit of processing
going on with little return. So, let's say we use the 10 minute
example, the Condition in the macro would be...
Time() Between #9:00:00 am# And #9:09:59 am#

With your 3-hourly macro, you would not need a macro Condition. Just
assign the macro on the timer event of another form, and set the Timer
Interval to 10800000.

- Steve Schapel, Microsoft Access MVP
 
Why not use a scheduling program like Windows Task
Scheduler and run the macros at the specified intervals.

Jim
 
Steve,

I always learn alot from your posts. That database you
recommended for storing all the newsgroup responses is
great. Since I teach a beginning Access class for the City
of Pittsburgh I distributed it to my students. It was well
recieved.

I like the idea of the Om Timer event and have used it but
unless you have the application on a stable PC wouldn't the
timer have to associated with the time of day. I have to
restart my PC a couple of times a day, so just restarting
the timer would not work. Can you associate the timer with
the PC clock to insure accuracy?

Thanks for all your advice

Eric Blitzer
-----Original Message-----
Rico,

As far as I know, to do this type of thing entirely within Access
requires the use of the On Timer event of a form which will be open at
all times (or at least at the time you want the macro to run). You
set the Timer Interval property of the form to a suitable value,
indicative of the degree of precision you need. For example, with
your 9:00am macro, if it is ok for it to start anytime between 9:00
and 9:10, then you would set the Timer Interval to 600000
(milliseconds). Whereas if it is imperative that it starts
immediately at 9:00:00 you would have to sert the timer to something
like 1000, but this has the disadvantage of a fair bit of processing
going on with little return. So, let's say we use the 10 minute
example, the Condition in the macro would be...
Time() Between #9:00:00 am# And #9:09:59 am#

With your 3-hourly macro, you would not need a macro Condition. Just
assign the macro on the timer event of another form, and set the Timer
Interval to 10800000.

- Steve Schapel, Microsoft Access MVP
Click to expand...
 
Steve is on vacation, so let me see if I can add to his assistance.

You could have your database code read the time and then subtract the
current time from the "target" time in order to get the number of
milliseconds, then put that number in the Timer value and continue as you
did previously.

You would use the DateDiff function to get the difference in the times, and
then convert that to milliseconds:

TimeDiffs = DateDiff("s", Now(), #9:00AM#)
If TimeDiffs < 0 Then TimeDiffs = 60 * 60 * 24 - TimeDiffs
MillisecondsTime = TimeDiffs * 1000


--
Ken Snell
<MS ACCESS MVP>

Eric Blitzer said:
Steve,

I always learn alot from your posts. That database you
recommended for storing all the newsgroup responses is
great. Since I teach a beginning Access class for the City
of Pittsburgh I distributed it to my students. It was well
recieved.

I like the idea of the Om Timer event and have used it but
unless you have the application on a stable PC wouldn't the
timer have to associated with the time of day. I have to
restart my PC a couple of times a day, so just restarting
the timer would not work. Can you associate the timer with
the PC clock to insure accuracy?

Thanks for all your advice

Eric Blitzer
Click to expand...
 
Back
Top