Hi everyone,
I was wondering if someone could tell me why my loop always seems to skip one plan.
My recordset is this:
plan_num plan_name clcemail empname
1340 abtestingname test@test.com test name
1340 abtestingname test1@test.com test name
1100 aaanametesting test2@test.com another name
1450 anametest test3@test.com name name
1450 anametest test4@test.com name name
1450 anametest test@test.com name name
I want one email to go to each plan_num/plan_name (a client). There can be multiple clcemails (client email addresses) for each plan. The empname is the name of the employee that will be sending the email.
mysql = "SELECT * from test order by plan_name"
Set db = CurrentDb()
Set rs = db.OpenRecordset(mysql)
' Use "FirstRecord" so we can check if this is the first record
strplannum = "FirstRecord"
'Loop through the recordset
Do While Not rs.EOF
' Get the Client Email
strClcEmail = rs![ClcEmail]
' Check if this is the First Record
If strplannum = "FirstRecord" Then
' Get the Empname and the plan_num AND PLAN NAME
strEmpName = rs![EmpName]
strplannum = rs![plan_num]
strplanname = rs![Plan_Name]
' Start building the strRecipients
strRecipients = strClcEmail & ";"
Else
' This is not the first Record
' Verify this is the same client
If strplannum = rs![plan_num] Then
' Still with the same client
' Add the client's email to strRecipients
strRecipients = strRecipients & strClcEmail & ";"
Debug.Print strRecipients
Else
' This is the next Employee - send the email
' First remove the trailing ";" from strRecipients
If Right(strRecipients, 1) = ";" Then
strRecipients = Left(strRecipients, (Len(strRecipients) - 1))
End If
Debug.Print "strRecipients: " & strRecipients
Debug.Print "strEmpName: " & strEmpName
Debug.Print "plan_num: " & strplannum
'*** Call Procedure to send email here ***
Call SendEmail(strplannum, strEmpName, strRecipients, strplanname)
' Get the current Empname and plan_num AND PLAN NAME
strEmpName = rs![EmpName]
strplannum = rs![plan_num]
strplanname = rs![Plan_Name]
' Start building the new strRecipients string
' If we have a client email
strRecipients = strClcEmail & ";"
End If
End If
rs.MoveNext
Loop
Thank you as always for the great help.
Elizabeth
Rabu, 20 Juli 2011
[MS_AccessPros] Looping through recordset
__._,_.___
.
__,_._,___
Langganan:
Posting Komentar (Atom)
Tidak ada komentar:
Posting Komentar