Reports to multiple pdf

[mavesan]

Hi There,

i am quite new to Access and I am finalaising a db that will produce invoices.
I have created a report that contains all the customers that i want to invoice for the month but i need to export the records of this report as independent pdf files.
I know that i have to built a loop to do that but i have no much knowledge in VBA.
I would love if someone could give me the code for this so i could copy and paste on my comand.
This is the details
-"Report invoices" (name of report)
record to be filter by [Customer Name]

Once i output the record i would like that the PDF doc name is made of
[Invoice number]
[Date of Invoice]
[Customer name]
all in the report


Could someone help me please!!!

Thanks in advance

 

[Trevor G]

Welcome to the forum,

Take a look at the code here, in the first thread that should work for you with a little adapting

http://www.utteraccess.com/forum/Export-Report-Pdf-t1962820.html

 

[mavesan]

Hi Trevor,

Thank you for your reply, I have tried the code and done the modifications, it works but it outputs the whole report in one pdf file.

I need to output the 180 records in the report in 180 pdf files,

not sure how to do this.

Thansk

 

[Trevor G]

Try this link then

http://bytes.com/topic/access/answers/843707-export-individual-record-form

 

[mavesan]

Hi Trevor

I tried the link and I have modified the VBA to my data base but still wont work.
I am not sure if I have done it properly as i am not use to work with VBA,
This is what I got
Private Sub Toggle9_Click()
Dim MyDB As DAO.Database
Dim MyRS As DAO.Recordset
Dim strSQL As String
Dim strRptName As String
strRptName = " Invoices "
'Used to create a Recordset of invoiceIDs for Form Filtering for each
'Invoice. [ID] is the Primary Key making the job easy
strSQL = "Select [Invoices].[ID] From Invoices"
Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecordset(strSQL, dbOpenForwardOnly)
With MyRS
Do While Not MyRS.EOF
'Open the Report Filering by the WHERE Clause for each specific [ID] Value
DoCmd.OpenReport strRptName, acViewPreview, , "[ID] = " & ![ID]
'Output Reports for each PK ([ID])
DoCmd.OutputTo acOutputReport, strRptName, acFormatSNP, " C:\Users\XXXX\Documents\Invoices \" & _
![MPRN] & "\" & _
![Date invoice] & "\" & _
![Account Number] & ".pdf"
'Close each Report after Outputting
DoCmd.Close acReport, strRptName, acSaveNo
.MoveNext 'Move to the next Record in Recordset
Loop
End With
MyRS.Close
Set MyRS = Nothing

End Sub

Any help to correct this will be well apreciated.

Thanks

 

[Trevor G]

Can you upload a sample so I can see where it is failing, because you have less than 10 messages you would need to use a third part like drop box and send me the link.

Copy the database and strip out all data from the table and make up a couple of rows we should have this working then.

 

[boblarson]

First off do you really have a primary key named ID?

Second, why do you have the format set to acFormatSNP instead of acFormatPDF?

 

[boblarson]

Oh and you have users/XXXX. What's with that?

 

[Trevor G]

The author is probably just hiding part of the path for the forum Bob.

 

[boblarson]

The author is probably just hiding part of the path for the forum Bob.

That may be true but you can never assume. And anyway, that isn't good coding as it would only work for one person. If it is to be placed in the user's documents folder, you need to concatenate in the correct user name.

 

[mavesan]

Hi Trebor

Thank you very much for all your help

I have made a mini copy of the db with the bits that I need to pdf, the report is called "invoices to PDF"
The main ID is the invoice number called [ID]
Also when i output the file in pdf i would love to call that file as
[Invoice Date]
[Invoice number]
[Customer Number]

I have attached now a copy of the db for text.
Really apreciate your help on this.

Blobarson, XXX is to hide the information, also as i have previous mentioned I have no much idea of VBA.

Thanks

 

[Trevor G]

Find attached a working version but with some minor modifications. I have added a combo box to the form and command button. Behind the command button in the Properties in the On Click Event is the code that will look at the combo box (so select the first name) then it will look at creating a pdf document for each name in the combo so it goes through the table. Your table is shown as "TBL_Monthly Invoices To Process" and the Field I have used is "Customer name" The report name I have used is "Invoices to PDF" obviously this is based on your example.

Some pointers there is a naming convension you should use when designing a database:
Table names begin with tbl and no spaces in the names
Field names shouldn't have spaces
Query names begin with qry and no spaces in the names
Form names begin with frm and no spaces in the names
Report names begin with rpt and no spaces in the names

The main reason for this in VBA it is easier to identify object names and field names otherwise you have to use square brackets to allow the VBA to account for spaces and this can become confusing when building your code.

In the code you will find some comments to help you.

 

[mavesan]

Hi Trevor

Thanks very much for your work, that works good except that it pdf the full report, it doesnt break it into each invoice for each customer.
Any chance you could have a look on this?
Thanks and Regards

 

[Trevor G]

From your example it produces the report as you would see it. If you want an individual invoice in pdf then I would advise you look at your design of the report.

When I viewed your report it shows for One Customer and you have placed in a sub report for all of the invoices if it is supposed to be 1 report per 1 invoice then you need to review the design and methodilgy.