Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
| Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old June 23rd, 2006, 05:05 AM
Registered User
 
Join Date: Jun 2006
Location: , , .
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default Access report print each page to separate PDF file

I have the same problem as following describe:
"I work for a bank owned by a bank holding corporation with 35+ banks and 280+ branches in 5 states.
With that said, we do have the Acrobat Distiller on our computers. And I'm wondering if it is possible, with VBA, to take the 40+ reports I have to generate from Access each month and print each page of the reports to a separate PDF file? I want to be able to specify the report name and location in the VBA code, rather than typing it in for each page of each report. These reports are sales incentive reports for loan officers, and each page of the reports has a field that contains the officer names. It would be wonderful to be able to use the contents of that field from each page as part of the file name."
Can someone help?! Thanks


  #2 (permalink)  
Old June 23rd, 2006, 07:24 PM
Authorized User
 
Join Date: Jul 2004
Location: , , .
Posts: 30
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I would create a recordset with each officer name - loop thru each name and pass that name (as a variable) to the where clause in the report query. This way you can loop thru each officer name and create a 1 page report for that person. Also set the caption equal to the officer name in the report open event - this will cause the distiller to pick of the caption as the name of the report - forcing a different name for each report.

  #3 (permalink)  
Old June 25th, 2006, 10:26 PM
Registered User
 
Join Date: Jun 2006
Location: , , .
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Quote:
quote:Originally posted by bab02
 I would create a recordset with each officer name - loop thru each name and pass that name (as a variable) to the where clause in the report query. This way you can loop thru each officer name and create a 1 page report for that person. Also set the caption equal to the officer name in the report open event - this will cause the distiller to pick of the caption as the name of the report - forcing a different name for each report.

Any more detail advise? Thank you so much

  #4 (permalink)  
Old June 27th, 2006, 06:50 PM
Authorized User
 
Join Date: Jul 2004
Location: , , .
Posts: 30
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Create a module called "VarDec" and add

'-----------------VarDec------------------
Option Compare Database
Public OfficerNm As String


On the click event of a command button add
'--------Get distinct officer Names-------
Dim Sql as string
Dim rs as recordset

Sql = "SELECT DISTINCT officerName FROM Table1 ORDER BY officerName"

Set rs = currentdb.openrecordset(Sql)

With rs

.movelast
.movefirst

Do until .eof = true

OfficerNm = !OfficerName
DoCmd.OpenReport "Report1", acViewNormal

.MoveNext
Loop

End with
rs.close


'--------Report Open Event (Report1)--------------
Me.caption = OfficerNm

Me.RecordSource = "SELECT F1, F2 FROM Table1 WHERE OfficerName = '" & OfficerNm &"'"

  #5 (permalink)  
Old June 27th, 2006, 07:04 PM
Authorized User
 
Join Date: Jul 2004
Location: , , .
Posts: 30
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Set your printer default to the Distiller

  #6 (permalink)  
Old July 3rd, 2006, 08:00 PM
Registered User
 
Join Date: Jun 2006
Location: , , .
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Quote:
quote:Originally posted by bab02
 Set your printer default to the Distiller

Thx, I will try it:D



Similar Threads
Thread Thread Starter Forum Replies Last Post
print pdf file on client side gauravazad ASP.NET 2.0 Basics 1 October 31st, 2007 09:36 PM
Print to PDF from Access donrafeal7 Access VBA 5 February 25th, 2007 11:30 PM
file upload processed on separate page vauneen ASP.NET 2.0 Professional 0 August 8th, 2006 03:03 AM
Access to print PDF file to default printer Dkline Access VBA 0 July 7th, 2004 03:24 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.