print a report as individual pdf files

R

ryannnnstl

Registered User.
Local time
Today, 09:17
Joined
Dec 22, 2009
Messages
17
hello,

I have a report that prints all the transactions for all the accounts into one giant pdf. I email each of the account letters individually to about 35 accounts and right now its a time consuming task to go in to the giant pdf and extract each accounts pdf individually... I would like to be able to have access loop through each account and print a pdf for that individual account into a specified folder.

question is how can I loop through each account and print a pdf for that account and send it to a specified folder?

Thanks in advance!
RM
 
OK. I am a complete newb when it comes to access. I just want to make sure this is what I need before I spend time on it. And also thanks for helping!

I have a query called "transactions" that gathers all the information needed to run the report called "letter single". The output is a huge pdf that has every account and every transaction for all those accounts during the month. So there are multiple accounts in this huge pdf, usually like 40. I usually have to go in and use a pdf splitter to extract the pages for each of the accounts, save those pages (usually as the account number, for example: 012345.pdf, 123456.pdf, 234567.pdf, ect...)). and then email the transaction report for that specific client.

so the big pdf contains reports for account:
12345
23456
34567
45678

it is in one giant pdf. I then have to go in and extract 12345. Save it as a seperate pdf. Goto my email and find the email address for acct 12345 and then send the email.

This code will do this? er, this is code will atleast get me started. I just want to be sure. Thanks again!
 
Last edited:
It will get you started, yes. I would create a second query based on your main query that gets a list of the accounts (SELECT DISTINCT...). Use that query for the recordset loop, and use the technique demonstrated (or one of several others) to restrict the report to the current account.
 
I am a little confused with this OpenRecordset idea. I created a second query based on your main query that gets a list of the distinct accounts. its output is a nice table, called "LetterSingleAcct", of accounts that I need to have loop'd through:
account
123456
234567
345678

So the next step is to store the account in row one, "123456", as a variable and run the report for the single account and have it print out as a pdf.

The problem I am having is that it is not printing out the record for the single account. It is still printing out the huge pdf with all the accounts...

This is what I have so far:

Private Sub Command26_Click()

Dim MyDB As Database, RS As Recordset
Dim strBody As String, lngCount As Long, lngRSCount As Long, acctnum As String


Set prtDefault = Application.Printer
Set Application.Printer = Application.Printers("CutePDF Writer")

Set MyDB = CurrentDb

Set RS = MyDB.OpenRecordset("LetterSingleAcct")
lngRSCount = RS.RecordCount
If lngRSCount = 0 Then
MsgBox "No Info", vbInformation
Else
RS.MoveLast
RS.MoveFirst
Do Until RS.EOF
acctnum = RS!account
strCriteria = "[account]='" & acctnum & "'"
DoCmd.OpenReport "Letter Single", acNormal, strCriteria
RS.MoveNext
Loop

^^ I am pretty sure this is where the problem is. If I understand correctly I set acctnum equal to the first account in the "LetterSingleAcct" query. Then I set the strCriteria equal to the account num. Then I OpenReport with the criteria set. But yet when I run the macro it still prints all the accounts into the report. I'm not even sure if any of this correct. What am I doing wrong?! Please help me!

End If
RS.Close
MyDB.Close
Set RS = Nothing
Set MyDB = Nothing
Close

End Sub
 
First, what is the data type of account in the table? Second, you have the wherecondition in the wrong place; there should be one more comma. Here's a reference on that (this will help on the data type question as well):

http://www.baldyweb.com/wherecondition.htm

I haven't used that method of creating PDF files. I would suspect that it would keep overwriting files until you're left with the last one, but I guess let's fix the other issues and see if that becomes one.
 
Thanks pBaldy!

Adding that comma seems to have done the trick. the acctnum are text, fyi.

Do Until RS.EOF
acctnum = RS!portfolio
strCriteria = "[portfolio]='" & acctnum & "'"
DoCmd.OpenReport "Letter Single", , acNormal, strCriteria
RS.MoveNext
Loop

It all works good. What pdfcreator would you recommend I use, instead of CutePdf, so that I can automatically save the pdf's as the acctnum in a specified folder? Cute Pdf works but it prompts me with a Save As window and asks me to specify where i want to save the file.

Thanks again!
 
I wasn't saying anything negative about CutePdf; I've just never used it. It may be the best tool for the job. I've used this numerous times:

http://www.lebans.com/reporttopdf.htm

You could pass the current account number as part of the file name using this method.
 
I got it working using lebans but it is back to printing the huge pdfs again. How would I apply the "strcriteria" filter in this function? I'm pretty sure i dont need the Do.Cmd.OpenReport... I may be wrong tho

Do Until RS.EOF
acctnum = RS!portfolio
strcriteria = "[portfolio]='" & acctnum & "'"
blRet = ConvertReportToPDF("Letter Single", vbNullString, _
acctnum & ".pdf", False, True, 150, "", "", 0, 0, 0)
'DoCmd.OpenReport "Letter Single", , acNormal, strcriteria
RS.MoveNext
Loop
 
