Email current record from Subform

[kgranneman]

I have a subform nested into a main form. I want to email a report of only the current record being viewed.
The report mirrors the form, it contains a subreport nested into a main report.
The main form has a unique text SubjectID and the subform has a unique number RecCount.
Allen Browne has a filter recommendation using a gstrReporFilter variable. I've manipulated this code for over a week without success.

I think there's a conflict with sending the report (when command button is clicked), interrupting the send (to apply the report filter) and then resending again.
The procedure returns error "send object cancelled-2501" from on_click. I added "Resume Next" and repeated the send object in the On_open report. The error is no longer displayed and it looks like the procedure is working, even asks the user for an output format, but the email message isn't created.
I'm able to generate the email with a pdf of all the records for all of the subjects (creates the email and opens to allow for edits). But generating an email of only the current subform tied to a main form is causing me much frustration.

Here is the code I'm using:

'Public variable in module with the option statements:
Public gstrReportFilter As String

'Code On_Click of email command button:
Private Sub cmdbtn_SendM_Click()
On Error GoTo 0
On Error Resume Next
Dim frmFURec As Form
Set frmFURec = Forms!frm_FUVisits!frm_FURecords.Form
If Me.Dirty Then Me.Dirty = False
gstrReportFilter = "[RecCount] = " & frmFURec.[RecCount]
'Number gstrReportFilter = "[Index] = " & Me.[Index]
'Text gstrReportFilter = "[Index] = """ & Me.[Index] & """"
DoCmd.SendObject acSendReport, "rpt_FUVisits"

ExitProc:
Exit Sub
ProcError:
MsgBox "Error: " & Err.Number & ". " & Err.Description
Resume ExitProc
End Sub

'Code On_Open of Report
Private Sub Report_Open(Cancel As Integer)
On Error GoTo 0
If Len(gstrReportFilter) > 0 Then
Me.Filter = gstrReportFilter
Me.FilterOn = True
DoCmd.SendObject acSendReport, "rpt_FUVisits"
'gstrReportFilter = vbNullString
End If

ExitProc:
Exit Sub
ProcError:
MsgBox "Error: " & Err.Number & ". " & Err.Description
Resume ExitProc
End Sub

Thanks.

 

[pbaldy]

Try taking the SendObject line out of the report's open event. I use a technique similar to that one, and you don't need (or want) the SendObject in the report's code.

 

[kgranneman]

Thank you for reviewing the code and replying.
I've tried it with and without the DoCmd in the On_open event.
The procedure advances to that point and displays the "Output format" dialog box.
After clicking "OK" I see the "generating report" dialog box flash momentarily and then.. nothing. No errors are returned, an email is not created and I cannot locate any semblance of the email itself or the report. Again, there is non of this behavior when a report is generated that is not limited to the current record.
Any other suggestions?
Thanks again

 

[pbaldy]

Add

Debug.Print gstrReportFilter

so you can examine what's being passed to the report in the VBA Immediate window. Does it look correct, and is it the appropriate syntax for the data type of RecCount?

 

[kgranneman]

The Debug.Print returns [RecCount] = 90.
Assuming the bracketed portion isn't applied, then the field is RecCount which is an AutoNumber, long interger and the record I tested was #90.
I have this field included on the report, although I don't need it. The report field name is also RecCount. I've tried both assigning the RecordSouce to "RecCount" and leaving it unbound.

 

[pbaldy]

That seems okay. Can you post the db here?