Solved Saving separate reports to PDF using VBA

[neilsolaris]

Hi,

I have a report, which is linked to data in a query. The query is a list of invoices, and the first field is the unique invoice number.

If I filter the query by invoice number, and save it, when I open the report, it shows just that one invoice, so I am able to save it as a PDF manually. Is anyone able to help me with VBA code in order to loop through all the invoices, and save them as separate PDF files please?

I have copied and adapted some code I found online. It doesn't work yet, but then I wasn't expecting it to to be honest! The name of the invoice number field in the query is Invoice_Number. The query is called Q_Invoices and the report is called R_Invoices_PDF.

Thanks for your help.

</>Private Sub cmd_GenPDFs_Click()

    Dim rs                    As DAO.Recordset

    Dim sFolder               As String

    Dim sFile                 As String



    On Error GoTo Error_Handler



    sFolder = "D:\Documents\Orchestra\Invoices\Invoice files\"



    Set rs = CurrentDb.OpenRecordset("SELECT Invoice_Number FROM Q_Invoices", dbOpenSnapshot)



    With rs

        .MoveFirst

        Do While Not .EOF

            DoCmd.OpenReport "R_Invoices_PDF", acViewPreview, , "[Invoice_Number]=" & ![Invoice_Number], acHidden

            sFile = Nz(![Invoice_Number], "") & ".pdf"

            sFile = sFolder & sFile

            DoCmd.OutputTo acOutputReport, "R_Invoices_PDF", acFormatPDF, sFile

            'If you wanted to create an e-mail and include an individual report, you would do so now

            DoCmd.Close acReport, "R_Invoices_PDF"

            .MoveNext

        Loop

    End With



    Application.FollowHyperlink sFolder    'Optional / Open the folder housing the files



Error_Handler_Exit:

    On Error Resume Next

    If Not rs Is Nothing Then

        rs.Close

        Set rs = Nothing

    End If

    Exit Sub



Error_Handler:

    If Err.Number <> 2501 Then    'Let's ignore user cancellation of this action!

        MsgBox "The following error has occured" & vbCrLf & vbCrLf & _

               "Error Number: " & Err.Number & vbCrLf & _

               "Error Source: cmd_GenPDFs_Click" & vbCrLf & _

               "Error Description: " & Err.Description & _

               Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _

               , vbOKOnly + vbCritical, "An Error has Occured!"

    End If

    Resume Error_Handler_Exit

End Sub</>

 

[Gasman]

Walk through the code with F8.
I am viewing on phone, but it looks correct to me.?

 

[neilsolaris]

I've tested it with F8 a few times. It goes to error with this line.

DoCmd.OpenReport "R_Invoices_PDF", acViewPreview, , "[Invoice_Number]=" & ![Invoice_Number], acHidden

Error description: Data type mismatch in criteria expression.

 

[Gasman]

Ok, is Invoice Number text?, if so it needs to be surrounded by ' at the least.
Using the immediate window and Intellisense I get

docmd.OpenReport "rptHours",acpreview,,"employeeid=" & 4,acWindowNormal

and it works fine.

 

[Uncle Gizmo]

I blogged an answer to a specific question on MS Access world forums on my website here:-

Generate Multiple Reports

Generate Multiple Reports – Nifty Access

www.niftyaccess.com

It might provide you some inspiration, or possibly perspiration!

 

[neilsolaris]

Ok, is Invoice Number text?, if so it needs to be surrounded by ' at the least.
Using the immediate window and Intellisense I get

docmd.OpenReport "rptHours",acpreview,,"employeeid=" & 4,acWindowNormal

and it works fine.

Exactly, invoice number is text! I'll give your suggestion a go now, and I'll let you know how I get on.

I forgot about the intermediate window. I'll try that too.

Thanks for your help.

Edit. I changed that line of code to...

DoCmd.OpenReport "R_Invoices_PDF", acViewPreview, , "['Invoice_Number']=" & !['Invoice_Number'], acHidden

And it comes up with an error message "Item not found in this collection". I can play around with it though.

I'm also wondering, doesn't that line of code filter the report, not the query the report is based on?

 

[neilsolaris]

I blogged an answer to a specific question on MS Access world forums on my website here:-

Generate Multiple Reports

Generate Multiple Reports – Nifty Access

www.niftyaccess.com

It might provide you some inspiration, or possibly perspiration!

Many thanks, I'll have a look in a second.

 

[neilsolaris]

Ok, I feel like I'm making a little progress. My invoice numbers run from 1064 to 1088. As an experiment I changed the above line of code to...

DoCmd.OpenReport "R_Invoices_PDF", acViewPreview, , "Invoice_Number" = "1087", acHidden

What happens now is it loops from 1064, 1065 etc (I stopped it at this point), and it saves PDF invoice files (1064 & 1065) to the correct location. However, when I open the PDF files, they are blank (when I say blank, the labels are the, as is the logo, page footer etc, just not the invoice data).

Is there anything obvious I'm doing wrong?

Thanks again.

 

[Gasman]

