Stop my code afte every 100 or so records for one minute 1

[Cleis]

Hi All!

I'm trying to Stop the code below at 100 record increments for 1 minute. Can someone tell me what to put in where the ** are to stop the code for say one minute?? If I run this code, it bogs down the our e-mail system. Any advice would be MOST appriciated!!!!!!!!!!!

Regards!

Itch


Private Sub CmdSend_Click()
On Error GoTo Err_CmdSend_Click

Dim db As DAO.Database
Dim rs As DAO.Recordset
' Dim Mail As MailSender 'Set Ref to Persits AspEmail 4.5.0.2
' Dim strEmail As String
Dim txtCount As Long

' strEmail = " "
Set db = CurrentDb
Set rs = db.OpenRecordset("qryEMailList", dbOpenDynaset)
txtCount = 0
' Set Mail = New MailSender

rs.MoveFirst
Do Until rs.EOF

' Mail.Host = "Stuff"
' Mail.From = "Stuff"
' Mail.FromName = "Stuff"
'
' Mail.AddAddress "stuff" '
' Mail.AddCC "Stuff"
' Mail.AddCC "Stuff"
' Mail.Subject = "Stuff"
' Mail.Body = "Stuff"
' Mail.Send

DoCmd.GoToRecord acDataForm, "frmNotify", acNext
rs.MoveNext
txtCount = txtCount + 1
Me.txtCountShow = txtCount

If txtCountShow = 100 or 200 or 300 or 400 or 500 Then

**What goes here?????????????**

End If

Loop

MsgBox "All E-Mail Messages Have Been Sent.", vbInformation, "G.M.R.O.I. Application"
Me!cmdClose.SetFocus
Me!cmdSend.Enabled = False
Set rs = Nothing

Exit_CmdSend_Click:
Exit Sub

Err_CmdSend_Click:
MsgBox Err.Description
Resume Exit_CmdSend_Click

End Sub

 

[RoyVidar]

Hi!

Try:
[tt]dim dtTime as date
...
dtTime = Now
do while datediff("s",dtTime,Now)<60
loop[/tt]

Roy-Vidar

 

[Cleis]

Hi Roy!

I would really like to tie this to a record count instead of a time parameter if possible. Any ideas??

Thank you for your post!!!!!


Regards!

Itch

 

[RoyVidar]

Your question:
"Can someone tell me what to put in where the ** are to stop the code for say one minute??". As far as I can see, I've answered that.

What's the new requirement? You don't want to pause the code anymore?

Roy-Vidar

 

[Cleis]

I'm sorry if I was not clearer in my post. . . nobody to fault except myself. I want to break the code for one minute afer the record count reaches 100 records. After one minute I would like the code to continue. Do you have any suggestions?

Thank you for your time in helping me with this problem.

Kind regards.

Itch

 

[RoyVidar]

Yes the previous should do exactly that (at least it does in my setup, declaration at the top, code where the stars are), think the challenge here lies within the if test. You need to either specify each condition (using the counter variable, not the form control):

[tt] If txtCount = 100 or txtCount = 200 or txtCount = 300 or txtCount = 400 or txtCount = 500 Then[/tt]

Or a more dynamic version:

[tt]if txtcount/100 = txtcount\100 then[/tt]

"Several ways to skin a cat" these are my methods of achieving something like this. See if it works;-)

Roy-Vidar

 

[Cleis]

HI Roy!

Okay I've stopped my code though your code after 100 records. How do I start it again after one minute?

Thanks again with helping me! I DO appriciate it!!

Regards!

Itch

 

[RoyVidar]

Using this on my setup (do while datadiff...), it continues by itself after one minute. Doesn't it in your setup? Post your current code if it doesn't

Roy-Vidar

 

[Cleis]

Okay! I'm over my head! I won't try a do loop again LOL!

Here is the code

Private Sub CmdSend_Click()
On Error GoTo Err_CmdSend_Click

Dim db As DAO.Database
Dim rs As DAO.Recordset
' Dim Mail As MailSender 'Set Ref to Persits AspEmail 4.5.0.2
' Dim strEmail As String
Dim txtCount As Long
Dim dtTime As Date

' strEmail = " "
Set db = CurrentDb
Set rs = db.OpenRecordset("qryEMailList", dbOpenDynaset)
dtTime = Now


txtCount = 0
' Set Mail = New MailSender

rs.MoveFirst
Do Until rs.EOF

' Mail.Host = "172.16.1.87"
' Mail.From = "richardcleis@don.com"
' Mail.FromName = "Richard Cleis"
'
' Mail.AddAddress "richardcleis@don.com" 'txtTo
' Mail.AddCC "richardcleis@don.com"
' Mail.AddCC "richardcleis@don.com"
' Mail.Subject = txtSubject
' Mail.Body = frmNotifySub!Message & Chr(13) & Chr(13) & "Customer# and Name:" & Chr(13) & [txtCust#] & " " & Trim([txtCustomerName]) & Chr(13) & Chr(13) & "Item To Be Discontinued:" & Chr(13) & Chr(13) & Trim([txtItem]) & " " & Trim([txtDescription1]) & Trim([txtDescription2]) & Chr(13) & Chr(13) & "On Hand Inventory in Selling UOM:" & Chr(13) & [txtOnHand] & Chr(13) & Chr(13) & "Alternate Item:" & Chr(13) & Trim([txtTransitionItem])
' Mail.Send

DoCmd.GoToRecord acDataForm, "frmNotify", acNext
rs.MoveNext
txtCount = txtCount + 1
Me.txtCountShow = txtCount

If txtCountShow / 100 = txtCountShow \ 100 Then

Do While DateDiff("s", dtTime, Now) < 60

End If

Loop

MsgBox "All E-Mail Messages Have Been Sent.", vbInformation, "G.M.R.O.I. Application"
Me!cmdClose.SetFocus
Me!cmdSend.Enabled = False
Set rs = Nothing

Exit_CmdSend_Click:
Exit Sub

Err_CmdSend_Click:
MsgBox Err.Description
Resume Exit_CmdSend_Click

End Sub

 

[RoyVidar]

Heres a little closer to how the "delay" code looks in my setup:

[tt]If txtCount / 100 = txtCount \ 100 Then
dtTime = Now
Do While DateDiff("s", dtTime, Now) < 60
Loop
End If[/tt]

Assigning time within the test, making it loop for 60 seconds (comparing the time), then it should continue.

Roy-Vidar

 

[Cleis]

Roy

I am VERY APPRECIATIVE for your help! Thanks for your time in helping me with this problem!! I'll be in Cancun Mexico next Monday and at 12 Noon I'll lift a drink with one of those fancy umbrellas to ya!!! . . . take it to the bank!!!!!

Here is a STAR for ya'!

Thank you!

Itch

 

[PHV]

Roy, why not call DoEvents in your timing loop ?