Export Individual PDFs (1 Viewer)

alexfwalker81

Member
Local time
Today, 14:01
Joined
Feb 26, 2016
Messages
93
I use a database to import data, which is then validated and reformatted. At present, the information is exported to Excel, which is then imported into Word in a MailMerge. I use a plugin in Word called 'Split the Merge' (or something like that), which takes my MailMerge and saves individual documents or PDFs. This is so that I have a separate entry for every line in my database table. Possibly a niche thing to have to do, but the context is that each line in the table is an ingredients list for a product, so each product then has its own output sheet. As you can see though, it's inelegant and has a few inherent limitations.

What I would like to be able to do is skip whole Excel > Word > Mail Merge bit. This is easily achieved by simply writing a report in Access which looks the same as my Word Mail Merge template. I can export this report in PDF format to wherever I like. However, the bit that I don't know how to do is do a separate report for every line in my table.

I'm thinking that I'd need a function to loop through each line a query or table, exporting a PDF each time, until it gets to the end. I just don't know how on earth to do that...
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 21:01
Joined
Jul 9, 2003
Messages
16,245
I answered a similar question a while back and I made it into a Blog on my website here: -


If your intention is to email the reports then I also provide some links on that blog which go to Gina Whipps website:-


where she provides some excellent information on how to email multiple reports.
 
Last edited:

Gasman

Enthusiastic Amateur
Local time
Today, 21:01
Joined
Sep 21, 2011
Messages
14,047
Here is some code that I created a good few years back to do pretty much the same thing.
This produces a report for each ship in a table to pdf.

Code:
Sub Print_All_Ships()
 Dim rs As DAO.Recordset
 Dim db As Database
 Dim stSQL As String, stDate As String, stDBpath As String, stFTPpath As String
 Dim stRptName As String, stParam As String, stLinkCriteria As String, stAlphabet As String, astAlpa(1, 26) As String
 Dim stStart As String, stEnd As String, iloop As Integer
 Dim iOKCancel As Integer
  
 ' GoTo rptalpha
  
 stRptName = "Main_by_Ship"
 Set db = CurrentDb
' Generate all the Ship reports
' GoTo rptleave:
stDBpath = CurrentProject.Path & "\"
stFTPpath = stDBpath & "Gazette\"

 stSQL = "SELECT Ship.Ship FROM Ship WHERE (((Ship.ID)<> 26 and (Ship.ID)<> 27))ORDER BY Ship.Ship"

 Set rs = db.OpenRecordset(stSQL)

 Do While Not rs.EOF
 ' Need to convert any spaces in ship name to _ for website
    stParam = LCase(Replace(rs!Ship, " ", "_"))
    stLinkCriteria = "[Ship] = '" & rs!Ship & "'"
    
    'DoCmd.CopyObject , stParam, acReport, stRptName
    DoCmd.OpenReport stRptName, acViewPreview, , stLinkCriteria
 ' Pause for 5 seconds to save report
    'Pause (5)
    DoCmd.OutputTo acOutputReport, stRptName, acFormatPDF, stFTPpath & stParam & ".pdf", False
    DoCmd.Close acReport, stRptName
    'DoCmd.DeleteObject acReport, stParam

    rs.MoveNext
    
'    iOKCancel = MsgBox("OK to proceed?", vbOKCancel)
'    If iOKCancel = vbCancel Then
'        Exit Sub
'    End If
    
 Loop
 rs.Close
End Sub

HTH
 

alexfwalker81

Member
Local time
Today, 14:01
Joined
Feb 26, 2016
Messages
93
Code:
Public Function Print_All_IF_FIR()

 Dim rs As DAO.Recordset
 Dim db As DAO.Database
 Dim stSQL As String, stDate As String, stDBpath As String, stFTPpath As String
 Dim stRptName As String, stParam As String, stLinkCriteria As String, stAlphabet As String, astAlpa(1, 26) As String
 Dim stStart As String, stEnd As String, iloop As Integer
 Dim iOKCancel As Integer
  
  
 stRptName = "rpt_final_export_IF_FIR"
 Set db = CurrentDb

' Generate all the FIR reports

 stDBpath = CurrentProject.Path & "\"
 stFTPpath = "U:\FIR\2020 v3\IF FIR\Alex_Testing\"

 stSQL = "SELECT ""tbl_final_export.Product VH CODE"" FROM tbl_final_export"

 Set rs = db.OpenRecordset(stSQL)

 Do While Not rs.EOF
    
    DoCmd.OpenReport stRptName, acViewPreview, , stLinkCriteria
 
    'Pause for 5 seconds to save report
    'Pause (5)
    'DoCmd.OutputTo acOutputReport, stRptName, acFormatPDF, stFTPpath & stParam & ".pdf", False
    DoCmd.OutputTo acOutputReport, "rpt_final_export_IF_FIR", acFormatPDF, "U:\FIR\2020 v3\IF FIR\Alex_Testing\test.pdf", False
    DoCmd.Close acReport, stRptName
    'DoCmd.DeleteObject acReport, stParam

    rs.MoveNext
    
   
 Loop
 rs.Close

