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,654 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

707 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now