Tags: access, code, couple, database, delay, microsoft, mysql, oracle, run, running, seconds, sql, time, vba

time delay in vba?

On Database » Microsoft Access

12,444 words with 17 Comments; publish: Fri, 07 Dec 2007 19:15:00 GMT; (250125.00, « »)

how do i get vba to run some code then wait a couple of seconds before running the rest?

All Comments

Leave a comment...

  • 17 Comments
    • I have been using this recently since someone put me onto it. There are bodgy ways using VBA but they are processor intensive. This is an API call that stops processing of the thread:

      Private Declare Sub AppSleep Lib "kernel32" Alias "Sleep" (ByVal dwMilliseconds As Long)

      Public Sub PauseApp(PauseInSeconds As Long)

      Call AppSleep(PauseInSeconds * 1000)

      End Sub

      call it with:

      'My code doing stuff and now I want a pause for three seconds

      PauseApp 3

      'My code resumes doing stuff

      #1; Tue, 11 Dec 2007 20:54:00 GMT
    • I have been using this recently since someone put me onto it. There are bodgy ways using VBA but they are processor intensive. This is an API call that stops processing of the thread:

      Private Declare Sub AppSleep Lib "kernel32" Alias "Sleep" (ByVal dwMilliseconds As Long)

      Public Sub PauseApp(PauseInSeconds As Long)

      Call AppSleep(PauseInSeconds * 1000)

      End Sub

      call it with:

      'My code doing stuff and now I want a pause for three seconds

      PauseApp 3

      'My code resumes doing stuff

      But then again Poots, it stops all processing ... What if you want to do something else in that slice of time? :D Just being argumentative here ...

      What about that ol delay routine of mine that does the time compare ...

      #2; Tue, 11 Dec 2007 20:55:00 GMT
    • But then again Poots, it stops all processing ... What if you want to do something else in that slice of time? :D Just being argumentative here ...

      What about that ol delay routine of mine that does the time compare ...I think that it just stops processing of that thread... this is why I shouldn't make comments about things I don't really know - some smartie calls your bluff lol.

      I assume your code is a bit like (pseudo):

      Dim t as Single

      t = timer

      Do While t + NumberOfSeconds > Timer

      Loop?? If so - that is what I used to. Someone objected to that code and suggested the API call.

      See what you think - is your code different\ better?

      http://www.experts-exchange.com/Databases/MS_Access/Q_21876140.html

      #3; Tue, 11 Dec 2007 20:56:00 GMT
    • But then again Poots, it stops all processing ... What if you want to do something else in that slice of time? :D Just being argumentative here ...

      Ah - I see - like if the user started navigating the app. Hmmm - yes - a problem.

      #4; Tue, 11 Dec 2007 20:57:00 GMT
    • I think that it just stops processing of that thread... this is why I shouldn't make comments about things I don't really know - some smartie calls your bluff lol.

      I assume your code is a bit like (pseudo):

      Dim t as Single

      t = timer

      Do While t + NumberOfSeconds > Timer

      Loop?? If so - that is what I used to. Someone objected to that code and suggested the API call.

      See what you think - is your code different\ better?

      http://www.experts-exchange.com/Databases/MS_Access/Q_21876140.html

      Naw ... That code won't work cause the interpreter will optimize it away ... You have to do something like:

      Dim StartTime as Date

      StartTime = Now

      Do While DateDiff("s",StartTime,Now) < [# of seconds desired]

      Loop

      #5; Tue, 11 Dec 2007 20:58:00 GMT
    • you might want to include a "do events", or whatever its called to allow the rest of the applcations running, or the OS to get a look in on the processor -or has that been made redundant these days with XP SP2?
      #6; Tue, 11 Dec 2007 20:59:00 GMT
    • and here all this time I thought

      for x=1 to 10000:next x

      was the way to go. Turns out there's a harder way... ;)

      #7; Tue, 11 Dec 2007 21:00:00 GMT
    • that shoudl work, but the acxtualk dealy will vary depending on what machine you are running the app on - it will vary with processor/memory/network/other apps runiing

      MOwens siolutinb will actuall delay for a sepcified number of seconds

      #8; Tue, 11 Dec 2007 21:01:00 GMT
    • and here all this time I thought

      for x=1 to 10000:next x

      was the way to go. Turns out there's a harder way... ;)

      See what I posted for Pootle ... The interpreter will optimize this away ... No significant delay will occur (I tried this same thing myself long ago) ...

      #9; Tue, 11 Dec 2007 21:02:00 GMT
    • See what you think - is your code different\ better?

      http://www.experts-exchange.com/Databases/MS_Access/Q_21876140.html

      PF... your link points to a paid subscription site. Most of us can't see the solution. That site always comes up when you google a computer question.

      I've often wondered if it was legit. Why pay for something that you can

      probably find for free...

      #10; Tue, 11 Dec 2007 21:03:00 GMT
    • Now if you really want to take a simple concept and complicate it even more, use M Owen's concept and account for midnight. If you happen to pause just before midnight and then during the pause it becomes another day then you will pause for a day. I had an app that had to run all night and pause several times, so I wrote:

      Public Sub Pause(sinSeconds As Single)

      Dim sinStartTime As Single

      Dim sinFinishTime As Single

      'Get the current number of seconds since the beginning of the day

      sinStartTime = Timer

      sinFinishTime = sinStartTime + sinSeconds

      'Adjust for midnight

      If sinFinishTime > 86400 Then sinFinishTime = sinFinishTime - 86400

      Do Until Timer > sinFinishTime

      DoEvents

      Loop

      End Sub

      #11; Tue, 11 Dec 2007 21:04:00 GMT
    • another complicated way using timer (and adding the additional complication of a multi-use timer just for fun)

      dim switchTimer as integer 'formwide switch for multi-use timer

      private sub stuffToRunBeforePause()

      'your before code here

      switchTimer = 1 'use the timer to delay run of stuffToRunAfterPause

      me.timerinterval = 2000 'set 2 secs

      end sub

      private sub stuffToRunAfterPause()

      'your after code here

      end sub

      private sub form_timer()

      select case switchTimer

      case 1 'this is your pause

      me.timerinterval = 0

      stuffToRunAfterPause

      case 2

      'this is some other use of the timer

      case 3

      'and yet another use of the timer

      end select

      end sub

      ?? better than loops since the machine is still 'alive' during the pause.

      izy

      #12; Tue, 11 Dec 2007 21:05:00 GMT
    • Naw ... That code won't work cause the interpreter will optimize it away Ah - I see what you mean now. The timer solution is more like your solution than the For x = 0 to 1000. The number of loops processed per millisecond will not affect the time taken in the former though it will with the for x loop.

      PF... your link points to a paid subscription site. Most of us can't see the solution. That site always comes up when you google a computer question.

      I've often wondered if it was legit. Why pay for something that you can

      probably find for free...Oops - forgot you can't see it. It is free to "experts" (yours truly :D) which just means you earn enough points per month answering questions. Experts get unlimited points to spend so that's why I moonlight there. In any case - it is a decent site but yes - why pay when you can get the same for free?

      Repeated below:

      Im running a section of code in a modle and if my program encounters an error accessing a peice of data, I have it change a status lable to read "Data Access Failed Waiting 5 seconds before trying again..."

      I've go this all set so that it will only retry 3 times before giving up attempting to auto access the data.

      Whats the best way to setup a timer to wait for 5 seconds before continuing?

      CheersI use this:

      Public Sub PauseApp(PauseInSeconds As Single)

      Dim sngStart As Single

      sngStart = Timer

      Do While sngStart + PauseInSeconds > Timer

      Loop

      End Sub

      Don't know if anyone has anything better.

      HTH

      Hi Andy1,

      Private Declare Sub Sleep Lib "kernel32" Alias "Sleep" (ByVal dwMilliseconds As Long)

      In the declarations section then:

      Sleep 5000

      Where you need to wait. This causes the thread to sleep for a period of time. This may be a better solution as a tight loop could increase processor usage and not allow your other applications to run.

      If you use the tight loop approach, ensure that you also use DoEvents to release the processor inside the loop.

      Tim Cottee

      Yep - I like Tim's solution. Put it this way - I will be changing my function in my app. Always thought it was quick and dirty but never got round to checking out for something better.

      #13; Tue, 11 Dec 2007 21:06:00 GMT
    • These are some great solutions, but my problem still exists, no matter what kind of delay there is. The reason is this, my code is creating folders on the network, and is also copying, and editing Word documents, excel documents etc. While this is all going on, Access is running code that is not necessarily slow, but looks slow due to opening apps, and creating folders etc. Access looks as if it's locked up, but it's not. So I thought I would create a blank form to open saying "Please wait....bla bla bla". Problem is, the form never really displays because the code runs to fast. I tried to use the built-in "OnTimer" event to start my code after the form is loaded, but just learned the hard way that it is a reoccurring event. :)

      Does anyone know how to use the OnTimer event once? In other words, I want a function to run 2 seconds after my form is open. Not every 2 seconds.

      Creating a loop to count 2 seconds from now doesn't work because access is still running code, so that form never fully loads "graphically".

      #14; Tue, 11 Dec 2007 21:07:00 GMT
    • These are some great solutions, but my problem still exists, no matter what kind of delay there is. The reason is this, my code is creating folders on the network, and is also copying, and editing Word documents, excel documents etc. While this is all going on, Access is running code that is not necessarily slow, but looks slow due to opening apps, and creating folders etc. Access looks as if it's locked up, but it's not. So I thought I would create a blank form to open saying "Please wait....bla bla bla". Problem is, the form never really displays because the code runs to fast. I tried to use the built-in "OnTimer" event to start my code after the form is loaded, but just learned the hard way that it is a reoccurring event. :)

      Does anyone know how to use the OnTimer event once? In other words, I want a function to run 2 seconds after my form is open. Not every 2 seconds.

      Creating a loop to count 2 seconds from now doesn't work because access is still running code, so that form never fully loads "graphically".

      Set your Me.TimerInterval to 2000 and in the timer event method of the form do your thing and set the TimerInterval to 0

      #15; Tue, 11 Dec 2007 21:08:00 GMT
    • Set your Me.TimerInterval to 2000 and in the timer event method of the form do your thing and set the TimerInterval to 0

      AAAAHHHH!!! I just figured that out about 1 min before your response!

      (pat on back)

      Thank you! That is exactly what I was looking for!

      #16; Tue, 11 Dec 2007 21:09:00 GMT
    • so often ignored :( :(

      my suggestion is precisely a run-once timer (plus any number of other timers doing other things (not simultaneously (but you can easily fix the simultaneous issue using a bit pattern)))

      and as for the grafix stuff: i have often argued here that it makes sense to open an unbound greets form with .timerinterval = 1

      the timer does not tick until the form is fully (grafixly) loaded!!

      at that stage a run-once timer event turns off the timer (.timerinterval = 0) and loads the data whilst the happy user is enchanted by your opening screen (and fully convinced that your app is 'responsive' even tho it has done nothing so far).

      in your case, the initial (designtime and/or Form_Load) .timerinterval MUST be 1

      your Form_Timer() event then looks like

      private sub Form_Timer()

      select case me.timerinterval

      case 1 'you know you just loaded the grafix

      me.timerinterval = 2000 'your 2secs delay

      'your code to run external stuff goes here

      case else 'you know your external stuff is supposed to have completed

      me.timerinterval = 0

      'your after external stuff goes here

      end select

      end sub

      izy

      #17; Tue, 11 Dec 2007 21:10:00 GMT