Run a Macro every 5 minutes automatically

[GonzaloYagueSunol]

Dear all,



I am using access 2013, I have an update query called "CurrentDate" and it is run by a macro called "TimetoTime". I want to run this macro automatically every 5 minutes. After researching different forums and tutorials, I have created a form with the following code:

------------------------------------------------------
Private Sub Form_Open(Cancel As Integer)
Me.TimerInterval = 300000
End Sub


Private Sub Form_Timer()
DoCmd.RunMacro "TimetoTime"
End Sub

------------------------------------------------

Unfortunately it does not work. I would really appreciate if anyone could help me solve this problem.

Thank you very much in advance.

Best regards,

Gonzalo

[pbaldy]

Has code been enabled for the db or is it in a trusted location?

[GonzaloYagueSunol]

It has been enabled for the db. There is not any trusted location

[pbaldy]

Have you tried setting the timer interval in design view and not using the code to set it? Are you sure the process isn't running, rather than the query not updating anything? Can you attach the db here?

[GonzaloYagueSunol]

Good Morning Paul,

I do apologize for replying you late.

As you may see, the CurrentDate query should update every five minutes the "Day Difference","ID Expiry Date" and "Permission" fields through the "TimetoTime" macro. I have tried to run this query manually and it works well.

Thank you very much again for your time and help.

Best regards,

Gonzalo

[pbaldy]

I get errors (no Form2, may be corrupted), but what good would it do to run the query every 5 minutes? Since it uses date, it would only change anything after midnight. Did you mean to use Now() and have times?

[GonzaloYagueSunol]

Hi Paul,

With the help of the query (run by the "TimetoTime" macro) I want to achieve this:

- The "CurrentDate" must be updated with the current date and I do not need the time. That's why I use Date() function.
- "Day Difference" is calculated as [ID Expiry Date]-[Current Date]
- "Permission" field is Green, Amber or Red based on the following pattern:

if [Day Difference]<0, [Permission]=Red
if 0<=[Day Difference]<15, [Permission]= Amber
if [Day Difference]>=15, [Permission]=Green

In order to calculate [Permission] and [Day Difference] as calculated fields, I would need to work with a .accdb file but I cannot do it because of the requirements of the project. For that reason, I work with a .mdb file. Although most of the times [Current Date] will not change (only at midnight), [Day Difference] and [Permission] fields of a potential future new record will be calculated automatically in a short period of time (in this case 5 minutes but it could be even less) without closing and opening the database.

Thank you very much again for your help and time.

Best regards,

Gonzalo

[pbaldy]

Like I said, I got errors but I was able to get rid of all code, set the interval in design view and call the macro in the event property directly and the macro ran on schedule.

[GonzaloYagueSunol]

Good morning Paul.

Thanks to this article, I have solved my problem.

http://www.justanswer.com/microsoft-...query-run.html

Thank you very much for your time and your help.

Best regards,

Gonzalo

[pbaldy]

No problem, I'm glad you got it sorted.