Delay code within the loop

[dmarop]

Hello,

I will need your help.

I want to make delay, for example 2 minutes when the code is within the loop.

Example

For i = 0 To 100
    Debug.Print i
    'Before go to the next to make the delay
Next

Thank you in advance.


Regards,
Dimitris

 

[pr2-eugin]

You can create a Pause function, something like.

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

Public Sub Pause(intSeconds As Variant)
[COLOR=Green]  ' Comments: Waits for a specified number of seconds
  ' Params  : intSeconds      Number of seconds to wait
  ' Source  : Total Visual SourceBook
[/COLOR]
On Error GoTo PROC_ERR

    Dim datTime As Date
    datTime = DateAdd("s", intSeconds, Now)
    Do
       [COLOR=Green] ' Yield to other programs (better than using DoEvents which eats up all the CPU cycles)[/COLOR]
        Sleep 100
        DoEvents
    Loop Until Now >= datTime
PROC_EXIT:
    Exit Sub

PROC_ERR:
    MsgBox "Error: " & Err.Number & ". " & Err.Description, , "modDateTime.WaitSeconds"
    Resume PROC_EXIT
End Sub

Then your code will be,

For i = 0 To 100
    Debug.Print i
    [COLOR=Green]'Pauses the execution for 1 second, if you want more just increase it.[/COLOR]
    Pause 1
Next

 

[jdraw]

Here's a routine I found a few years back

'---------------------------------------------------------------------------------------
' Procedure : Pause
' Author    : From ghudson Access World Forums
' Date      : 28/02/2011
' Purpose   : To cause Access to Pause for a specific amount of time(in seconds)
'---------------------------------------------------------------------------------------
'
Public Function Pause(NumberOfSeconds As Variant)
10    On Error GoTo Err_Pause

          Dim PauseTime As Variant, start As Variant

20        PauseTime = NumberOfSeconds
30        start = Timer
40        Do While Timer < start + PauseTime
50        DoEvents
60        Loop

Exit_Pause:
70        Exit Function

Err_Pause:
80        MsgBox Err.Number & " - " & Err.Description, vbCritical, "Pause()"
90        Resume Exit_Pause

End Function

And a test routine

Sub TESTTIMER()

Debug.Print Now
Pause (3)
Debug.Print Now
End Sub

and the test result

15/05/2015 8:10:15 AM 
15/05/2015 8:10:18 AM

 

[BlueIshDan]

I've always used this, but I'm sure these fellas can improve on its effectiveness.

Public Sub Pause(ByVal seconds As Long)
    Dim time As Date: time = Now + 0.00001 * seconds
    While DateDiff("s", time, Now) < 0:: Wend
End Sub

 

[dmarop]

Thank you all for your help.

Now the code is working!

Have a nice time.

Dimitris

 

[spikepl]

Makes you wonder which code eats the least resources.

My bet - without trying - is pr2-eugin's.

 

[BlueIshDan]

actually his would be calling a function that practically does the same thing as the routine calling it. Almost doubling up on resources I would imagine.

 

[spikepl]

Naaah - my point is that his code uses (not very efficiently though) a system routine, which presumably is better than just a plain cpu-consuming loop

 

[BlueIshDan]

Yes, but encapsulated in what would already be used.

It would be questionably better if it was just calling the Sleep 1000 * seconds, but not while we're using the same Access VBA function, variable and object overheads as we would with manually coding the process purely in Access.

 

[Solo712]

Yes, but encapsulated in what would already be used.

It would be questionably better if it was just calling the Sleep 1000 * seconds, but not while we're using the same Access VBA function, variable and object overheads as we would with manually coding the process purely in Access.

Freeing resources while in the time loop looks like a good approach. I would go with even a smaller Sleep interval. But the function has a rather obvious redundancy in introducing the DateDiff where the API Sleep already is a timer. We know how many times a second the Sleep timer is going to kick in. A loop with a counter would have been the most resource-savvy way to go.

Best,
Jiri

 

[BlueIshDan]

I suppose we'd have to be designing for the happy medium between performance and user experience.

 

[Vickyy]

I encountered a problem with my MS Access DB and i would

appreciate any help on here please.

I have a 'tblStock' with fields 'ProductID', 'InitialStock', 'Buy', 'Sell'

and 'UpdatedStock'. I also have a form 'StockUpdate' add values

and also add new records to 'tblStock' . If I have value [100] for

IntialStock quantity, Buy [0] and sell [10], UpdatedStock will be [90]

(that's done and fine!). The problem is, I would like to make the

UpdatedStock value [90] to be the NEW InitialStock, so that any BUY

or SELL will keep updating the UpdatedStock and making it the NEW

InitialStock for the next transactions and so on.....but i have been

struggling to find code to implement this.....Any help will be

greatly appreciated.

Thanks in anticipation!

 

[BlueIshDan]

How much wood, would a wood chuck, chuck if a wood chuck could chuck wood? A wood chuck would chuck as much as a wood chuck could, if a wood chuck could chuck wood...