Printing reports from Access to PDF

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to print a report i have created in access to a PDF report, the
trick is the report consist of 100 schools, i need a visual basic code that
will print each schools report separately. I have seen this code before; i
just can't find it now. Anyone know of it?
Thanks
 
A Report can be forced to use a new page for every school

So - print one report w/ 100 breaks..Print it to PDF ...VBA not needed
...(unless you are trying to do soemthing more complicated than you
described)

Bob
 
Hi J,

You will need to open the report filtered for each school, then print to
PDF, in order to have 100 separate PDF files. You can do this with VBA code
using a loop, but without knowing the name of your report, or the table and
fieldnames involved for storing the school info, I cannot provide a specific
example. Here is a procedure that should get you there part way. It assumes
that your default printer is set to a .PDF printer driver (otherwise, you can
specify this, under File > Page Setup on the Page tab.

Create a query named qrySchools that includes a listing of the desired
schools. Use the appropriate criteria to produce a filtered list, if you do
not wish to include all schools. The query needs to include the primary key
of the school, shown in this example as "pkSchoolID". This example also
assumes a numeric primary key field. You'll need to make a slight
modification in the event that your primary key is a text data type. Finally,
this code requires a checked reference to the Microsoft DAO 3.6 Object
Library.


Sub Test()
On Error GoTo ProcError

Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb()
Set rs = db.OpenRecordset("qrySchools", dbOpenSnapshot)

With rs
Do Until (.EOF Or .BOF) = True
DoCmd.OpenReport "YourReportName", View:=acViewNormal, _
WhereCondition:="pkSchoolID = " & rs("pkSchoolID")
rs.MoveNext
Loop
End With

ExitProc:
'Cleanup
On Error Resume Next
rs.Close: Set rs = Nothing
db.Close: Set db = Nothing
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in Test subroutine..."
Resume ExitProc
End Sub


After copying and pasting the code, and making the appropriate substitutions
for the name of the report, the name of the query and/or the name of the
primary key field, click on Debug > Compile ProjectName. Hopefully, your code
will compile without an error.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________
 
When I use this code, it asks me for each ID (What I changed the pkSchoolID
to)like a parameter query. I would like to get reports for however many IDs
there are and not type in each ID. I think I am missing something. Please
help.
 
I figured out what was wrong, there is a typo in the original code, after
acViewNormal there should be a comma a space and another comma. Also, the
View:= and the WhereCondition:= are unneccesary.

Another note on printing. If you add a field to your original query for the
name you want to use as a filename and add a .pdf to it (like ID&Month.pdf
ie... 15447John Doe.pdf), you can add an input box and use that field to copy
and then paste it to the save as name in the next screen, like this.

Dim MyValue As String
MyValue = InputBox("", "", rs("FName"), 100, 100)
DoCmd.OpenReport "AE_Statement_05012008", acViewNormal, , "BonusEmp = " &
rs("ID")
rs.MoveNext

The BonusEmp is the field in the report and rs("ID") is the field in the
query.
 
Hi Dawn,
I figured out what was wrong, there is a typo in the original code, after
acViewNormal there should be a comma a space and another comma.
Click to expand...

This is a true statement only if you are using what is known as positional
arguments.
Also, the View:= and the WhereCondition:= are unneccesary.
Click to expand...

Again, this is true only if you are using positional arguments.

I prefer to use named agruments in my code. Named arguments can be placed in
any order, and I think they lead to more "readable" code. Notice how your
suggested change involves two commas in a row, ie. between acViewNormal and
BonusEmp:

DoCmd.OpenReport "AE_Statement_05012008", acViewNormal, , "BonusEmp = " &
rs("ID")

Positional arguments, as you have used, are very dependent on the proper
position, thus the need for the two commas. Using named arguments means that
the programmer does not have to write code that looks like this:

DoCmd.SomeMethod "ReqValue1",,,,,,OptionalValueX

Your optional parameter:
"BonusEmp = " & rs("ID")

would use the named argument WhereCondition, if you used named versus
positional parameters.

Try my code again; I think you will find that it is correct, if you
implement it correctly. Make sure to include the line continuation character
(the space underscore).


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
 
Tom, thanks so much for your response and you are correct, I did end up using
positional arguments. I like your code much better, it is self explanatory,
but I could not get it to work as I am a beginner to VBA. But you steered me
in the right direction and it does work now and I am very happy with it.
Thanks again!
 
Hi Dawn,

You're very welcome. Glad I could help.

If you'd like to learn more about VBA programming (and who wouldn't???), try
out these two free resources:

DAO - Back To Basics Compilation/Demo by Tom Wickerath, Jan/Feb 2007
http://www.seattleaccess.org/downloads.htm

and

Access Basics by Crystal
http://www.accessmvp.com/Strive4Peace/Index.htm


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
 
Back
Top