Hi Clive,
Thanks so much for your help. I see what you're saying.
I played around with lots of test recordsets and the Debug Window shows I capture all the data for that last email, but as we know the email doesn't get created. The funny thing is now that you pointed out my problem, I am surprised the last record's email recipient makes it into the recipient list. I kept thinking it wouldn't because of the Do While Not rs.EOF.
To create the last email I simply added another Call procedure outside of the loop and it took care of it. Does that seem problematic to you? I tried to think of a scenario where that would cause an error but it seems okay....
Thanks again.
--- In MS_Access_Professionals@yahoogroups.com, "horastacatto" <zctek@...> wrote:
>
> Hi Elizabeth,
>
> There is one problem that I have just noticed before
> finishing for the night.
>
> In the Else clause with the line,
>
> ' Get the current Empname and plan_num AND PLAN NAME
>
> you get the currentvalues ready for the next Loop
> which will end if you get EOF so you fall out of the
> loop with an unprocessed record.
>
> You need some cleanup code after the Loop instruction
> to handle that unprocessed record.
>
> Hope that helps.
>
> Regards, Clive.
>
> --- In MS_Access_Professionals@yahoogroups.com, "horastacatto" <zctek@> wrote:
> >
> > 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