Manny has attended:
Access Intermediate course
Access Advanced course
Outputing individual records to seperate PDF via VBA
I want to output the pdf files based on each record. It means each record will have its own single pdf file and the file name will be based on the record's column name.
i am very close but i get a failure on line and cannot figure out why.
CurrentDb.QueryDefs("myReportRecordSource").SQL = strSQL
ive ripped most of the code from the attached forum. https://stackoverflow.com/questions/51333290/how-to-output-multiple-pdf-files-based-on-record-in-ms-access
Please may you help.
Manny
------------------------------------------------------
Dim strSQL As String
Dim rsGroup As DAO.Recordset
Dim ColumnName As String, myPath As String
myPath = "Z:\24AM\Oxygen\Operations\S&P Challenges\Challenges for " & ddate & "\ICE Challenges " & ddate & "\"
Set rsGroup = CurrentDb.OpenRecordset("SELECT DISTINCT ISIN FROM [S&PChallengesSupportT]", dbOpenDynaset)
Do Until rsGroup.EOF
ISIN = rsGroup![ISIN]
strSQL = "SELECT * FROM [S&PChallengesSupportT] WHERE ISIN = '" & [ISIN] & "';" 'copy sql from the report record source and put in the column name as a variable
CurrentDb.QueryDefs("myReportRecordSource").SQL = strSQL
' OUTPUT REPORT TO FILE
DoCmd.OutputTo acOutputReport, "BrokerSupportS&PR", acFormatPDF, _
myPath & [ISIN] & ".pdf", False
rsGroup.MoveNext
Loop
RE: Outputing individual records to seperate PDF via VBA
Hi Manny,
Thank you for the forum question and thank you for the nice feedback. I am happy that you found the Advanced Access course useful.
It is difficult to test it without having the database, but with my knowledge of SQL, I think there is an error in the following line:
strSQL = "SELECT * FROM WHERE ISIN = '" & [ISIN] & "';"
You select all but you are not defining from where. Without the database but by just reading the code I would try something like:
strSQL = "SELECT * FROM ISIN WHERE ISIN = '" & [ISIN] & "';"
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
RE: Outputing individual records to seperate PDF via VBA
Hey Jens,
i figured it out. I iteritaed over each indentifer [ISIN] and output each record.
-------------------------------------------
Dim rs As DAO.Recordset
Dim MyFileName As String
Dim mypath As String
Dim temp As String
mypath = "Z:\24AM\Oxygen\Operations\S&P Challenges\Challenges for " & ddate & "\"
Set rs = db.OpenRecordset("SELECT DISTINCT ISIN FROM [S&PChallengesSupportT]", dbOpenDynaset)
Do While Not rs.EOF
temp = rs("[ISIN]")
MyFileName = rs("[ISIN]") & ".PDF"
DoCmd.OpenReport "BrokerSupportS&PR", acViewReport, , "[ISIN]='" & temp & "'"
DoCmd.OutputTo acOutputReport, "", acFormatPDF, mypath & MyFileName
DoCmd.Close acReport, "BrokerSupportS&PR"
rs.MoveNext
Loop
Set rs = Nothing
Set db = Nothing
RE: Outputing individual records to seperate PDF via VBA
Well done again Manny.
When I saw your code I remember, that I actually did something similar years ago.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
