×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Access Report - Save Report to multiple PDF's for each of the vendors

Access Report - Save Report to multiple PDF's for each of the vendors

Access Report - Save Report to multiple PDF's for each of the vendors

(OP)
Hi There,
I have a access report that has multiple vendors and when I run the report I want to save each of the vendors to a PDF file. For example Vendor-A has 2 pages, Vendor-B has 1 page and Vendor-C has 5 pages. Each of the vendor will be saved in a PDf file. Then I would like to email them to me all 3 vendors.

Thanks,

Sam

RE: Access Report - Save Report to multiple PDF's for each of the vendors

Wouldn't be easier to create a report per Vendor? You can then run 3 reports: one for each Vendor. No need to split them up.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

RE: Access Report - Save Report to multiple PDF's for each of the vendors

(OP)
I have 15 vendors to do this.

Sam

RE: Access Report - Save Report to multiple PDF's for each of the vendors

Does that matter? You can program to run all 15 reports with one click of a mouse. Or give user a list to choose from which Vendor(s) to create a report for.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

RE: Access Report - Save Report to multiple PDF's for each of the vendors

(OP)
Andrzejek

Do you have a sample A Vba script for this process? Thanks

RE: Access Report - Save Report to multiple PDF's for each of the vendors

No, I don't. But... how do you process your report right now? Could you show your code? I am sure it would be easy to modify the code to allow creating reports.

I can see the input, something like this:

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

RE: Access Report - Save Report to multiple PDF's for each of the vendors

If you plan to send the report to all the vendors and don't need to select a specific list of vendors, this can be done by adjusting the where clause in a loop and recordset. I have some code that can save the report to pdf and send the emails with attachments through outlook. If that describes what you are looking for, I can put together some sample code, may not get to it until the weekend though. Another tool you could look into is pdftk (free) which can insert and extract pages from pdf files amongst other things. Though if you used that it is a command line tool and you would need to know which page numbers to extract.

RE: Access Report - Save Report to multiple PDF's for each of the vendors

(OP)
Here is the Code that I have so far. I makes one Single PDF



Private Sub timecard_agency_daterange_Click()
On Error GoTo Err_timecard_agency_daterange_Click

Dim stDocName As String
'Save report to local drive
Dim strFile As String
strFile = "W:\Call Center\Call Center Reports\Agency_Reports\" & Format(Now, "YYYYMMDD_hms") & ".pdf"


stDocName = "timecard_agency_daterange_TZ"

DoCmd.OutputTo acOutputReport, stDocName, acFormatPDF, strFile

DoCmd.OpenReport stDocName, acPreview

Exit_timecard_agency_daterange_Click:
Exit Sub

Err_timecard_agency_daterange_Click:
MsgBox Err.Description
Resume Exit_timecard_agency_daterange_Click
End Sub



*****Employer is the key field to separate the PDF's

Thank you in advance.

Sam

RE: Access Report - Save Report to multiple PDF's for each of the vendors

One step at the time.
You said: "I have 15 vendors", and I assume you have a table with your Vendors:

tblVendors
ID   Vendor
1    Vendor1
2    Vendor2
3    Vendor3
...
15   Vendor15
 
So, now I would work on the code to create a PDF report for (any) one Vendor.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

RE: Access Report - Save Report to multiple PDF's for each of the vendors

(OP)
I having problem getting the filter right. The table is Agency_hours and the field is employer. There are 5 employers with employees. The report runs for everyone.

Thanks

Private Sub Label317_Click()
Dim rs As DAO.Recordset
Dim rpt As Access.Report
Dim sFolder As String
Dim sFile As String
Const sReportName = "timecard_agency_daterange_TZ"

On Error GoTo Error_Handler

'The folder in which to save the PDFs
sFolder = "W:\Call Center\Call Center Reports\Agency_Reports\"


'Set rs = db.OpenRecordset("SELECT DISTINCT [Employer] FROM [Agency_Hours]", dbOpenDynaset)
Set rs = CurrentDb.OpenRecordset("SELECT DISTINCT [employer] FROM [Agency_Hours]", dbOpenSnapshot)
With rs
If .RecordCount <> 0 Then 'Make sure we have record to generate PDF with
'Open the Report
DoCmd.OpenReport sReportName, acViewPreview, , , acHidden

'Define a report object so we can manipulate it below
Set rpt = Reports(sReportName).Report
.MoveFirst
Do While Not .EOF
'Build the PDF filename we are going to use to save the PDF with
sFile = Nz(![employer], "") & ".pdf"
sFile = sFolder & sFile
'filter the report to the specific record or criteria we want
rpt.Filter = "[employer]=" & ![employer]
rpt.FilterOn = True
DoEvents 'This is critical or else the filter isn't applied!!!!
'Print it out as a PDF
DoCmd.OutputTo acOutputReport, sReportName, acFormatPDF, sFile, , , , acExportQualityPrint
'If you wanted to create an e-mail and include an individual report, you would do so now
.MoveNext
Loop
'Close the report now that we're done with this criteria
DoCmd.Close acReport, sReportName
End If
End With

'Open the folder housing the PDF files (Optional)
Application.FollowHyperlink sFolder

Error_Handler_Exit:
On Error Resume Next
If Not rpt Is Nothing Then Set rpt = Nothing
If Not rs Is Nothing Then
rs.Close
Set rs = Nothing
End If
Exit Sub

Error_Handler:
If Err.Number <> 2501 Then 'Let's ignore user cancellation of this action!
MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
"Error Number: " & Err.Number & vbCrLf & _
"Error Source: Command1_Click" & vbCrLf & _
"Error Description: " & Err.Description & _
Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
, vbOKOnly + vbCritical, "An Error has Occurred!"
End If
Resume Error_Handler_Exit
End Sub

RE: Access Report - Save Report to multiple PDF's for each of the vendors

What happened to Vendors? Or are they the same as Employers?

You have a line of code:
rpt.Filter = "[employer]=" & ![employer]

so I assume your report has some kind of Data Source. Do you have a field in this data source named 'employer' ?

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

RE: Access Report - Save Report to multiple PDF's for each of the vendors

(OP)
Andrzejek Thank you for your response. The Employer names are reading in the VBA. However, when the report runs the data comes from a query. Also, I believed that query should be used instead of using the table in the database?.

RE: Access Report - Save Report to multiple PDF's for each of the vendors

If you want to use a query instead of the data from a table, that's fine. It doesn't matter.

But my idea was: create a PDF report for just one Vendor (Employer?).
And after that is accomplish, you can retrieve a list of all of them, loop thru the data, and create a separate PDF for each.

Unless I misunderstand what you want... ponder

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

RE: Access Report - Save Report to multiple PDF's for each of the vendors

(OP)
Andrzejek Thahk you so much. Do you have a sample of vba that I see?

RE: Access Report - Save Report to multiple PDF's for each of the vendors

You already do have VBA code to create a PDF report for all Employers (right?), you just need to adjust it to create a PDF for just one Employer.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

RE: Access Report - Save Report to multiple PDF's for each of the vendors

(OP)
Andrzejek, Thank you again. The problem is that I generate a table on the fly with only 5 or 6 vendors. But the report aspect it picks every vendor. I would like to limit with a filter or do loop. do you have a sample?
Sam

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close