E-mail Current Record on Form

[pbaldy]

I believe if you select the form you can do a "Save as" and create a report out of it.

 

[yourfriendangela]

So after changing it to a report, the e-mail macro only attaches the first record, not the specific record I am on.

 

[pbaldy]

It should filter to whatever record has focus here:

Me.Filter = "ID='" & Forms![PantryTierForm]![ID] & "'"

 

[yourfriendangela]

On a report, how does it know which record has focus?

Also, i'm not sure why, but for my report, the command button wizard does not open when I put a button on the report in design view... any idea why?

 

[yourfriendangela]

The magic wand button is pressed down for "Use Control Wizards"

The Wizard works for other controls, just not for buttons... what is going on?

 

[yourfriendangela]

This code is not working:

Private Sub Report_Open(Cancel As Integer)
Me.Filter = "ID='" & Reports![PantryTierReports]![ID] & "'"
Me.FilterOn = True
End Sub

 

[yourfriendangela]

Paul,

You are awesome. It all works!!
I just didn't understand at first, but now I do. You have to use a form and a report. Genius. Thank you for your help. Much appreciated.

 

[pbaldy]

Sorry, I was out all afternoon and evening yesterday. Glad you sorted it out.

 

[yourfriendangela]

Yes, thanks again.

I do have a few last questions if you can answer them.

1. My report works well with my form, and it will e-mail out one record at a time. How can I automate the e-mail with a specific e-mail address depending on the record's ID? I know I can put an e-mail address in the macro builder, but that would only automate one address.

2. My report can't open in report view anymore because of the VBA code On Open event. It says it "cannot find the referenced form". Which is strange, because I can easily use the form to e-mail a specific record in the report. Is there any way to fix this without comprimising the e-mail process?

 

[pbaldy]

1) If the email address is on the same form as the ID, you can get it from the form. Otherwise, you can use DLookup or open a recordset to get the address from a table.

2) A couple of methods come to mind. Keeping the same method, you can test to see if the form is loaded before applying the filtering code (look at IsLoaded in VBA help or search here). You can pass the value to filter on in OpenArgs and test that instead of using the form. You would add the value in the appropriate position in the OpenReport code, and in the report:

If Not IsNull(Me.OpenArgs) Then
  Me.Filter = "ID='" & Me.OpenArgs & "'"
  Me.FilterOn = True
End If

 

[gtlgirl11]

Angela,

I did something similar without the VBA code. I created a report that displayed only the fields specified with respect to a given record. So once you create the record you press 'Save and Send'. Then a macro prompted you as to which record you want to send, and you enter a key for the record and it sent it as a report. I can go into more detail if you'd like?

 

[gtlgirl11]

oops, nm. appears its sorted!

 

[yourfriendangela]

Paul,
Ok, so the email address is on the same form as the ID. But something is wrong with the macro action "EmailDatabaseObject". It does not populate any of the message details, i.e. To, Subject, Message Text, even if I type something in. It doesn't make sense.

Either way, I have no clue how to auto-populate the "To" section.

And I tried the code you wrote there, putting it into the same code I have now and it didn't do anything...

Private Sub Report_Open(Cancel As Integer)
Me.Filter = "ID='" & Forms![PantryTierForm]![ID] & "'"
Me.FilterOn = True
If Not IsNull(Me.OpenArgs) Then
Me.Filter = "ID='" & Me.OpenArgs & "'"
Me.FilterOn = True
End If
End Sub

The report still shows the same error message when I try to open it in report view by itself. It will open fine in report view as long as the form is already opened. Maybe this is just a necessary process....

 

[pbaldy]

You're mixing methods. It's either the form or OpenArgs, not both. If you want to stick with the form, then use IsLoaded to test for the form being open.

 

[yourfriendangela]

Ok i'll try using IsLoaded

Can you help me figure out the e-maildatabase object problem?

 

[pbaldy]

I'm not sure how flexible a macro is. I'd use VBA, where it's simple:

DoCmd.SendObject acSendReport, "ReportName", acFormatPDF, Forms!FormName.EmailAddress, ...

Where "EmailAddress" is the control on the form containing the email address. You can look in VBA help at SendObject for the arguments following, replacing "..." with the appropriate arguments.

 

[MichaelWaimauku]

The command button would only have the macro to email the report. The report's open event would have the VBA code from the link. I think you went too far down (into related links). You just want this code:

Me.Filter = "ShipmentsID=" & Forms![Select Load List]![LoadID]
Me.FilterOn = True

You would adjust the items in red.

Hi, I want to do a similar thing of only reporting on the current record that is showing in the open form. If I use a macro to drive the sendobject (Access 07) to send the pdf to the email address current on the form, how do I get the vba to run the report for the 'current' record on the form and send to email via the sendobject macro?? Not sure if this makes sense as I'm new to access???

 

[pbaldy]

The code you quoted would run automatically in the open event of the report, and filter it to whatever record was current on the form.