Referencing a subform control for email

[Tony Williams]

I have a form on which there is a tabcontrol and on this onr of the tabpages
is a tick box which opens Outlook with data
from the main form and prompts the user to amend the message before being
sent.
The code is
Private Sub Loadtxt_Click()
Dim oA As Outlook.Application
Dim oM As Outlook.MailItem
Set oA = CreateObject("Outlook.Application")
Set oM = oA.CreateItem(olMailItem)
If Me!Loadtxt = True Then
oM.Subject = "Upload to Online " & Me.Docnumtxt
oM.Body = "Please upload this document to Online " & vbCrLf & "Document
Number: " & Me.Docnumtxt & vbCrLf & "Author: " & " " & Me.DocAuthortxt &
vbCrLf & "Document URL: " & " " & Me.DocURLtxt
'then you can send it
'oM.Send

' or display it for editing
oM.Display
End If
End Sub

On the tab control is a subform called
tblDocSubjectssubform which has a control called cmbPsubject. I want to
include in the email the value of this control and have used this code
& me.tblDocSubjectssubform(cmbPSubject) but it doesn't work. How can I
reference the control on the subform?

TIA
Tony Williams

[Pieter Linden]

Tony,
there's a really good article on www.mvps.org that you can download
that Keri Hardwick wrote that gives examples of referencing controls
on forms, subforms, subsubforms etc right here...
http://www.mvps.org/access/forms/frm0031.htm

The only problem I see with this is that you're going to have more
than one possible subject if you have a subform... are you just going
to grab the value from the first record?

HTH,
Pieter

[Tony Williams]

Thanks Pieter, in answer to your question that is the problem. I've managed
to work out the syntax for the oM.Body and yhis is what I've got now
oM.Body = "Please upload this document to Online " & vbCrLf & vbCrLf &
"Document Number: " & Me.Docnumtxt & vbCrLf & "Document Name: " &
Me.DocNametxt & vbCrLf & "Author: " & " " & Me.DocAuthortxt & vbCrLf &
"Document URL: " & " " & Me.DocURLtxt & vbCrLf & "Primary Code: " &
Forms![frmdocumentrecord]![tblDocSubjectssubform].Form![DocPSubjecttxt] &
vbCrLf & "Secondary Code: " &
Forms![frmdocumentrecord]![tblDocSubjectssubform].Form![cmbSSubject]

However because tblDocSubjectssubform is in continuous format I'm only
picking up the first record when I need to pick all the data. Any ideas?
TIA
Tony
"Pieter Linden" <pi********@hotmail.com> wrote in message
news:bf**************************@posting.google.c om...

Tony,
there's a really good article on www.mvps.org that you can download
that Keri Hardwick wrote that gives examples of referencing controls
on forms, subforms, subsubforms etc right here...
http://www.mvps.org/access/forms/frm0031.htm

The only problem I see with this is that you're going to have more
than one possible subject if you have a subform... are you just going
to grab the value from the first record?

HTH,
Pieter

[Pieter Linden]

Kind of guessing now, but how about opening a RecordsetClone of the
subform's underlying recordsource and then looping through that and
going that way. Something like:

dim rs as dao.recordset
set rs = me!frmSub.RecordsetClone

do until rs.eof
'do your appending here
rs.movenext
loop

[Tony Williams]

Thanks for that suggestion Pieter but being a novice at coding that has me
lost! It is the "do your appending here that loses me what exactly does that
mean?
TIA
Tony
"Pieter Linden" <pi********@hotmail.com> wrote in message
news:bf**************************@posting.google.c om...

Kind of guessing now, but how about opening a RecordsetClone of the
subform's underlying recordsource and then looping through that and
going that way. Something like:

dim rs as dao.recordset
set rs = me!frmSub.RecordsetClone

do until rs.eof
'do your appending here
rs.movenext
loop

[Fletcher Arnold]

"Tony Williams" <[e-mail]> wrote in message
news:bt**********@sparta.btinternet.com...

Thanks for that suggestion Pieter but being a novice at coding that has me
lost! It is the "do your appending here that loses me what exactly does that mean?
TIA
Tony
"Pieter Linden" <pi********@hotmail.com> wrote in message
news:bf**************************@posting.google.c om...

Kind of guessing now, but how about opening a RecordsetClone of the
subform's underlying recordsource and then looping through that and
going that way. Something like:

dim rs as dao.recordset
set rs = me!frmSub.RecordsetClone

do until rs.eof
'do your appending here
rs.movenext
loop

I imagine he means adding to the string to build up the body text of the
e-mail. Although 'appending' is often used specifically when talking about
inserting new records in a table, that's not what is meant here.
I just thought I'd add a comment because I've just answered a post on
MoveFirst, which I believe you will need if you use the form's
RecordsetClone but which has not been used here.
There also seems to be a mistake with set rs = me!frmSub.RecordsetClone
which I believe should be set rs = me!frmSub.Form.RecordsetClone
here is an example of the technique:

Private Sub cmdList_Click()

On Error GoTo Err_Handler

Dim rst As DAO.Recordset
Dim strBody As String

strBody = "Dear Santa," & vbCrLf & _
"Here is my Christmas list: " & vbCrLf

Set rst = Me!sbfSub1.Form.RecordsetClone

If rst.RecordCount > 0 Then

rst.MoveFirst

While Not rst.EOF
strBody = strBody & rst!ThingWanted & vbTab & rst!QtyRequired &
vbCrLf
rst.MoveNext
Wend

End If

MsgBox strBody, vbInformation

Exit_Handler:

If Not rst Is Nothing Then
Set rst = Nothing
End If

Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Sub

HTH
Fletcher