Hmmm.
maybe you need to prefix it with...
rstLate.MoveFirst before the loop starts...
Hmmm.
maybe you need to prefix it with...
rstLate.MoveFirst before the loop starts...
If you have posted the same question at multiple forums, please read this IMPORTANT INFO.
Please use the thread tools to mark your thread Solved
Please review the Knowledge Base
for samples and solutions , or to submit your own!
Uh, wont that put me into an infinite loop?
To live is Christ... To code is cool!
Not if you do it OUTSIDE the beginning of the Do While...like above it...come on now, use some of that noggin, granted it hurts a little...
If you have posted the same question at multiple forums, please read this IMPORTANT INFO.
Please use the thread tools to mark your thread Solved
Please review the Knowledge Base
for samples and solutions , or to submit your own!
Sorry but that comes up with the same error because the inner loop goes to the last record then I try on the outer loop to move to the next record -
Nevermind I see my total stupidity after the inner loop! duh!!
Sorry about that!!
I'll leave the rest for tommorrow
To live is Christ... To code is cool!
I thought that would work but it does not.
[VBA]rstLate.MoveFirst
Do While Not rstLate.EOF
strClientName = rstLate![Dealer]
strEmailBody = "The first test"
Do While Not rstLate2.EOF
If rstLate2![Dealer] = strClientName Then
strEmailBody = strEmailBody & rstLate2![orderNumber]
End If
rstLate2.MoveNext
Loop
SendEmail strEmailBody, "andy@wmohs.com", "New Test Late Short Orders"
rstLate.MoveNext
Loop [/VBA] also doesn't work??
This only gets me to the second record
when I try
[VBA]rstLate.MoveFirst
Do While Not rstLate.EOF
strClientName = rstLate![Dealer]
strEmailBody = "The first test"
rstLate2.MoveFirst
Do While Not rstLate2.EOF
If rstLate2![Dealer] = strClientName Then
strEmailBody = strEmailBody & rstLate2![orderNumber]
End If
rstLate2.MoveNext
Loop
SendEmail strEmailBody, "andy@wmohs.com", "New Test Late Short Orders"
rstLate.MoveNext
Loop [/VBA] this also does not work - So I am missing something
To live is Christ... To code is cool!
Ok so I figured out what was wrong with my Inner and Outer Loop. I had
[VBA]
Set rstLateI = rstLateO
[/VBA]
I needed to set the inner loop record set the same way I did the outer loop. Not sure why that is different than above - but it worked.
So now I have:
[VBA]
Dim rstLateI As DAO.Recordset
Set rstLateI = CurrentDb.OpenRecordset("qryEmailTest", dbOpenDynaset, dbSeeChanges)
rstLateO.MoveFirst
Do While Not rstLateO.EOF
strClientName = rstLateO![Dealer]
strEmailBody = "This Email was sent to address short orders that have been processed by WmOhs but for some reason have not bee signed off yet."
rstLateI.MoveFirst
Do While Not rstLateI.EOF
If rstLateI![Dealer] = strClientName Then
strEmailBody = strEmailBody & "<h3>Your order</h3>" & _
rstLateI![orderNumber] & "<br />" & _
rstLateI![Name] & " <p> Was Processed " & rstLateI![DaysElapsedSinceProccessed] & " " & "days ago! <br /></p>"
End If
rstLateI.MoveNext
Loop
SendEmail strEmailBody & "Please let us know if you have any questions", "andy@wmohs.com", "Late notice for Short Orders"
rstLateO.MoveNext
Loop
[/VBA]
This goes through the loops correctly - but for some reason when it sends the email it sends out as many emails as gathered info. For example if client "KSD" has 3 late jobs - it correctly compiles the three orders into one email, but then it send that email out 3 times. I assume this is because when I loop through the rst the first time I hit "KSD" I gather all the other "KSD" jobs with the inner loop then send an email off, but then I enter back into the Outer loop and find anouther "KSD". This shows me why I need a emailSent flag.
For the emailSent flag you showed me:
[VBA]sqlEmailSent = "Update [qryEmailTest] Set [EmailSent] = 1" & _
"Where [Dealer] = '" & rstLateO![Dealer] & "'"
DoCmd.RunSQL sqlEmailSent[/VBA]
That I assume would be inserted right after my SendEmail
Then I would and an IF statement before the inner loop that would check this field. But when I set up my code like:
[VBA]
Do While Not rstLateO.EOF
strClientName = rstLateO![Dealer]
strEmailBody = "This Email was sent to address short orders that have been processed by WmOhs but for some reason have not bee signed off yet."
rstLateI.MoveFirst
If rstLateO![EmailSent] = 1 Then
Do While Not rstLateI.EOF
If rstLateI![Dealer] = strClientName Then
strEmailBody = strEmailBody & "<h3>Your order</h3>" & _
rstLateI![orderNumber] & "<br />" & _
rstLateI![Name] & " <p> Was Processed " & rstLateI![DaysElapsedSinceProccessed] & " " & "days ago! <br /></p>"
End If
rstLateI.MoveNext
Loop
End If
SendEmail strEmailBody & "Please let us know if you have any questions", "andy@wmohs.com", "Late notice for Short Orders"
sqlEmailSent = "Update [qryEmailTest] Set [EmailSent] = 1" & _
"Where [Dealer] = '" & rstLateO![Dealer] & "'"
DoCmd.RunSQL sqlEmailSent
rstLateO.MoveNext
Loop
[/VBA]
I get A syntax error missing operator on the DoCmd.RunSQL sqlEmailSent
Am I at least getting closer??
To live is Christ... To code is cool!
You might need to add arguments for the connection necessary...to the point of identifying a recordset to perform the sql on...
If you have posted the same question at multiple forums, please read this IMPORTANT INFO.
Please use the thread tools to mark your thread Solved
Please review the Knowledge Base
for samples and solutions , or to submit your own!
Ok I think that qryEmailTest is the record set that I want to perform the sql on - Didn't I set that with the "Update [qryEmailTest] ??
To live is Christ... To code is cool!
What I mean is, you have to establish a connection to a database to perform the update in...
If you have posted the same question at multiple forums, please read this IMPORTANT INFO.
Please use the thread tools to mark your thread Solved
Please review the Knowledge Base
for samples and solutions , or to submit your own!
Samoh...If you have a sanitized version of the database, i can take a look at using your table structure
.....it is kind of hard to provide correct syntax out of context....
I know this is giving you fits, so if you want to you can email me a copy...my email addy is in my profile
If you have posted the same question at multiple forums, please read this IMPORTANT INFO.
Please use the thread tools to mark your thread Solved
Please review the Knowledge Base
for samples and solutions , or to submit your own!