End Function

Qualified success... I've hacked your code about and this 'works'. In other words, it exports successfully, but what it's not doing is a report per line in the source table. I'm aware of course that the absolute path is just overwriting each time, but I'll sort that later. The immediate problem is that it's not splitting the report up.
 

Gasman

Enthusiastic Amateur
Local time
Today, 21:01
Joined
Sep 21, 2011
Messages
14,047
You are not setting the stLinkCriteria for each record?

As for the outputfile look how I was doing it.? This gave me a pdf file of ship name to upload to the website. www.bibby-gazette.co.uk
I did the same for Dates in my DB.
 

alexfwalker81

Member
Local time
Today, 14:01
Joined
Feb 26, 2016
Messages
93
You are not setting the stLinkCriteria for each record?

As for the outputfile look how I was doing it.? This gave me a pdf file of ship name to upload to the website. www.bibby-gazette.co.uk
I did the same for Dates in my DB.

I had inadvertently taken out the stLinkCriteria, thinking it did something else...

This is the new code;

Code:
Public Function Print_All_IF_FIR()

 Dim rs As DAO.Recordset
 Dim db As DAO.Database
 Dim stSQL As String, stDate As String, stDBpath As String, stFTPpath As String
 Dim stRptName As String, stParam As String, stLinkCriteria As String, stAlphabet As String, astAlpa(1, 26) As String
 Dim stStart As String, stEnd As String, iloop As Integer
 Dim iOKCancel As Integer
  
  
 stRptName = "rpt_final_export_IF_FIR"
 Set db = CurrentDb

' Generate all the FIR reports

 stDBpath = CurrentProject.Path & "\"
 stFTPpath = "U:\FIR\2020 v3\IF FIR\Alex_Testing\"

 stSQL = "SELECT ""tbl_final_export.Product VH CODE"" FROM tbl_final_export"

 Set rs = db.OpenRecordset(stSQL)

 Do While Not rs.EOF
 
  ' Need to convert any spaces in ship name to _ for website
    stParam = LCase(Replace(rs!tbl_final_export, " ", "_"))
    stLinkCriteria = "[Product VH Code] = '" & rs!tbl_final_export & "'"
    
    DoCmd.OpenReport stRptName, acViewPreview, , stLinkCriteria
 
    'Pause for 5 seconds to save report
    'Pause (5)
    'DoCmd.OutputTo acOutputReport, stRptName, acFormatPDF, stFTPpath & stParam & ".pdf", False
    DoCmd.OutputTo acOutputReport, "rpt_final_export_IF_FIR", acFormatPDF, "U:\FIR\2020 v3\IF FIR\Alex_Testing\test" & stLinkCriteria & ".pdf", False
    DoCmd.Close acReport, stRptName
    'DoCmd.DeleteObject acReport, stParam

    rs.MoveNext
    
   
 Loop
 rs.Close

End Function

When this runs, I get 'Run-time error '3625': Item not found in this collection', on these two lines;

stParam = LCase(Replace(rs!tbl_final_export, " ", "_"))
stLinkCriteria = "[Product VH Code] = '" & rs!tbl_final_export & "'"
 

Gasman

Enthusiastic Amateur
Local time
Today, 21:01
Joined
Sep 21, 2011
Messages
14,047
So 'tbl_final_export' does not exist in your recordset?

That strLinkCriteria is meant to identify each individual record.

You don't need that replace unless you have spaces in that field.? My ships were calledeg "Mersey Bridge" and I could not use spaces for the fielnames on the website, or I did not want to. I would think you shoule be using stParam in the filename notstLinkCriteria as well.?

You need to take time to understand the code, it was meant as an example, just that, an example.
 

alexfwalker81

Member
Local time
Today, 14:01
Joined
Feb 26, 2016
Messages
93
So 'tbl_final_export' does not exist in your recordset?

Yes, that table does exist in the database

You don't need that replace unless you have spaces in that field.? My ships were calledeg "Mersey Bridge" and I could not use spaces for the fielnames on the website, or I did not want to. I would think you shoule be using stParam in the filename notstLinkCriteria as well.?

Understood, but I've put it back in on the basis that you never quite know what rubbish users will put into the system, so it protects against a scenario, albeit unlikely

You need to take time to understand the code, it was meant as an example, just that, an example.

Agreed, doing my best! The problem is that you're dealing with a semi-competent individual here, so I 'get' most of what's happening, but often have to experiment with syntax!
 

Gasman

Enthusiastic Amateur
Local time
Today, 21:01
Joined
Sep 21, 2011
Messages
14,047
You are meant to be using a field in the table? likely
Code:
 stLinkCriteria = "[Product VH Code]=" & rs![Product VH Code]
