I am completely new to Visual Basic as a coding language coming mainly from Java and SQL but rusty in both of them
in current role I am working with more Microsoft Access Databases but I need to manipulate them to do what I want.
Currently I am trying to a database to export every row of a table to its own PDF file. from looking up and following tutorials I have found that the best way to do this would be to create a report and make it a recordset then run a loop to go through each page and export it
I have looked at a good few places and searched here for splitting the report and exporting but I am only seeing some ways of splitting it into 3rds but I cannot see anything to do each row.
I may have this completely wrong but from looking up stuff and checking tutorials this is how I thought it should go but when I put it into Access I keep getting errors about end of statements?
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT DISTINCT ID FROM Import")
Do While Not rs.EOF
expression.OpenReport(export, View, FilterName,
WhereCondition, WindowMode)
DoCmd.OpenReport "Import",<..>,,"ID=" & rs!ID
DoCmd.outputTo
acoutputreport, Import
acFormatPDF
rs!Jobnumber & ".PDF"
rs.moveNext
Loop
end sub
6 Replies 6
Right idea to loop through recordset and open filtered report.
Posted code is not valid VBA.
- Remove expression line.
- Remove <..>.
- Need line continuation characters.
- Need commas between DoCmd.OutputTo arguments.
- Report name needs to be within quotes.
Close report after PDF output.
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT DISTINCT ID FROM Import")
Do While Not rs.EOF
DoCmd.OpenReport "Import", , , "ID=" & rs!ID
DoCmd.OutputTo _
acOutputReport, "Import", _
acFormatPDF, _
rs!Jobnumber & ".PDF"
DoCmd.Close acReport, "Import", acSaveNo
rs.MoveNext
Loop
If ID is a primary key, it should be unique in table and DISTINCT is superfluous.
Probably not providing a full folder/file path with rs!Jobnumber & ".PDF".
Thank you this was my attempt after looking at multiple solutions and piecing together parts that I through were relevant which I know isn't the best way to go about it but if I was to set a specific path in the export command how is that formatted? and would it need to be the full path if it was going to be on a network drive?
I am planning to learn more VBA but unsure where/ how to start. I looked up syntax and where i was learning did not mention anything about line continuation characters but it does make sense coming from java where the way you ended a statement was ;
Thank you so much.
I did try to run the code but its throwing and exception
runtime error 2487
object argument is blank or invalid. I did confirm the field i was using didnt have any blank entries but i feel it could be something on how my table is formatted?
DoCmd.OutputTo _
acOutputReport, , _
acFormatPDF, _
rs!ID & ".PDF"
So, a few things:
Of course you need to set/have/use a full path name to where the PDF file is to be outputted.
And of course, you need some "means" to create a unique file name. You could I suppose start at 1, say like this:
1.PDF
2.PDF
3.PDF
etc. etc. etc.
However, often if the data row in question has some kind of quote number, job number etc.? Then we use that column from the database as "part of" the file name we are to create for the one row export.
So, we have this concept:
The required path name (save location folder) + Some column such as QuoteNumber + .PDF extension.
So, you need code to create that file name in the recordset loop to export each row of data to a given PDF file name.
As a FYI, we have to use two commands for this export.
First command to open the report (with a filter – we will filter report to our one row of data).
Then we have the command to export that open report to a PDF.
It turns out that the command to export the report to a PDF does NOT have a filter option. It thus will automatic export all rows for the given report.
However, as noted, by opening the report with a filter to one row, then when we run the export report command, it will only export the row(s) that the current report is showing – in this case only one row.
Solution:
Open the report with a filter to the ONE row, and then run the export PDF command – it will export the open report with the 1 row of data to the PDF. When done, we MUST close the report.
So, the above is the basic logic.
So, you have not shared "how" you want the file name to be created. As noted, we "assume" that you have some column in the database, such as JobNumber etc. ONLY YOU can share if such a column exists – if not, then I suppose an incrementing number, and perhaps say today's date could be used. Say like this:
2025年11月14日-1.PDF
2025年11月14日-2.PDF
2025年11月14日-3.PDF
2025年11月14日-4.PDF
Etc. etc. etc.
So, you have not shared how or what the file name is to look like. (so, we all here having to play a guessing game).
So, assuming you create a new form, and a button on it to export the records?
Say like this:
And now the export code behind the button:
Private Sub Command0_Click()
Dim sPath As String ' path name where pdfs are saved
Dim sFile As String ' name of PDF file to save
Dim sReport As String ' name of Report to export
Dim rs As DAO.Recordset
sPath = "\\SERVER1\PdfExports\" ' include the trailing "\" for path name
sReport = "Import"
Set rs = CurrentDb.OpenRecordset("SELECT * FROM Import")
Do While rs.EOF = False
DoCmd.OpenReport sReport, acViewPreview, , "ID = " & rs!ID, acHidden
sFile = sPath & rs!SomeColumnHereFromTable & ".PDF"
DoCmd.OutputTo acOutputReport, sReport, acFormatPDF, sFile
' now that PDF is exported to the file, we MUST close the report
DoCmd.Close acReport, sReport
rs.MoveNext
Loop
rs.Close
MsgBox "Export complete"
End Sub
Now, of course in the above code the name of the routine (Private Sub Command0_Click()) will be generated by MS-Access when you build the button click event. So, really, you ONLY need to enter the code inside of the Sub and Sub End part - your Sub name may very well be different than above. So, let MS-Access create the Sub name (button event Sub name) for you.
As noted, in above, we NEED to know how you plan to create a file name for the output. It could be based on today's date, some column from the database, or even just an incrementing number. Without this information, we really can't (yet) create a working solution.
So, yes, you need to setup and include the path name.
So, yes, you need to create a "unique" file name for each PDF.
So, yes, you need to add the ".PDF" to the file extension for export.
That has worked perfectly. the date is being imported from spreadsheet so the only field I was confident on have data was the ID field as a quick look through it showed blanks and duplicates in other fields