Full Version: Make Looping 5 Seconds Apart
UtterAccess Forums > MicrosoftŪ Access > Access Forms
WongMeister
Good Evening.
Is it possible to stagger the looping of a table to one loop every 4 seconds?
I've streamlined what I want to do, into this basic example:
I have a table (tblOutbound), in which each record contains the data to be sent out in an email. There is a field named "Test Data" which contains some data to go in the body of the email. There are also two columns named "SendToEMAIL" and "CopyEMAIL", which merely contain the email addresses these emails are to be sent.
There could possibly be a couple hundred records, so that a couple hundred emails would be sent. However, by running this code below, it would normally send out a couple hundred emails in about 10 seconds. HOWEVER, I would like a 5 second-delay between each individual loop.
Can anbody help me with the "delay coding" and also the correct place to insert this code below?
Thanks.
Dim rsSingleSweep As Recordset
Set rsSingleSweep = CurrentDb.OpenRecordset("tblOutbound")
Dim strResults As String
rsSingleSweep.MoveFirst
Do Until rsSingleSweep.EOF
strResults = "Test Data: " & rsSingleSweep.Fields("Test Data") & Chr(13) & Chr(10)
DoCmd.SendObject acSendNoObject, , Text, rsSingleSweep.Fields("SendToEMAIL"), rsSingleSweep.Fields("CopyEMAIL"), , "This is a test email.", "" & strResults & "", 0, True
rsSingleSweep.MoveNext
strResults = ""
Loop
rsSingleSweep.Close
pere_de_chipstick
Hi WongMeister
There are two ways you could do this,
1. Specify a timer event in an open form where the timer event calls (or runs) your code
Set the Timer interval property to 4000 to run the code in the On Timer event every 4 seconds (4000 milliseconds)
Then set the time interval property to 0 to stop the On Timer event from firing.
2. Use the SleepAPI function
this API effectively locks your function for the period you specify before it continues your code execution
CODE
Option Compare Database
Option Explicit
Public intCancFind As Integer
Private wdApp As Object 'Word.Application
Private Declare Sub sapiSleep Lib "kernel32" _
        Alias "Sleep" _
        (ByVal dwMilliseconds As Long)


Public Function sTestSleep()
On Error GoTo err_proc
'Function tests SleepAPI
Const cTIME = 5000 'in MilliSeconds
    Call sapiSleep(cTIME)
    MsgBox "Before this Msgbox, I was asleep for " & cTIME & " Milliseconds."

exit_proc:
    Exit Function

err_proc:
    MsgBox err.Description
    Resume exit_proc
  
End Function

hth
WongMeister
Thanks Bernie!
missinglinq
If the point of your five-second delay is to simply not overwhelm things with so many emails going out all at once, you might simply try a DoEvents command immediately before the Loop command.
DoEvents returns control to Windows, and in this case would (I think) allow one email to be sent before running before returning control to Access, allowing it to continue running its code and sending out the next email.
Linq ;0)>
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.