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