Hi Elizabeth,
Could you post the DebugWindow results for running
your code and indicate what is missing from your
results.
Otherwise could you post an mdb file that demonstrates
the problem to the AssistanceNeeded Folder.
(nothing confidential and zipped).
I can't see the problem just eyballing the code.
Regards, Clive.
--- In MS_Access_Professionals@yahoogroups.com, "glcass58" <glcass58@...> wrote:
>
> 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 name
> 1340 abtestingname test1@... test name
> 1100 aaanametesting test2@... another name
> 1450 anametest test3@... name name
> 1450 anametest test4@... name name
> 1450 anametest test@... 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
>
Tidak ada komentar:
Posting Komentar