Retrieve multiple records from a nested subform

[Guest]

I have a helpdesk application that records customer calls.

The main form (which selects the customer) has a subform
(FRM_Log_Entry_Subform) showing all calls. This subform has a subform showing
actions against the call.

The main form has a button to email call details which works fine.

What I need to do is retrieve all of the actions from the second subform
(FRM_Log_Entry_Actions_Subform) to email those as well.

Current code is shown below, any assistance much appreciated!

Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient

' Create the Outlook session.
Set objOutlook = CreateObject("Outlook.Application")

' Create the message.
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)

With objOutlookMsg

' Set the Subject, Body, and Importance of the message.
.Subject = "Support Call " & [FRM_Log_Entry_Subform]![Our Log No:] & "
/ " & [FRM_Log_Entry_Subform]![Company Name]
.Body = _
vbCr _
&
"-----------------------------------------------------------------------------------" _
& vbCr & "Customer: " & [FRM_Log_Entry_Subform]![Company Name] &
" (" & [FRM_Log_Entry_Subform]![Europa ID] & ")" _
& vbCr & "System: " & [FRM_Log_Entry_Subform]![EDI System] & "
" & [FRM_Log_Entry_Subform]![EDI Comms Method] _
& vbCr &
"-----------------------------------------------------------------------------------" _
& vbCr _
& vbCr & "Log Number: " & [FRM_Log_Entry_Subform]![Our Log No:] _
& vbCr & "Status: " & [FRM_Log_Entry_Subform]![Status] _
& vbCr & "Created By: " & [FRM_Log_Entry_Subform]![Logged By:] _
& vbCr & "Created On: " & [FRM_Log_Entry_Subform]![Date:] & " " &
[FRM_Log_Entry_Subform]![Time:] _
& vbCr & "Cust Ref: " & [FRM_Log_Entry_Subform]![Their Log No:] _
& vbCr & "Cust Contact: " & [FRM_Log_Entry_Subform]![Cust Contact:] _
& vbCr & "Call Type: " & [FRM_Log_Entry_Subform]![TypeDes:] _
& vbCr _
&
"-----------------------------------------------------------------------------------" _
& vbCr & "Description:" _
& vbCr & [FRM_Log_Entry_Subform]![Detail:] _
& vbCr
.BodyFormat = olFormatRichText

If [FRM_Log_Entry_Subform]![Priority] = 1 Then
.Importance = olImportanceHigh
ElseIf [FRM_Log_Entry_Subform]![Priority] = 3 Then
.Importance = olImportanceLow
End If

objOutlookMsg.Display
.Send

End With

 

[Guest]

Hi Mark

Sounds to me like you need to create a loop which places values from the
subform into the email. I will show how a loop can take records from a
subform and place them into a string. You can then adapt this to your own
purpose.

First, create a recordset so that you can access the data in the subform.
I've never used a nested subform, I assume you refer to them as follows:

[Forms]![Parent form]![Child form]![Grandchild form].Form

Or it might be:

[Forms]![Parent form]![Child form].Form![Grandchild form].Form

If not, I apologise, you will need to find this out from somewhere else.

So to open the recordset use:

Dim Actions As DAO.Recordset
Set Actions = [Forms]![Parent form]![Child form]![Grandchild
form].Form.Recordset

(...or however you refer to the nested subform)

You will need to enable the DAO library if it is not already. Go into Tools
- References and tick "Microsoft DAO 3.6 Object Library".

Create a string variable:
Dim Output As String

Create a loop to whack the records from the subform recordset into the string.

With Actions
..MoveFirst
Do
Output = Output + .Fields("Name of first field you want to output") & any
carriage returns, spaces, commas etc
Output = Output + .Fields("Name of second field you want to output") + any
carriage returns, spaces, commas etc
Etc etc
..MoveNext
Loop Until .EOF
End With

I'm sorry that my response is a little general. If you need any help, please
reply.

Cheers

David

I have a helpdesk application that records customer calls.

The main form (which selects the customer) has a subform
(FRM_Log_Entry_Subform) showing all calls. This subform has a subform showing
actions against the call.

The main form has a button to email call details which works fine.

What I need to do is retrieve all of the actions from the second subform
(FRM_Log_Entry_Actions_Subform) to email those as well.

Current code is shown below, any assistance much appreciated!

Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient

' Create the Outlook session.
Set objOutlook = CreateObject("Outlook.Application")

' Create the message.
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)

With objOutlookMsg

' Set the Subject, Body, and Importance of the message.
.Subject = "Support Call " & [FRM_Log_Entry_Subform]![Our Log No:] & "
/ " & [FRM_Log_Entry_Subform]![Company Name]
.Body = _
vbCr _
&
"-----------------------------------------------------------------------------------" _
& vbCr & "Customer: " & [FRM_Log_Entry_Subform]![Company Name] &
" (" & [FRM_Log_Entry_Subform]![Europa ID] & ")" _
& vbCr & "System: " & [FRM_Log_Entry_Subform]![EDI System] & "
" & [FRM_Log_Entry_Subform]![EDI Comms Method] _
& vbCr &
"-----------------------------------------------------------------------------------" _
& vbCr _
& vbCr & "Log Number: " & [FRM_Log_Entry_Subform]![Our Log No:] _
& vbCr & "Status: " & [FRM_Log_Entry_Subform]![Status] _
& vbCr & "Created By: " & [FRM_Log_Entry_Subform]![Logged By:] _
& vbCr & "Created On: " & [FRM_Log_Entry_Subform]![Date:] & " " &
[FRM_Log_Entry_Subform]![Time:] _
& vbCr & "Cust Ref: " & [FRM_Log_Entry_Subform]![Their Log No:] _
& vbCr & "Cust Contact: " & [FRM_Log_Entry_Subform]![Cust Contact:] _
& vbCr & "Call Type: " & [FRM_Log_Entry_Subform]![TypeDes:] _
& vbCr _
&
"-----------------------------------------------------------------------------------" _
& vbCr & "Description:" _
& vbCr & [FRM_Log_Entry_Subform]![Detail:] _
& vbCr
.BodyFormat = olFormatRichText

If [FRM_Log_Entry_Subform]![Priority] = 1 Then
.Importance = olImportanceHigh
ElseIf [FRM_Log_Entry_Subform]![Priority] = 3 Then
.Importance = olImportanceLow
End If

objOutlookMsg.Display
.Send

End With