Solved

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

Posted on 2014-09-17
5
1,717 Views
Last Modified: 2014-09-19
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!
0
Comment
Question by:mjchevalier
  • 3
  • 2
5 Comments
 
LVL 12

Accepted Solution

by:
pdebaets earned 500 total points
ID: 40329070
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
 

Author Comment

by:mjchevalier
ID: 40329192
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
 
LVL 12

Assisted Solution

by:pdebaets
pdebaets earned 500 total points
ID: 40329206
Put it in your report Record Source property. Change the table name and form name to your table and form name, respectively.
0
 

Author Comment

by:mjchevalier
ID: 40332830
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
 

Author Closing Comment

by:mjchevalier
ID: 40332834
Excellent detailed help that got me straight to my solution!
0

Featured Post

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question