Access Multiple Reports Print to PDF

Thank you for your help in this area!!

I have a report named "rptUserReport"  It contains a list of usernames, file names, and file paths.  This is what I'm trying to do:
1. Generate individual reports for each user in my table
2. Once report is generated, create/export to PDF and name the file based on the username field

I created a form with a command button and I managed to do this, but it bombs out OR does not yield the results i'm looking for:

Private Sub Command1_Click()
Dim rst As DAO.Recordset
Dim CustomerID As Long
Dim CustomerName As String

Set rst = CurrentDb.OpenRecordset("qryUser", dbOpenSnapshot)
With rst
    Do Until .EOF
        CustomerID = !Users_ID
        CustomerName = !Users
        DoCmd.OpenReport "rptUsers", acViewReport, , "Users_ID=" & CustomerID
        Name "Users1.pdf" As "Rpt_" & CustomerName & ".pdf"
        CustomerID = 0
        CustomerName = ""
        .MoveNext
    Loop
    .Close
End With
Set rst = Nothing
End Sub

I'm not at all sure that the above is the best approach.  It looked like it was working, but it wasn't.  Is this possible?

Thank you in advance for your direction and assistance!

Joel
martinezjrjAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jeffrey CoachmanMIS LiasonCommented:
" It looked like it was working, but it wasn't."
Can you be a bit more specific as to what is, or is not happening when you say "Not Working"?
Thanks


In a nutshell, you should have a Users table and use this to loop trough...
0
Jeffrey CoachmanMIS LiasonCommented:
You also did not mention the version of Access you are using...

In access versions prior to 2007, this is more complex...

In Access 2007 you need to install the PDF addin: http://www.microsoft.com/download/en/details.aspx?id=9943
...with access 2010 PDF functionality is built in


The code might look something like this:

Dim rst As DAO.Recordset
Dim strReportName As String

Set rst = CurrentDb.OpenRecordset("tblUsers")
strReportName = "rptFilePaths"

    rst.MoveFirst
    Do While Not rst.EOF
        DoCmd.OpenReport strReportName, acViewPreview, , "UserID=" & rst!UserID, acHidden
        DoCmd.OutputTo acOutputReport, strReportName, acFormatPDF, "C:\YourFolder\" & rst!UserName & ".pdf"
        DoCmd.Close acReport, strReportName
        rst.MoveNext
    Loop
   
    MsgBox "Done."
   
rst.Close
Set rst = Nothing


...sample attached.

I am sure you can modify this to fit your needs.

;-)

JeffCoachman
Database46.accdb
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
martinezjrjAuthor Commented:
Well for starters, when it reached the part in the code that says

Name "Users1.pdf" As "Rpt_" & CustomerName & ".pdf"

It was looking for an actual file named user1.pdf at start. i had to place a blank user1.pdf document in the folder to get things going.  Then it would NOT "move next" and so it would try to recreate the same report it had just created.  I tried a few things but it would hang up on something new everytime.  The truth is i found the code online and tried to customize as best i could.

and i'm using Access 2010

AND...

ITS WORKING!!! i have to generate 200+ reports so its still chugging along!

YAHOOOO!!!

Thank you Coach!! Thank you Sir!!

I will post back results in a few min.

Joel
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Jeffrey CoachmanMIS LiasonCommented:
Actually the hang up was probaly that you did not have a folder "C:\YourFolder", ...or the report was not created yet.

This is why I posted a fully functioning sample for you to study, test and modify.

;-)

Jeff
0
martinezjrjAuthor Commented:
First, let me thank you in advance for the assist! I appreciate your time on this.  Everything is working just fine, but i did run into one small issue.

When i click the command button that runs the VB code, i get the reports as i would expect.  However, it seems as if theres a loop somehwere.  In my table i'll have:

ID | Username | FileName | FilePath
---------------------------------------------
1  | user1        | file1.doc  | \\servername\share\
2  | user1        | file2.doc  | \\servername\share\
3  | user1        | file3.doc  | \\servername\share\
4  | user2        | file4.doc  | \\servername\share\
5  | user2        | file5.doc  | \\servername\share\
6  | user2        | file6.doc  | \\servername\share\
7  | user3        | file6.doc  | \\servername\share\
8  | user3        | file6.doc  | \\servername\share\
9  | user3        | file6.doc  | \\servername\share\

When the code runs for the first record in the table it grabs all records for user1 and creates the report just fine.  In this case my first pdf will be named user1.pdf and contain a report with 3 records.  same for the others.  However, it seems to be generating a new report for user1 everytime it .moves next? does that make sense??  i noticed this becasue  the modified date for user1.pdf kept changing because it was being overwritten.  so essentially, because there are 3 records in the table, the file gets created 3 different times.  I guess it's not really a loop because i think it will eventually finish, but it is taking some time to do so.

My files table has over 10,000 records with approx 200 unique users.

I hope i'm making sense!!

Thanks

Joel



0
martinezjrjAuthor Commented:
Figured it out.  I checked to see if the file existed at the destination by using a function example i found here:
http://vbadud.blogspot.com/2007/04/vba-function-to-check-file-existence.html

Coupled with your exceptional code and VOILA!!

Here's the finished VBA code for the command button:

Option Compare Database

Private Sub Command1_Click()
Dim rst As DAO.Recordset
Dim strReportName As String
Dim sFile
Set rst = CurrentDb.OpenRecordset("qryUser")
strReportName = "rptUsers"

    rst.MoveFirst
    Do While Not rst.EOF
     sFile = "C:\Users\Desktop\ReportsHere2\" & rst!Users & ".pdf"
        If File_Exists(sFile) = False Then
            DoCmd.OpenReport strReportName, acViewPreview, , "Users_ID=" & rst!Users_ID, acHidden
            DoCmd.OutputTo acOutputReport, strReportName, acFormatPDF, "C:\Users\Desktop\ReportsHere2\" & rst!Users & ".pdf"
            DoCmd.Close acReport, strReportName
        Else
           End If
        rst.MoveNext
    Loop
   
    MsgBox "Done."
   
rst.Close
Set rst = Nothing
End Sub



Private Function File_Exists(ByVal sPathName As String, Optional Directory As Boolean) As Boolean

'Returns True if the passed sPathName exist
'Otherwise returns False
On Error Resume Next
If sPathName <> "" Then

If IsMissing(Directory) Or Directory = False Then

File_Exists = (Dir$(sPathName) <> "")
Else

File_Exists = (Dir$(sPathName, vbDirectory) <> "")
End If

End If
End Function

I'm a happy camper!

Thank you!!

Joel
0
martinezjrjAuthor Commented:
Jeff is just good.  His solution was so simple and sweet!  The bomb!!

atta boi!!
0
Jeffrey CoachmanMIS LiasonCommented:
Thanks, glad I could help.


...And you should congratulate yourself for being able to independently troubleshoot and modify my code to fit your needs.

;-)

Jeff
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.