Access 2003: Save report as pdf file

Good morning,

Thank you Everybody for writing,

I need to try your ideas and get back to you soon,

Re: saving report to pdf
====================

One thing, I would like to say, is late yesterday, when I ran the code from  the link from Planet source
is that I think it shut off my default printer setting bec.

I have a vba script that runs in the morning via Windows scheduler to print an Excel spreadsheet,
and when I came in the morning, the code was paused in the excel spreadsheet waiting for me
to choose a printer.

I went to control panel, and chose my default printer, and my script ran fine.

At home, I was thinking, that I am not sure if the planet source code does a save as to pdf file,
or just converts and displays the pdf file on the screen.

If the planet source code does not do a save as to pdf, I could always tinker with with the SendKeys
to save as, but maybe Jeff is providing an alternate approach, so I have to look into Jeff's approach
this morning....

Re: customizing file name
====================
Helen, thx for the code

Helen and Jeff:
===============
I know this is off topic and I plan on opening a new post.
Can I please let you know when I post the question?

It relates to printing pictures:
=======================
I need to print a report that is 4 pictures across and 4 pictures down.
Currently the user uses Merge feature in Word. User created a doc with a table to hold pictures.
User told me that Word itself cannot merge pictures into a Word doc so they purchased a
Word AddIn call OnMerge.

I'm looking for alternate approaches to produce a "photo album"  by clicking a button on an Access form.

Many years ago, I copied from Helen's website vba to print Word labels.
I am aware that in Access I can print columnar reports.
Our pictures reside in a server folder not in a database.

So since Helen replied here, and Jeff has broad knowledge, I wanted to "flag" you guys here,
if I can alert you when I post this question.

tx and will  get back to you on the above ideas, tx, s

Hi Kelvin,

I followed your suggestion and changed printer name to "Adobe PDF" bec. that's what's mentioned
in Control Panel/Printers.

I ran:
Call RunReportAsPDF("rpt_Test", "C:\ARG_09_M4_Graph\", "test.pdf")

and got error msg in the screen shot below.... any thoughts...?

Hi Jeff,
thx for your instructions - i go tthe pdfs to print, thx so much!

... i do have questions, though, i'd like to describe my actions so far for any future EE reader:

a) downloaded you mdb to myfolder
b) downloaded zip file    to myfolder
c) extracted dll                to my folder
d) created a folder in c:\  called "YourFolder"
e) changed mdb startup setting to NONE for form, so that form does not show automatically when open
    mdb
f) click on form button, getting the error msg for msgbox, so I commented out the last line
       MsgBox "There was an error executing the command." _
    & vbCrLf & vbCrLf & "Error " & Err.Number & ": "
    '& vbCrLf & vbCrLf & "Error, vbExclamation

g) then got an error msg for Space command, pls see below
     so I went to vba/tools/references   and saw that next to Outlook it says MISSING
     so I unchecked OUTLOOK, bec. we use Lotus Notes here
h) then clicked on button, and saw pdfs flash, and msgbox of Done
i)  checked YourFolder and see 2 pdfs
j) thank you for customizing an mdb for me with a DAO loop for student table which is what I'll have
   to do, as well

k) quest, pls:   and pls advise if you'd like new posts for these quests:
=========================================================
    a) do you keep your pdfs in a sep folder like Your folder   or as a subfolder in the folder that
        your mdb is located in
    b)  what does "avoids the endless loop" mean in this code snippet window?
    c) In second screen shot, you see components of mdb
        1) I need to import the Lebans pdf logic into an exisitng mdb
              do i import the modules that are named "mod"
        2) Is the class module your generic routines that you use for your projects that is not
            required for me to import into my mdb?
        3) I will copy and adjust the code behind the form's button window
     
Pls advise on (k) and if you want (k) in a sep. post,
tx, s
           
   

'Recordset Cleanup Code
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
 
Exit_cmdEmailIndividualCustReports_Click:
    'Avoids the endless loop
    DoCmd.Echo True
    On Error Resume Next
    rst.Close
    Set rst = Nothing
    dbs.Close
    Set dbs = Nothing
    Exit Sub
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:

Hi Jeff,

a) It looks like the code does not allow ** subfolder within **  a folder, do you agree?
IOW:
i tried: "C:\ARG_09_M4_Graph\ARG_09_M4_Graph_pdfs\"
                     instead of
           "C:\YourFolder\"
  and the pdfs did not go to subfolder.

if I did a different (you called it "root"?) folder like this: "C:\ARG_09_M4_Graph\"
       pdfs went there successfully.

