Sending emails with Outlook from a form & Subform

[meendering]

I have a form and subform that I want to use data from to send out an email
message. The main form has the general information for a particular event
and the subform has all of the details. The subform can have multiple
records that are connected to a single record from the main form. I have
created my code and I am able to pull the first record from the subform into
my email but I am not sure of how to get the rest of the subform records
included in my email message. Below is the code for what I have so far.
------------------------------------------------------------------------------
------------------------------------------------------------------
Private Sub cmdSendEmail_Click()
On Error GoTo Err_cmdSendEmail_Click

Dim stText As String '-- E-mail text
Dim RecDate As Variant '-- Rec date for e-mail text
Dim stSubject As String '-- Subject line of e-mail
Dim StPatient As String
Dim stAccessDate As Variant
Dim stExposureTime As String
Dim stTicketID As String '-- The ticket ID from form
Dim stWho As String '-- Reference to tblUsers
Dim stHelpDesk As String '-- Person who assigned ticket
Dim strSQL As String '-- Create SQL update statement
Dim errLoop As Error

stSubject = "EHR Access Audit Follow-up"

RecDate = Me.DateEmailSent
strHelpDesk = Me.AssociateName
StPatient = Me.SubForm_CoWorkerEHRAccess_Detail.Form.PatientName
stAccessDate = Me.SubForm_CoWorkerEHRAccess_Detail.Form.AccessDate
stExposureTime = Me.SubForm_CoWorkerEHRAccess_Detail.Form.ExposureTime



stText = "A recent electronic health record (EHR) audit of activity in
Touchworks reveals that you accessed the record of one of your co-workers.
The access occurred on a date on which no other traceable activity occurred
such as an appointment being scheduled, a visit, creation of a task or order,
or receipt of test results." & Chr$(13) & Chr$(13) & _
"Per IHP's policy, Permitted Access to Patient Medical Records
(3.PC.14) employees are permitted to access a patient's medical record only
when they have a legitimate, job-related need to do so. Please explain the
purpose of the access event(s) noted below:" & Chr$(13) & Chr$(13) & _
"Patient: " & StPatient & Chr$(13) & _
"Date of access: " & stAccessDate & Chr$(13) & _
"Length of access: " & stExposureTime & Chr$(13) & Chr$(13) & _
"The IHP policy referenced above is available on the IHP
intranet or may be obtained from your manager." & Chr$(13) & Chr$(13) & _
"Please return your response to this inquiry to me within 10
days. Feel free to call me at 515-471-9203 if you have any questions." & Chr
$(13) & Chr$(13) & _
"Sincerely," & Chr$(13) & _
"Erika Linden" & Chr$(13) & _
"Privacy Officer and Director of Coding & Compliance"

'Write the e-mail content for sending to assignee
DoCmd.SendObject , , acFormatTXT, varTo, , , stSubject, stText, -1

'Set the update statement to disable command button
'once e-mail is sent
strSQL = "UPDATE Tbl-CoworkerEHRAccess SET Tbl-CoworkerEHRAccess.
EmailSent = Yes " & _
"Where Tbl-CoworkerEHRAccess.RecordNumber = " & Me.RecordNumber
& ";"

Exit_cmdSendEmail_Click:
On Error GoTo Err_Execute
CurrentDb.Execute strSQL, dbFailOnError
On Error GoTo 0

'Requery checkbox to show checked
'after update statement has ran
'and disable send mail command button
Me.cbEmailSent.Requery
Me.cbEmailSent.SetFocus
Me.cmdSendEmail.Enabled = False

Exit Sub

Err_Execute:

' Notify user of any errors that result from
' executing the query.
If DBEngine.Errors.Count > 0 Then
For Each errLoop In DBEngine.Errors
MsgBox "Error number: " & errLoop.Number & vbCr & _
errLoop.Description
Next errLoop
End If

Resume Next


Err_cmdSendEmail_Click:
MsgBox Err.Description
Resume Exit_cmdSendEmail_Click

End Sub
------------------------------------------------------------------------------
---------------------------------------------------------

The part that I would want repeated for the additional subforms records are:

"Patient: " & StPatient & Chr$(13) & _
"Date of access: " & stAccessDate & Chr$(13) & _
"Length of access: " & stExposureTime & Chr$(13) & Chr$(13) & _

 

[ErezM via AccessMonster.com]

hi
you'll need to change the 3 variables to one long string that will hold all
the reocrds. let's call it "strList".
since a form (your sub form in this case) has an underlying recordset, you
can move around that recordset as on any ado or dao recordset.
you can do something like this:
with me.subForm.recordset
.MoveFirst
While Not .Eof
strList = strList & "Patient: " & !PatientName & Chr$(13)
strList = strList & "Date of access: " & !AccessDate & Chr$(13)
strList = strList & "Length of access: " & !ExposureTime & Chr$(13)

.MoveNext
Wend
End With

i hope you're familiar enough with the above kind of code, but that should
build a list of all the patients in the sub form

good luck
Erez

StPatient & Chr$(13) & _