You don't need the OpenReport, but you're back to filtering using some method such as the one in the first link I sent. Here's code I have creating/emailing PDF files for each selected customer. Note that it puts the current customer in a textbox:
Code: 
  Do While Not rs.EOF
    Forms!frmStatementFilter.txtEmailCust = rs!CustomerAccount

    blRet = ConvertReportToPDF(strReport, vbNullString, _
                               "c:\" & rs!CustomerAccount & "Statement.pdf", False, False, 0, "", "", 0, 0)
    DoEvents

  'bunch of email code

    rs.MoveNext
  Loop
Here's the code in the report:
Code: 
  If Not IsNull(Forms!frmStatementFilter.txtEmailCust) Then 'we're emailing
    Me.RecordSource = "StatementData"
    Me.Filter = "CustomerAccount=" & Forms!frmStatementFilter.txtEmailCust
    Me.FilterOn = True
  End If

The If statement is there because I may open the same report for preview, and in that event I don't want it filtering this way. You also wouldn't need the RecordSource line; mine changes.
 
If i understand it correctly i need to add the me.filter to the onOpen event in the report. How do I set up the onOpen event in the report? I found onOpen in the properties of the report but it didnt work. Where do I put

Code: 
Me.Filter = strcriteria
Me.FilterOn = True
^^ i don't know where and how to use this

Do i add it to vba code behind the report? I'm confused on how to set up the onOpen event for the report...

private sub command_click()

Code: 
Do Until RS.EOF
            acctnum = RS!portfolio
            strcriteria = "[portfolio]='" & acctnum & "'"
            blRet = ConvertReportToPDF("Letter Single", vbNullString, _
            "c:\" & acctnum & ".pdf", False, False, 150, "", "", 0, 0, 0)
            
            RS.MoveNext
Loop
 
Yes, you'd add that as VBA code behind the report, in its open event.
 
ok i think ive got it, but for some reason I dont think its running correctly. Its not sending the file to the correct location. its sending it to something like
c:\docume~1\user\locals~1\temp\snp28c.snp
everything looks good, I think. Do you see anything that may need fixing?

Code: 
Private Sub Command26_Click()
    
    Dim MyDB As Database, RS As Recordset
    Dim blRet As Boolean
    Dim strBody As String, lngCount As Long, lngRSCount As Long, acctnum As String, filename As String
    
    Set MyDB = CurrentDb

    Set RS = MyDB.OpenRecordset("LetterSingleAcct")
    lngRSCount = RS.RecordCount
    If lngRSCount = 0 Then
      MsgBox "No promo email messages to send.", vbInformation
    Else
      RS.MoveLast
      RS.MoveFirst
      Do Until RS.EOF
            acctnum = RS!portfolio
            strcriteria = "[portfolio]='" & acctnum & "'"
            blRet = ConvertReportToPDF("Letter Single", vbNullString, _
            "c:\" & acctnum & ".pdf", False, False, 150, "", "", 0, 0, 0)
            RS.MoveNext
Loop
    
    End If
    RS.Close
    MyDB.Close
    Set RS = Nothing
    Set MyDB = Nothing
    Close

End Sub

Code: 
Private Sub Report_Open(Cancel As Integer)

   Me.Filter = strcriteria
   Me.FilterOn = True
    
End Sub
 
The lebans method does create a snapshot file, but I think it deletes it. That code looks like it should put the PDF on C:\. That's not happening? Can you post the db? I haven't had a problem with that code not putting the file where it should.
 
nvm, i didnt realize i needed to add the clCommonDialog to the class module list...

It's printing pdf to the location with the filename as the account number but it is still printing all the pages. it looks like filter may not be working correctly...
 
Last edited:
I meant the database itself, so I could play with it. I've used the Lebans code many times, without having that problem, so there is something unique in your database. If it contains sensitive info, then certainly we don't want you uploading that.
 
Edit: Snap, I didn't see the reply on the second page. I think this is most of the code that the button uses. I added in additional code that was behind the report that formats the Page Headers. Either thats the problem causing the conflict or its something in the Report_letter Single properties... I think. Again thanks for the help pbaldy.

I'm still having problems with getting the filter to load before each report. Currently it prints out the massive file. Do I have to set the recordset back to Letter Single before I format the report header? Right now its set on LetterSingleAcct (which is the nice list of accounts that we made a query for earlier), I believe, but Letter Single is where all the data in the report is...

This is what i have for the code behind the Report_Letter Single:
Code: 
Option Compare Database
'Option Explicit



Dim GrpArrayPage(), GrpArrayPages()
Dim GrpNameCurrent As Variant, GrpNamePrevious As Variant
Dim GrpPage As Integer, GrpPages As Integer

Dim FirstPass As Boolean

Private Sub Report_Open(Cancel As Integer)
  
  Me.Filter = strcriteria
  Me.FilterOn = True
    
End Sub

Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As Integer)
    ' When the report previews or prints, the format events will fire twice.
    ' First to calculate total pages, Second to render to preview window or printer
    ' Need to know which pass this is.
    FirstPass = Not FirstPass
End Sub


Private Sub PageHeaderSection_Format(Cancel As Integer, FormatCount As Integer)
Dim i As Integer
  

  If FirstPass Then
    ReDim Preserve GrpArrayPage(Me.Page + 1)
    ReDim Preserve GrpArrayPages(Me.Page + 1)
    GrpNameCurrent = Me!portfolio & Me!CC
    If GrpNameCurrent = GrpNamePrevious Then
        GrpArrayPage(Me.Page) = GrpArrayPage(Me.Page - 1) + 1
        GrpPages = GrpArrayPage(Me.Page)
            For i = Me.Page - ((GrpPages) - 1) To Me.Page
                GrpArrayPages(i) = GrpPages
            Next i
    Else
        GrpPage = 1
        GrpArrayPage(Me.Page) = GrpPage
        GrpArrayPages(Me.Page) = GrpPage
    End If
  Else
    If GrpArrayPage(Me.Page) > 1 Then
        Me!HeaderLogo.Visible = False
        Me!ctlGrpRE.Visible = True
        Me!CtlGrpDate.Visible = True
        Me!ctlGrpPages.Visible = True
    Else
        Me!HeaderLogo.Visible = True
        Me!ctlGrpRE.Visible = False
        Me!CtlGrpDate.Visible = False
        Me!ctlGrpPages.Visible = False
    End If
    Me!ctlGrpPages = "Page " & GrpArrayPage(Me.Page) '& " of " & GrpArrayPages(Me.Page)
  End If
  GrpNamePrevious = GrpNameCurrent
End Sub
And this is the code behind the button:
Code: 
Private Sub Command23_Click()
    Dim MyDB As Database, RS As Recordset
    Dim blRet As Boolean
    Dim strBody As String, lngCount As Long, lngRSCount As Long, acctnum As String, FileName As String, strCriteria As String
    
    
    
    Set MyDB = DBEngine.Workspaces(0).Databases(0)
    
    Set RS = MyDB.OpenRecordset _
      ("LetterSingleAcct")
      ' sets recordset to LetterSingleAcct
    lngRSCount = RS.RecordCount
      ' counts number of records in LetterSingleAcct
    If lngRSCount = 0 Then
      MsgBox "No promo email messages to send.", vbInformation
    Else
      RS.MoveLast
      RS.MoveFirst
      
     Do Until RS.EOF
           acctnum = RS!portfolio
           
           blRet = ConvertReportToPDF("Letter Single", vbNullString, _
            "C:\" & acctnum & ".pdf", False, True, 150, "", "", 0, 0, 0)
                'runs pdf report
        
          RS.MoveNext
                'moves to next recordset
    Loop
    
    End If
    RS.Close
    MyDB.Close
    Set RS = Nothing
    Set MyDB = Nothing
    Close
End Sub

Any idea? Thanks in advance!
 
Last edited:
I really think something is wrong with the acctnum variable in the report_Open statement or in the properties of the "letter Single" report. I can manually run the report for each account by going into the "Letter Single" report properties in the VB window and changing it to portfolio="cs12345", saving, and then I run the report in the Access environment.

Right now the code above saves a huge pdf for each individual account in the correct location. I just can't get the filter to run onOpen correctly...

Private Sub Report_Open(Cancel As Integer)

Me.Filter = "portfolio='" & acctnum & "'"
Me.FilterOn = True

End Sub

I'll be working on it more tomorrow.
 
Last edited:
What is the data type of portfolio? Also, since you use acctnum in multiple objects, it should be declared in a standard module rather than in the procedure:

Public acctnum As String

I think if you didn't have Option Explicit commented out, you'd get a compile error. I would have Option Explicit at the top of every module.
 
portfolio, as in the types of characters that make up, can sometimes be numbers and sometimes be numbers + letters.

example: cs1234 or 123456

so i can type in portfolio="123456" or portfolio="cs1234" in the filter and it pulls up a single report for that portfolio.
 
You must log in or register to reply here.

Similar threads

T
Export report in Desktop with account name
Replies
2
Views
366
Pat Hartman
P
G
Text recognition question
2
Replies
22
Views
680
Pat Hartman
P
O
Display a pdf-file in a report?
Replies
6
Views
635
ComputerVersteher
C
G
Solved Data from form not showing on Statement of Account report
2
Replies
21
Views
1,050
GaP42
G
P
Create seperate PDF Files from a report Base on a query with more than 300 records
Replies
5
Views
802
Uncle Gizmo
Uncle Gizmo

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom