Application.Ontime in MS Access

[ions]

Dear MS Access Expert.

I am aware that a form has the On Timer event in conjunction with the Time Interval property.

I currently want to run two events. one at 3 minutes and one at 5 minutes.

I also want to run a third event conditionally (run in one minute if this is true).

In Excel I would do it with Appliction.Ontime method.

How can I accomplish the above in MS Access.

I appreciate your feedback and input.

Thanks.

 

[DCrake]

Set your Timer Interval to 1 minute 60000 (1000 = 1 second)

Then on your Form.Timer() event place an case statement

Dim curMin As Interger

curMin = Val(Mid(Time(),4,3))

Select Case curMin
   Case 3,6,9,12,18,etc
      Do your 3 minute routine
   Case 5,10,15,20,etc
      Do your 5 minute routine
End Select

So each minute the form examines what is the current minute and if it matches any of your case statements the relevent routine is run. Make sure that your routines don't take longer than one min to run as you will end up flying up your own A**E.

David

 

[ions]

DCrake.

Thank you for your response.

The user starts the timer when they open the form. They can open the form at any time so the 5 minute interval could be at 7, 12, 17 on one workstation and 8,13, 18 on another etc....

Also there is also a conditional timer function. It is triggered when an error occurs this one is completely unpredictable.

I will think about your answer and try to see if I can incorporate it into the solution.
However I believe I need something like the OnTime function in Excel.

There must be a similar function in MS Access no?

Thank you.

 

[DJkarl]

DCrake.

Thank you for your response.

The user starts the timer when they open the form. They can open the form at any time so the 5 minute interval could be at 7, 12, 17 on one workstation and 8,13, 18 on another etc....

Also there is also a conditional timer function. It is triggered when an error occurs this one is completely unpredictable.

I will think about your answer and try to see if I can incorporate it into the solution.
However I believe I need something like the OnTime function in Excel.

There must be a similar function in MS Access no?

Thank you.

Access does not have a similar function to OnTime. You could probably write a function that would behave in a similar way, I think this would not be too easy. The quickest method might be to create some hidden forms, one with a three minute timer and one with a five minute timer, have your main form open each one, that way they are running in the background and firing off timer events. You might want to be careful if your procedures take a long time or are process instensive (like running a query), they might not allow both timer events to run simultaneously and if they are dependant on one another the results could be unpredictable at best.

 

[DCrake]

As commented earlier you have not indicated as to the nature of the calls you intend to do. If they affect the data in any way then I would suggest having one stand alone app that contains the timer routines and let this one do it independantly. running the same procedure simultainiously, even if it from two or more pcs could have distasturous effect on our data.

be warned.

 

[ions]

Hi Drake,

The function calls in MS Access are not time intensive but they rely on a time intensive process that occurs in MS Excel. I have two MS access tables linked to two Excel Files which are linked to two XML files. The XML refresh in Excel takes roughly 45 seconds to complete.

Required Timers in MS Access:

The first timer in MS Access is to refresh the Form that uses the above Mentioned MS Access Linked Tables. The Form refresh takes a maximum of 3 seconds and the form is to have a automatic refresh every 3 minutes (auto refresh.)

The second Timer will trigger a new refersh in 45 seconds if the original refresh (timer 1) throws an error due to File Locking issues.

The third Timer closes the Form if it is idle for 10 minutes or more.

I could use forms for each seperate timer. Timer 1 and Timer 3 would have forms that open when the Original Form opens. Timer 2 would have a form that opens only if the error is thrown. After the Error Refresh (triggered by timer 2 ) completes I would close Timer 2 Form.

All forms would be Hidden.

Doesn't sound effecient and I am shocked MS Access doesn't have something comparable to ONTIME.

 

[DJkarl]

Hi Drake,

The function calls in MS Access are not time intensive but they rely on a time intensive process that occurs in MS Excel. I have two MS access tables linked to two Excel Files which are linked to two XML files. The XML refresh in Excel takes roughly 45 seconds to complete.

Required Timers in MS Access:

The first timer in MS Access is to refresh the Form that uses the above Mentioned MS Access Linked Tables. The Form refresh takes a maximum of 3 seconds and the form is to have a automatic refresh every 3 minutes (auto refresh.)

The second Timer will trigger a new refersh in 45 seconds if the original refresh (timer 1) throws an error due to File Locking issues.

The third Timer closes the Form if it is idle for 10 minutes or more.

I could use forms for each seperate timer. Timer 1 and Timer 3 would have forms that open when the Original Form opens. Timer 2 would have a form that opens only if the error is thrown. After the Error Refresh (triggered by timer 2 ) completes I would close Timer 2 Form.

All forms would be Hidden.

Doesn't sound effecient and I am shocked MS Access doesn't have something comparable to ONTIME.

So you are linking to Excel files and refreshing a form, I guess my next question is what are you doing in Access that you could not do with an Excel form? If you kept everything in Excel you could use the OnTime function.

 

[DCrake]

DJKarl you beat me to it.

 

[ions]

The form is part of a comprehensive MS Access information system that the company uses.

The requirement was that the interface must be part of this MS Access System as the company is very comfortable with it.

Note: I am using Excel to Link to XML files because I couldn't find a solution in MS Access. I have a thread in regards to this on this forum.

Thank you for your feedback and advice.

It still bothers me that there is no comparable function to ONTIME in MS Access.

Someone must of created something?

Thanks.

 

[DCrake]

Access and Excel are two different applications with two different objectives. they have been improved laterally over various revisions in order to be able to communicate between themselves. However, they were never intended to be clones of each other.

This is life and if you are so dissapointed with the situation you could always write to Microsoft about this issue. No, I am not being cynical, people do write to them, and yes, they do respond.

David

 

[ions]

Thanks David.

I appreciate your help.

I will implement the timers using Hidden Forms.

Peter.