asked on

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

Jeffrey Coachman

" 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...

ASKER CERTIFIED SOLUTION

Jeffrey Coachman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

martinezjrj

ASKER

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

Jeffrey Coachman

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

martinezjrj

ASKER

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

martinezjrj

ASKER

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

martinezjrj

ASKER

Jeff is just good. His solution was so simple and sweet! The bomb!!

atta boi!!

Jeffrey Coachman

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