Rabu, 20 Juli 2011

[MS_AccessPros] Looping through recordset

 

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

__._,_.___
Recent Activity:
.

__,_._,___

Tidak ada komentar:

Posting Komentar