Question Printing a Single Record in a Report

[elio]

Hi,
I have designed a simple database for printing out a standard letter using a table, a form and a report. I am using Access 2007. Everything works great except that when I set up a print button on the form, it prints out all the records and not just the one I have entered. I just wish to print the record that is currently on screen in the form, in the report I have created.
Additionally, it would be really useful if I could create a button that would create a .pdf of the single record and ask me to input an email address I wished to send it to, and then of course, to send it. I have the full MS Office 2007 suite.
Hope you can help, I will attach the database.
Thanks!

 

[Jeanette Cunningham]

Here is a link that shows how to print the record that is currently on the screen.
http://www.allenbrowne.com/casu-15.html

Using A2007, there is a built-in button on the print ribbon that allows to create a .pdf.

To create a button that emails a pdf of the report is a bit more work.
Here is a link with code to email a pdf of a report.
http://www.access-programmers.co.uk/forums/showthread.php?t=154602

 

[elio]

Thanks Jeanette, really appreciate it. The first fix, the printing of a single record via a report seems to have gone very well and be working now.

I tried the fix for the emailing a pdf of the report, but so far that doesn't work. I get the attached error message and I'm stuck. I am using Access 2010, but the database says its an Access 2007 one.
I am a complete novice, so would need the entire code for the event procedure window, obviously, entering my own report name. (rpt_print_out).

Thanks.

 

[Jeanette Cunningham]

Copy and paste exactly the code you used for the button to email the report as a pdf.

 

[elio]

OK, its here:

DoCmd.SendObject acSendReport, "rpt_print_out", acFormatPDF, [To], [Cc], [Bc], [Subject], [MessageText], [EditMessage], [TemplateFile]

Private Sub email_PDF_Click()

End Sub

 

[Jeanette Cunningham]

If you have the email address in the record source of the form, try like this:

Private Sub email_PDF_Click()
Dim strAddress As String

strAddress = Me.[EmailAddressTextboxName]

DoCmd.SendObject acSendReport, "rpt_print_out", acFormatPDF, strAddress
End Sub

 

[elio]

Thanks Jeanette, but the email address is not part of the record. What I would like is it to open an email in Outlook with "Custom Made Appliance Information for: Then the contents of that records Patent's Name field, and the .pdf file attached. The email address of the recipient would be put in manually from the Outlook Contacts List.

 

[Jeanette Cunningham]

Try this:
Private Sub email_PDF_Click()
Dim strContent As String
strContent = Me.[TextboxName]
DoCmd.SendObject acSendReport, "rpt_print_out", acFormatPDF, , , , , strContent
End Sub

 

[elio]

Hi Jeanette, thanks, that almost works. Two problems, first it puts the field from the report in the BODY of the email instead of the SUBJECT of the email, and second, if I close the email without sending it, it triggers a run time error.
Many thanks for your help.

 

[Jeanette Cunningham]

Private Sub email_PDF_Click()
On Error GoTo Err_Handler
Dim strSubject As String
strSubject = Me.[TextboxName]
DoCmd.SendObject acSendReport, "rpt_print_out", acFormatPDF, , , , "strSubject"
Exit_Handler:
Exit Sub
Err_Handler:
Select Case Err.Number
Case 2501 'cancelled, ignore
Case Else
MsgBox Err.Number & " " & Err.Description
End Select
Resume Exit_Handler
End Sub

 

[elio]

Thanks Jeanette, almost cracked it, the problem being that this code actually puts strSubject in the subject box of the email, instead of the contents of the patient's name field. I have attached a copy of the database with the new code, so you can see what I mean.

Many thanks!

 

[Jeanette Cunningham]

Here is the amended code

Private Sub email_PDF_Click()
On Error GoTo Err_Handler
Dim strSubject As String
strSubject = Me.[Patient's Name]
DoCmd.SendObject acSendReport, "rpt_print_out", acFormatPDF, , , , strSubject
Exit_Handler:
Exit Sub
Err_Handler:
Select Case Err.Number
Case 2501 'cancelled, ignore
Case Else
MsgBox Err.Number & " " & Err.Description
End Select
Resume Exit_Handler
End Sub

Here are some very important tips about table and field names.
Make sure you use only the letters A-Z, any digit 0-9 and the underscore character in your table and field names. It is best to avoid spaces in the names as well.

You have got away with your current field names because you have only a tiny amount of code in your database.
If you intend to develop this database to include more functionality, I strongly recommend that you change your field names according to the rules I mentioned above.

 

[elio]

Thanks Jeanette, I have modified the database in light of your helpful comments. I have also changed the patients name field to two separate fields, patient forename and patient surname.
I wasn't sure how to alter your new code to make it print the patients forename with a space followed by the surname so I modified it to print just the patients forename to test the code, and it works brilliantly! However, I would like to have the patients forename and surname in the subject line, so could you please amend the code?
I am not sure if this is possible, but its worth asking: is it possible to amend the .pdf name from RPT_PRINT_OUT.pdf each time. I was wondering if the .pdf name could be the patients name?

Many thanks!
PS. I have attached my amended database.

 

[Jeanette Cunningham]

See if this is acceptable.
Attached database.

 

[elio]

Thanks Jeanette! That seems to work

 

[elio]

Hi Jeanette, Sorry, I have another problem. When I filled the label boxes with text I get a strange 1 character blank space appearing in certain words. I cannot get rid of them whatever I try.
In the bold heading, in the word "Appliance", there is a space between the second p and the l. (app liance)
Similarly, in the label below there is a blank space in the word "your" there is a space between the u and the r. (you r)
I've spent hours trying to fix this but to no avail.
Can you take a look at it for me please?
Many thanks, new file attached.

 

[elio]

Sorry, I should have said, these errors are in the report. The report looks perfect in print preview and in design view etc, but when I print it, the printed, paper, report has those errors on it.

 

[elio]

Hi Jeanette/ Anyone,
I have installed the database on another machine now, Vista 32bit, office 2010 and all is OK, including the print out issue (?), but, the email pdf button does not do anything at all, can you help please?
I have attached the latest version I have of the database.

 

[elio]

blank post to get to 15 so I can do a PM.

 

[elio]

blank so I can get to 15 posts to do a PM.