I have another related question, relating to opening a report hidden?
Can I let you know when it is posted? tx, s

Thank you everybody for your help...s

Hi Jeff, new post related to this post based on your code
    can u look at if u have a minute? tx! s

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Access_Reports/Q_24762463.html

The code worked fine for me going to a subfolder.
Make sure you reference it in all places with the trailing" "\"

Also make sure you have rights/permissions to this subfolder.

Finally, check your "speeling"
;-)

Jeff

Thank you so much, Jeff...

You are right it was a typo.... I misspelled my sub-folder name

I imported all the components to my actual mdb and tested it there. Work fine!

I will be off on Mon. I will continue to adapt this solution for business needs.
    First  will work with creating pdf report for an individual student when chosen from combo box
    Then will create a DAO loop/script to create files for an entire class
                   (each student's pdf will live in a target folder)
                               thx again and have a nice weekend, s

mental note for next week... may open a post to ask if I can bulk print these pdf files, to give the user an option of printing them off the folder thru .bat file, scripting commands etc....

tx everybody, have a nice weekend, s

Again, I would like to emphasize how impressed I was that you were able to do most of the work on your own.
Then to follow up by asking great questions that will help you understand how things are working.

So in honor of your efforts, I have updated my sample file and removed all references to "Email".
In this way future generations of askers here will benefit from your wisdom.
;-)

JeffCoachman

To adress your "printing PDF" concern.

Please remember that Printing the actual Report or the PDF from that report will result in the *exact same printout*.

It is *Far* easier to print the report than it is to print the pdf.

The main reason why you should need a pdf is that you need to "Give" someone an exact "snapshot" file of the data/report, at a particular time.

But again, I know how you are, ;-) , don't take my word for it, try it out for yourself.
Print the report, then run this code.
Open the PDF and print it from Adobe.
Compare the two...


Batch printing the reports only requires you to modify the code to loop the Report Open command:
DoCmd.OpenReport strReportName, acViewNormal, , "CustomerID=" & "'" & strCustID & "'"

;-)

JeffCoachman

Hi Jeff,

a) I was off on Mon. and over the weekend I was thinking of posting a revised mdb
(w/o Outlook reference and commenting out one of the lines of code in the msgbox at
 error handling)

This morning I see that you did the same for your sample mdb.
Perhaps you want to upload yours....

In the meantime, below is modified mdb.

Also for EE reader, is a screen shot of windows explorer. I highlighted the components
that you need (the rest of window explorerer you can ignore)

Mr. Lebans approach to pdf is wonderful and is quoted alot in EE. G-d bless him.
G-d bless Jeff for his very clear way of explaining how to use Mr. Lebans approach and for
Jeff's sample mdb which is very clear as well.

b) Jeff, will be back shortly to comment on your print-pdfs comment.
    I have to digest your remarks.

    tx, s

   
   

Hi Jeff,

In June 2009, I asked a similar question FROM THE EXCEL side about printing the file and
saving as a pdf.

In that post you explained as you do in this post, that it's EASIER to print from EXCEL than to print
from pdf screen, after one has save Excel to pdf. I tried to print from pdf using Sendkeys and it
did not work properly, so I ended up printing spreadseeht using Excel print command.

So, without having testing printing Access report after it has been converted to pdf, I will agree
with you, that it's probably EASIER to print from within Access.

The reason for my quest. is a bit diff, and should open a new post:
======================================================

You see, at this time, my users are not really Access users.  This is an Oracle environment and
they use a very expensive web app. to do their work. I transferred from another dept bec. they
had an opening. I was working with Access 2000 in old dept.

So for their missing functionality, I am approaching with Access 2003.
One day, I may put all my functions on an Access menu and install on users machines.

Right now, I'm running off my pc.

And so:
=======
Once I get the DAO routine to work to bulk print to pdf files.
I will have 200 pdfs living in a folder on a server.

The user is used to going to server "for things" so if they need a student's report they can go to the
server.

I want to post, if there is a way to code a .bat file or script language to loop thru the folder
and print ALL 200 pdfs w/o the user doing manually, if adobe allows parameters for printing like
i'm making this up:
    shell c:\program files\adobe\adobe.exe   "pdf file name" -p  (where -p would be to print)

tx, s

oh Jeff,

this is the link that i forgot to post above where you advised about printing from Excel and not from
Adobe:  tx, s

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_24530934.html

Hi Everyone,

related post:
    http://www.experts-exchange.com/Microsoft/Applications/Q_24770676.html

tx, s