Email Using Continuous Form

[DoveArrow]

I have a form that I'm using to Email a list of campus advisors.
Here's basically how it works: The user enters the last name of the
student that they're working on into an unbound text box. Then, the
user selects the student's campus location and their Academic Program
from a series of unbound combo boxes. Based on this information, the
computer runs a query that displays the names of the advisor(s) that
work on that particular student. So for example, if my student's name
is Zellwigger, he attends campus 241, and his academic program is
UC.BA.ENG, it will display the email addresses of his advisors in a
subform.

What I want to have happen next is, when you click on a button, marked
Email, in the Form Header of the subform, it will open up an Email
page with the addresses of the appropriate advisors. Here's the code
that I'm working with.

Private Sub Email_Click()
Dim db As Database
Dim rs As DAO.Recordset
Dim strCriteria As String
'Set db = CurrentDb
'Set rs = db.OpenRecordset("qfltAdvisorLookup", dbOpenDynaset)

Set rs = Me.RecordsetClone
Do While Not rs.EOF
If Len(Trim(rs![Contact E-Mail])) > 0 Then
strCriteria = strCriteria & "; " & rs![Contact E-Mail]
End If
rs.MoveNext
Loop
FollowHyperlink "mailto:" & strCriteria

rs.Close
Set rs = Nothing
Set db = Nothing

End Sub

Now this little line of code works great, the first time you try to
use it. However, if you want to go back into the main form, and change
the name of the student, the location, and/or the Academic Program, it
still brings up an Email box, but the To: line is blank. What's going
on?

 

[DoveArrow]

I have a form that I'm using to Email a list of campus advisors.
Here's basically how it works: The user enters the last name of the
student that they're working on into an unbound text box. Then, the
user selects the student's campus location and their Academic Program
from a series of unbound combo boxes. Based on this information, the
computer runs a query that displays the names of the advisor(s) that
work on that particular student. So for example, if my student's name
is Zellwigger, he attends campus 241, and his academic program is
UC.BA.ENG, it will display the email addresses of his advisors in a
subform.

What I want to have happen next is, when you click on a button, marked
Email, in the Form Header of the subform, it will open up an Email
page with the addresses of the appropriate advisors. Here's the code
that I'm working with.

Private Sub Email_Click()
Dim db As Database
Dim rs As DAO.Recordset
Dim strCriteria As String
'Set db = CurrentDb
'Set rs = db.OpenRecordset("qfltAdvisorLookup", dbOpenDynaset)

Set rs = Me.RecordsetClone
Do While Not rs.EOF
If Len(Trim(rs![Contact E-Mail])) > 0 Then
strCriteria = strCriteria & "; " & rs![Contact E-Mail]
End If
rs.MoveNext
Loop
FollowHyperlink "mailto:" & strCriteria

rs.Close
Set rs = Nothing
Set db = Nothing

End Sub

Now this little line of code works great, the first time you try to
use it. However, if you want to go back into the main form, and change
the name of the student, the location, and/or the Academic Program, it
still brings up an Email box, but the To: line is blank. What's going
on?

Nevermind. I figured it out. Before running the Do...Loop, I needed to
tell it to go back to the first record. I added the line
"rs.MoveFirst" and now it works fine.