Emailing a filtered report from a form button

[Johnny Drama]

Okay,

I have an interesting one and I'm not sure if what I'm trying to do is possible.

I have a form (frmUpdate Ctrl) that allows users to update data as needed. On that form is a button that passes the record number to a report, opens the report with the single record, and then emails the updated record to the appropriate person.

I'm trying to make it so that the preview of the report does not open, but just attaches the report to the email. Is this possible, or does the report need to be open to accept the passing of the record value?

The VBA code is below.


Private Sub btn_mailreport_Click()
On Error GoTo Err_btn_mailreport_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "rptUpdated Control"
stLinkCriteria = "[Control Number]=" & "'" & Me![Control Number Combo] & "'"
DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria
DoCmd.SendObject acReport, stDocName, acFormatRTF, "email@domain.com", , , "Updated Control", "The attached control has been updated", True

Exit_btn_mailreport_Click:
Exit Sub

Err_btn_mailreport_Click:
MsgBox Err.Description
Resume Exit_btn_mailreport_Click


End Sub

Thanks in advance for any help.

 

[pbaldy]

To use that technique to filter the report, it does need to be opened first. Alternatives include putting a criteria in the report's source query and techniques like this:

http://www.granite.ab.ca/access/email/reporttomultiplerecipients.htm

 

[Johnny Drama]

Okay,

I took a shot at it, well several actually, and I still can't quite get it. I made some adjustments after referencing the article at the link you provided.

The onclick button code now looks like this (I did not include the sub call or end statements):

stDocName = "rptUpdated Control"
DoCmd.SendObject acReport, stDocName, acFormatRTF, "email@domain.com", , , "Updated Control", "The attached control has been updated", True

and the report OnOpen event looks like this:

Me.Filter "Control Number=" & Forms![frmUpdate Controls]![Control Number Combo]
Me.FilterOn = True

When I click the button it appears as if the form tries to open, and maybe it does, as a dialog box opens and then closes very quickly, but the email application is never launched and the report is never sent.

Any other thoughts?

 

[pbaldy]

I assume you are starting this from that form, correct? I'm not sure what you mean by the form tries to open. You would still need the single quotes around the value, if it worked the other way, and you missed the equals sign:

Me.Filter = "Control Number='" & Forms![frmUpdate Controls]![Control Number Combo] & "'"

 

[Johnny Drama]

Yes, I'm opening it from the form. I meant to say it looks like the report tries to open. I've added the additional quotes as you suggested, but still no dice.

When I try to run the report directly I get a compile error: Invalid use of property and it highlights the following: ![Control Number Combo]

 

[pbaldy]

Can you post a sample db?

 

[Johnny Drama]

I've tried to upload for you several times but I keep getting an error message from the site. The sample DB isn't larger than the maximum allowable, so that's not the problem. I'll try later.

 

[pbaldy]

See if this helps:

http://www.access-programmers.co.uk/forums/showthread.php?t=140587

 

[Johnny Drama]

Here you go...

 

[pbaldy]

You still missed that equals sign, and I missed the inadvisable space in the field name, which requires brackets. Try this:

Me.Filter = "[Control Number]='" & Forms![frmUpdate Controls]![Control Number Combo] & "'"

 

[Johnny Drama]

You the man....it works!