I'm not even sure your sql would work either?
Use
Code:
stSQL = "SELECT tbl_final_export.[Product VH CODE] FROM tbl_final_export"
and as that is the only field you are retrieving it should be that one?
 
Last edited:

Gasman

Enthusiastic Amateur
Local time
Today, 21:01
Joined
Sep 21, 2011
Messages
14,047
Agreed, doing my best! The problem is that you're dealing with a semi-competent individual here, so I 'get' most of what's happening, but often have to experiment with syntax!
Syntax is not the problem here, it is the logic of what you are trying to do.
You want to be able to identify each record as you retrieve it and pass that data to the report, so that it only works for that one record.?

You get the data from the table, but you address individual records and fields.
 

alexfwalker81

Member
Local time
Today, 14:01
Joined
Feb 26, 2016
Messages
93
Ah, ok - much clearer now. I think what’s thrown me was the Ship.Ship in the original example, so I couldn’t tell which way round tables and fields should be. Will give it a crack shortly.
 

Gasman

Enthusiastic Amateur
Local time
Today, 21:01
Joined
Sep 21, 2011
Messages
14,047
Yes, that DB was created as my very first, so lots of bad habits there, which I have learned no to do over the last few years. :)

Ship is the name of the table, and name of the ship. Nowadays I would use ShipName. :)

1600258795102.png
 

alexfwalker81

Member
Local time
Today, 14:01
Joined
Feb 26, 2016
Messages
93
This works brilliantly;

Code:
Public Function Print_All_IF_FIR()

 Dim rs As DAO.Recordset
 Dim db As DAO.Database
 Dim stSQL As String, stDate As String, stDBpath As String, stFTPpath As String
 Dim stRptName As String, stParam As String, stLinkCriteria As String, stAlphabet As String, astAlpa(1, 26) As String
 Dim stStart As String, stEnd As String, iloop As Integer
 Dim iOKCancel As Integer
  
  
 stRptName = "rpt_final_export_IF_FIR"
 Set db = CurrentDb

' Generate all the FIR reports

 stDBpath = CurrentProject.Path & "\"
 stFTPpath = "U:\FIR\2020 v3\IF FIR\"
  
 stSQL = "SELECT tbl_final_export.[Product VH CODE] FROM tbl_final_export"

 Set rs = db.OpenRecordset(stSQL)

 Do While Not rs.EOF
 
 ' Need to convert any spaces in ship name to _ for website
 stParam = LCase(Replace(rs![Product VH Code], " ", "_"))
 stLinkCriteria = "[Product VH Code] = '" & rs![Product VH Code] & "'"
    
 DoCmd.OpenReport stRptName, acViewPreview, , stLinkCriteria
 
 DoCmd.OutputTo acOutputReport, "rpt_final_export_IF_FIR", acFormatPDF, stFTPpath & rs![Product VH Code] & ".pdf", False
 DoCmd.Close acReport, stRptName
 
 rs.MoveNext
    
   
 Loop
 rs.Close

End Function

What I'd love to do is be able to create a folder with the format "YYYYMMDD" each time, which I thought would be simple, but everything that I've tried seems to fail!
 

alexfwalker81

Member
Local time
Today, 14:01
Joined
Feb 26, 2016
Messages
93
Nailed it - thanks to everyone for your help.

Code:
Public Function Print_All_IF_FIR()

 Dim rs As DAO.Recordset
 Dim db As DAO.Database
 Dim stSQL As String, stDate As String, stDBpath As String, stFTPpath As String
 Dim stRptName As String, stLinkCriteria As String
 Dim stStart As String, stEnd As String, iloop As Integer
 Dim iOKCancel As Integer
  
  
 stRptName = "rpt_final_export_IF_FIR"
 Set db = CurrentDb

' Generate all the FIR reports

 stDBpath = CurrentProject.Path & "\"
 stFTPpath = "U:\FIR\2020 v3\IF FIR\" & Format(Date, "YYYYMMDD") & "_" & DLookup("[run_instance]", "qry_instance")
   
 stSQL = "SELECT tbl_final_export.[Product VH CODE] FROM tbl_final_export"
 
 Set rs = db.OpenRecordset(stSQL)
 
 MkDir stFTPpath

 Do While Not rs.EOF
 
 stLinkCriteria = "[Product VH Code] = '" & rs![Product VH Code] & "'"
    
 DoCmd.OpenReport stRptName, acViewPreview, , stLinkCriteria
 
 DoCmd.OutputTo acOutputReport, "rpt_final_export_IF_FIR", acFormatPDF, stFTPpath & "\" & rs![Product VH Code] & ".pdf", False
 DoCmd.Close acReport, stRptName
 
 rs.MoveNext
    
   
 Loop
 rs.Close

End Function
 

Gasman

Enthusiastic Amateur
Local time
Today, 21:01
Joined
Sep 21, 2011
Messages
14,047
Well done, hopefully you learnt a few things along the way as well. (y)
 

Users who are viewing this thread

Top Bottom