outputing individual records sep

AI Training: For Busy Decision Makers & Professionals Book now

Forum home » Delegate support and help forum » Microsoft Access VBA Training and help » Outputing individual records to seperate PDF via VBA

Outputing individual records to seperate PDF via VBA

resolvedResolved · Urgent Priority · Version 2016

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

Wed 8 May 2019: Automatically marked as resolved.

 

Training courses

 

Training information:

Welcome. Please choose your application (eg. Excel) and then post your question.

Our Microsoft Qualified trainers will then respond within 24 hours (working days).

Frequently Asked Questions
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.


 

Access tip:

Deleting duplicate records from a table

You cannot delete records tables where there duplicate records. A way around this is to create a new table which wont hold the duplicates. and then deleting the old table.

1. Use a make-table query based on this table only. IMPORTANT - Ensure that you include all of the fields from your original table or you may loose data.

2. Open the query's property sheet by using VIEW, QUERY, PROPERTIES, and set the Unique Values property to Yes

3. Because you have selected the Unique Values to Yes when you run the query, Access creates a new table without duplicate records. You can now delete the old table and rename the new one.

View all Access hints and tips


Server loaded in 0.12 secs.