Sending data from Form to MS Outlook (RecordsetClone Problem)

[mchapman44]

I have a form that contains many sub forms within it. I'm trying to setup VB
code to send information within these forms to MS Outlook. My email code is
working and I can select fields from the master form (The Form that opens)
but I cannot get data from the sub forms. I believe it's because my VB is not
selecting the correct record source from the form.

My sub form name is 'Sub_Frm_Finance' and I need to select data from 7
fields from this form.

My VB code so far is below. When I run the code I get a compile error on the
'RecordsetClone' value!!!!

Private Sub Email_Click()

Dim mess_body As String
Dim rst As DAO.Recordset
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem

Set rst = Sub_Frm_Finance.RecordsetClone

mess_body = "Booking Number" & Me.BookingNumber & vbCrLf & "Job Title" &
rst!JobTitle & vbCrLf & "Job Location" & rst!JobLocation & vbCrLf & "CRB
Check Required" & Me.CRBRequired & "Start Date" & rst!JobStartDate & " " &
"End Date" & rst!JobEndDate & vbCrLf & "StartTime" & rst!StartTime & vbCrLf &
" " & "End Time" & rst!EndTime & vbCrLf & "Required Hours" &
rst!RequiredHours & vbCrLf & "Required Days" & rst!RequiredDays & vbCrLf &
rst!Comments

Set objOutlook = CreateObject("Outlook.Application")
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)

With objOutlookMsg
' Set the Subject, the Body, and the Importance of the e-mail message.
.To = "(e-mail address removed)"
.Subject = "Job Alert"
.Body = mess_body
.Display
End With


End Sub

I would greatful if someone could help as I need to finish the database by
the end of the week and this is my last section.

 

[Arvin Meyer [MVP]]

You must build another recordset to get the data from the subform query or
table. If there is only one record you can refer to a value on a subform
with:

Me.NameOfSubformControl.Form.NameOfControl.Value

Also, I'm not too sure that I'd use the form's recordsetclone for my
recordset. I'd rather build a new recordset, which is restricted by a value
on the form so that I only get the 1 row and can refer to values on that
record in my subform's recordset. It's probably OK to do it your way, just
neater and more portable.

 

[mchapman44]

Thanks Arvin but I'm not sure what you mean by build another recordset?

The forms works fine and all subforms are working and data is entering the
tables correctly. When my form is open I just want to be able to get the
value from the fields in the subform and pass these to ms outlook. I have a
total of 8 records within subforms and 3 in the master form that I need to
get. The 3 fields in the master can all be retrieved using me.<Value>.

 

[Arvin Meyer [MVP]]

There are 2 recordsets on your form. Recordset 1 is the recordset which
provides the record(s) on the main form. Recordset 2 is the recordset which
provides the record(s) on the subform. Right now these recordsets are being
accessed as recordsetclone(s).

What is throwing me, here is your description of the recordcount. You say
that there are 3 in the main form and 8 in the subform. How are the subform
records related to the main form records?
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[mchapman44]

Arvin,

I have fixed the issue using the followiing command

Set rst = Me!Sub_Frm_Job_Details

and then referring to rst!<field Name> in my VB.

Thanks for your help.