Exporting to PDF using fields as the name

Question

(Follow-on from: MS access Split report and export each row as a PDF )

I am exporting files to PDF based on a a report. Initially to get it working I was naming the PDF files after the Table's ID field which worked but now I want to name it after the Job Number Field and the current date as number but when I change the button function to that it's not working. I think the syntax and formatting are fine but it still won't work.

The Code below is the entire button code which works and exports each row as its PDF

Private Sub Toggle2_Click()
   Dim sPath       As String
   Dim sFile       As String
   Dim sReport     As String
   Dim rs          As DAO.Recordset

   sPath = "C:\Users\murtaghd\Documents\files\"
   sReport = "export"
 
   Set rs = CurrentDb.OpenRecordset("SELECT * FROM Import")

   Do While rs.EOF = False

       DoCmd.OpenReport sReport, acViewPreview, , "ID = " & rs!ID, acHidden
       sFile = sPath & rs!ID & ".PDF"
    
       DoCmd.OutputTo acOutputReport, sReport, acFormatPDF, sFile
    
   
    
       DoCmd.Close acReport, sReport
    
       rs.MoveNext

    Loop

   rs.Close

   MsgBox "Export complete"
End Sub

I want to have it work where it uses job number and current date as the file name but it's not working with just the job number when

DoCmd.OpenReport sReport, acViewPreview, , "ID = " & rs!ID, acHidden
sFile = sPath & rs!Job_Number & ".PDF"

I tried formatting the Job_Number a few ways but every time there is an issue running the code

import

Table

What does "not working" mean - error message, wrong result, nothing happens? Show an example of Job_Number value. — June7
– June7, Commented Nov 17 at 17:26
Is the field name really "Job_Number" or is it "Job Number" ? — Tim Williams
– Tim Williams, Commented Nov 17 at 17:58
Maybe try sFile = sPath & rs.Fields("Job Number").Value & ".PDF" — Tim Williams
– Tim Williams, Commented Nov 17 at 18:12
...and to add the date sFile = sPath & rs.Fields("Job Number").Value & "_" & Format(Date, "yyyymmdd") & ".PDF" — Tim Williams
– Tim Williams, Commented Nov 17 at 18:39
If field name is actually "Job Number", this is a good example of why not to use spaces in naming convention. — June7
– June7, Commented Nov 17 at 18:43

Albert D. Kallal · Accepted Answer · 2025-11-17 22:12:13Z

3

The issue looks to be that you have a space in the column name. As noted, if spaces in column names can be avoided, then you should. Many reasons exist, but take this SQL query:

SELECT ID, City, First Name from tblCustomers

In above, we have a column with a space. But, SQL uses spaces for delimiters. So, in above, do we have a column called "First Name from tblcustomers"????

Or even called "First Name from" ????

So, spaces in column names tends to be a bad idea, since then we here, (and SQL) can't know when a column name stops, and then additional SQL key words (also separated by spaces) starts!

The general SQL solution is to introduce [ ] (square brackets) around the column name, so SQL can thus figure out when the column name ends, and additional things like ORDER BY etc. starts......

So, given you look to have a column name called "Job Number"?

Then your code needs to use this:

Do While rs.EOF = False

       DoCmd.OpenReport sReport, acViewPreview, , "ID = " & rs!ID, acHidden
       sFile = sPath & rs![Job Number] & ".PDF"
    
       DoCmd.OutputTo acOutputReport, sReport, acFormatPDF, sFile
    
   
    
       DoCmd.Close acReport, sReport
    
       rs.MoveNext

    Loop

The above would thus output a file with the job number, say like this:

1345.PDF

However, if you also want the date? Then you need/want/should/have to decide on what date format you want?

And you ALSO have to decide if you want spaces in the file name. In most cases, spaces in a file name are ok, but once again, often file names with spaces can trip up the OS.

However, let's assume you want today’s date, and you want this format:

  Job Number + Date + .PDF

So, then our code becomes this:

sFile = sPath & rs![Job_Number] & " " & format(date(), "YYYY-MM-DD") & ".PDF"

Now, above will place the job number first, then the date. And you may well want the date first (so it can sort correctly in a folder by date, and THEN it will sort by job numbers for that day).

So, with above, assuming a job number of 123456, then the above will result in:

123456 2025-11-17.PDF

However, one might prefer having the date first, and hence this:

2025-11-17 123456.PDF

So, it's not clear what format you want, but for the date first, then you have this:

sFile = sPath & format(date(), "YYYY-MM-DD") & " " & rs![Job Number] & ".PDF"

As noted, since you used a space in the column name?

Then in code, then when writing SQL, then when using that column, you forevermore MUST surround that column name with [].

edited Nov 17 at 22:12

answered Nov 17 at 22:00

Albert D. Kallal

50.1k3 gold badges41 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Darren Murtagh Nov 18 at 8:36

Thank you so much. I am quite new to programming within MS Access but I new it had to be due to how the field was setup. unfortunately the spreadsheet comes in from a completely external source that I have no influence over. if I could control it I would call it JobNo or suck

Albert D. Kallal Nov 18 at 15:24

No worries, spaces in field names can well be handled. But as a good FYI and when you have a chance to develop a database, then consider avoiding spaces. Some don't worry too much about this issue -- learning about databases is a big area of information systems, and learning never stops.....

Darren Murtagh Nov 19 at 10:50

yeah the space would not be there if I any control. Just wondering as I'm sure I will be using VBA a lot more. what resource would you reconmend using to learn it. currently im just using W3Schools

Collectives™ on Stack Overflow

Exporting to PDF using fields as the name

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related