E-mail Current Record on Form

[yourfriendangela]

Hello,

I have yet to find an answer for this problem anywhere. I know it has been brought up by many before, but I would really appreciate it if someone knowledgable about Access 2010 forms/macros could help me out!

I have created a form that I want to e-mail out in PDF format by clicking a command button. When I use the "EMailDatabaseObject" macro action, the control outputs all records into an e-mail attachment, not just the current record.

I just want to e-mail one record at a time! How can I do this??? Please help!

 

[pbaldy]

I'd use a report; you'll have a lot more control over the formatting of the result. You can use this technique to let it filter itself to the record on the form:

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

 

[yourfriendangela]

Thank you Paul, but is VBA coding the only option? I have no experience using VBA.
Can I not use a specific macro action which would work in conjuction with "EMailDatabaseObject" ?

I would rather not have to use a report. But it sounds like it's the only way...

 

[pbaldy]

You can try a macro; I don't use them. In 2010 I think it's SetProperty; previously it was SetValue.

 

[yourfriendangela]

I can't figure out the SetProperty macro. Ugh this is impossible.

 

[pbaldy]

Unless you're building a web database, try the VBA. It's basically cut/paste and change the names to yours.

 

[yourfriendangela]

Ok I'll try it out. How do I put the VBA code into a command button on the form?

 

[pbaldy]

http://www.baldyweb.com/FirstVBA.htm

Note that the email bit can still be your macro. The bit in the report to filter it probably needs to be VBA.

 

[yourfriendangela]

Ok I now understand that part, but if I need to use both a macro and VBA code like you say, how should I set up the command button clicks?


I cut and pasted the code, but with my lack of familiarity, I can't exactly tell what names I will have to change. Seems "txtProgress" and "lblStatus" need to be changed, but that can't be it right?

 

[pbaldy]

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.

 

[yourfriendangela]

Aha, well can you tell me what "ShipmentsID" is naming? And "LoadID"?

I use a field named "ID" for the primary key

 

[yourfriendangela]

I am sorting by ID, and I have named the control on my form as "ID"

When I replace "ShipmentsID" and "LoadID" with "ID", it says i have a syntax error

 

[yourfriendangela]

It says:
Syntax error (missing operator) in query expression 'ID=001037P'.

001037P is the ID value of the first record my query recordset

 

[pbaldy]

What is your actual code? Don't forget to change the form name.

 

[yourfriendangela]

Private Sub Form_Open(Cancel As Integer)
Me.Filter = "ID=" & Forms![PantryTierForm]![ID]
Me.FilterOn = True
End Sub

 

[pbaldy]

Because your field is text, try

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

 

[yourfriendangela]

Ok I edited the code as you wrote it, and it worked!!

One big problem however... Since the VBA is set to On Open event, the form is automatically filtered to the one record when in form view. The macro will work fine and attach the one record to an e-mail because of the filter. But I have to turn the filter off to move records. So when I want to run the e-mail macro for another record I have to turn the filter back on. And when I do, it reverts back to the first record, filtering out all the other records.

It seems I cannot run the e-mail macro for any record except the first.
Help please? I'm getting so close...

 

[pbaldy]

I'm confused; are you emailing a form or a report? The process is designed for a report.

 

[yourfriendangela]

I am emailing a form. I see, so I'll have to change to a report then?

 

[yourfriendangela]

Is there any way to just copy my layout and structure to a blank report? I would hate to have to do all my design work from scratch again....