auto email late clients

[XLGibbs]

Hmmm.

maybe you need to prefix it with...

rstLate.MoveFirst before the loop starts...

[samohtwerdna]

Uh, wont that put me into an infinite loop?

[XLGibbs]

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...

[samohtwerdna]

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

[samohtwerdna]

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

[samohtwerdna]

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??

[XLGibbs]

You might need to add arguments for the connection necessary...to the point of identifying a recordset to perform the sql on...

[samohtwerdna]

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] ??

[XLGibbs]

What I mean is, you have to establish a connection to a database to perform the update in...

[XLGibbs]

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