Button on Access 2013 form to print report using current data on entry form and then save report as PDF

Hi all, I am a complete Novice with Access and this is actually the second question I have posted to try to figure out my Access 2013 problem.  First, I'll explain what I have done, what is the result, and then where I need to be with this.  Thank you in advance for your help.

I have created a one table database that uses an entry form to create records.  Once I had the form working I designed a simple report to output a single record, the current record showing on the form, to a letter size label using a 'Print' button at the bottom of the form.  This part I have working and the PrintCurrent_Click() code behind the button I have used is show below.

Private Sub PrintCurrent_Click()
'Print current record
'using rptEmployees.
Me.Dirty = False
If IsNull(Me!ID) Then
MsgBox "Please select a valid record", _
vbOKOnly, "Error"
Exit Sub
End If
DoCmd.OpenReport "JobTicket", acViewNormal, , "ID =" & Me.ID
End Sub

Now as I said this code will print out the newly entered record on the screen in the form correctly.  What I have to add to this is code that will allow me to save the report with the same newly entered data from the displayed form to the Desktop as a PDF file named using the 'Jobnum' field from the same newly entered data.  This is where I run into problems.  I have not been able to successfully and without error save the report as 'Jobnum value'.PDF to the desktop.  I can't seem to get the current data from the screen to be saved.  I keep getting one pdf that has a report page for every record in the database.  I hope I have explained this thoroughly for you.  Thank you for your help!
mjchevalierAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

pdebaetsCommented:
You can use DoCmd.OutputTo to send a report to a PDF file, but the problem is you can't specify a criteria string as a parameter, so you have to specify the criteria somewhere else.

the most straight-forward way to do this is to base your report on a query or SQL String, and include the criteria in your query or SQL. Here's an example report record source:

Select * from MyTable where ID = Forms!MyFormName!ID

Open in new window


Now you can use DoCmd.OutputTo like so:

DoCmd.OutputTo acOutputReport, "JobTicket", acFormatPDF, environ("userprofile") & "\desktop\" & me!jobnum & ".pdf"

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mjchevalierAuthor Commented:
Thanks pdebaets!  Your select statement is what I really need I believe.  But, since I am brand new to Access 2013, I really don't know where to put the Select statement in order for the button on the form to use it?  Can you please explain how and where I enter this?  Sorry to need micro level of detail.
0
pdebaetsCommented:
Put it in your report Record Source property. Change the table name and form name to your table and form name, respectively.
0
mjchevalierAuthor Commented:
Thank you pdebaets!  I modified the code I add to include what you gave me and put the SQL statement in the record source property of my report and everything works perfectly.  Thank you for your level of detail and all the help!
0
mjchevalierAuthor Commented:
Excellent detailed help that got me straight to my solution!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.