No, that will not work
Look at what I typed in the immediate window to concatenate the criteria value to the criteria.?
Plus those are not text, but numeric, so why have them as text?
Plus if you have spaces in a field you need to surround that with [], so I am surprised anything came out TBH

Try

DoCmd.OpenReport "R_Invoices_PDF", acViewPreview, , "[Invoice_Number] = '" & "1087" &"'", acHidden

So get the criteria correct, then we will worry about the data, but that is the likely cause of no data on the reportI would have thought?

Edit:
You had the field correct in the original code?

DoCmd.OpenReport "R_Invoices_PDF", acViewPreview, , "[Invoice_Number]=" & ![Invoice_Number], acHidden

so try

DoCmd.OpenReport "R_Invoices_PDF", acViewPreview, , "[Invoice_Number]='" & .[Invoice_Number] & "'", acHidden

 

[neilsolaris]

Thanks Gasman.

I can't remember exactly why I originally decided to put the invoice numbers in as text, but it was probably in case I wanted to insert, say, 1087.5 for example, but I didn't want the other invoices showing as 1087.0, if that makes sense. But probably I won't have any reason to insert a non-integer invoice, so I could probably change that field to numbers now. Shall I change the field type now? I just don't want to complicate things while you've already suggested code for a text field.

I tried the codes you suggested.

DoCmd.OpenReport "R_Invoices_PDF", acViewPreview, , "[Invoice_Number] = '" & "1087" &"'", acHidden

That one works, except that it saves all of the invoices in a single PDF file. I pressed F8, and stopped before the loop.

DoCmd.OpenReport "R_Invoices_PDF", acViewPreview, , "[Invoice_Number]='" & .[Invoice_Number] & "'", acHidden

This one brings up a compile error "Method or data member not found". Should there be a full stop before the 2nd "Invoice_Number" perhaps?

I'm not sure how to test it with the intermediate window, but I'll see if I can work it out.

Thanks again.

 

[Gasman]

You can put a breakpoint on a line and the code will stop there.
Then you can copy a line of code and paste in the immediate window, providing you have the data to do so?

Change that . to a ! (as you did have it) and try again. I have rarely had to use ! and have used . (which I though would have worked?).
You need some as you said With rst. You could alsu specify rst.[Invoice_Number].

I am also going blind as I missed the underscore for Invoice_Number, so no [] actually required, sorry.

Please post your latest version of the code as well.

 

[neilsolaris]

Hi Gasman,

Sorry for my slow reply, I just got back home now. What I've done is attached a practice copy version of my database (just containing a small sample of the invoice and supplier table data). Is that more helpful?

By the way, if you wanted to play around with it, feel free to change the data type of the invoice number to a number. I think that would be better, as you said.

Thanks again for your help.

 

[Gasman]

Well one thing I was thinking of is, that if you leave it as alpha, 1 and 11 through 19 will sort before 2 and the same with the other numbers?
So if you need them sorted in numerical order, best to have a number.?

 

[neilsolaris]

Well one thing I was thinking of is, that if you leave it as alpha, 1 and 11 through 19 will sort before 2 and the same with the other numbers?
So if you need them sorted in numerical order, best to have a number.?

 

[neilsolaris]

That's interesting, but I'm sure mine are in numerical order, even though it's set as text. But I'll double check though when I'm back on my computer.

Anyway, I might as well set it to number format though.

 

[Gasman]

Your code works for me after I changed the output folder?

 

[neilsolaris]

That's weird, it's working fine for me now as well! I don't know why it wasn't working before.

By the way, if I go ahead and change the data type to number, can I copy one of your above lines?

Thanks for your help.

 

[Gasman]

See this. I added to invoices manually into table

I then created a query

SELECT T_Invoices.Invoice_Number
FROM T_Invoices
ORDER BY T_Invoices.Invoice_Number;

Result

Just remove the single quotes from rs!Invoice_Number

DoCmd.OpenReport "R_Invoices_PDF", acViewPreview, , "[Invoice_Number]=" & ![Invoice_Number], acHidden

 

[neilsolaris]

See this. I added to invoices manually into table View attachment 91813 I then created a query

 SELECT T_Invoices.Invoice_Number FROM T_Invoices ORDER BY T_Invoices.Invoice_Number;

Result View attachment 91814 Just remove the single quotes from rs!Invoice_Number

 DoCmd.OpenReport "R_Invoices_PDF", acViewPreview, , "[Invoice_Number]=" & ![Invoice_Number], acHidden

See this. I added to invoices manually into table View attachment 91813 I then created a query

 SELECT T_Invoices.Invoice_Number FROM T_Invoices ORDER BY T_Invoices.Invoice_Number;

Result View attachment 91814 Just remove the single quotes from rs!Invoice_Number

 DoCmd.OpenReport "R_Invoices_PDF", acViewPreview, , "[Invoice_Number]=" & ![Invoice_Number], acHidden

I see, so definitely a good idea for me to change it to numbers. Thanks for explaining how to change the code.

 

[Gasman]

While you are at it, test for EOF before the MoveFirst, in case you ever get an empty recordset.