Separate Access Report for Each record in a Query - Loop I Think via VBA

[healey21]

I have a report which currently shows all records from a query, what I would like to achieve is the ability to Output the report as a snapshot for each record on its own snapshot into a folder for our accounts department. I am searching but can't find a solution. The details are:

Report Name: "rptInvoiceToProcess"
Query Name: "qryInvoiceToProcess"
Folder Location: "L:\Accounts To Process\"
Field ID Name is: "InvoiceID"
Table Name if it helps: "tblInvoices"

Any help or direction would be very much appreciated.

 

[Trevor G]

I have simulated your information, and created a form with a combo box and a command button the combo is named cboNames and behind the command button is the code for On Click Event. You would need to select the first name in the combo then click the button. The code used is as follows:

Private Sub cmdExportPDF_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim MyFileName As String
Dim mypath As String
Dim temp As String
mypath = "L:\Accounts to Process\" 'Adjust the path to your location
Set db = CurrentDb()
Set rs = db.OpenRecordset("SELECT [InvoiceID] FROM [tblInvoices]", dbOpenDynaset) 'Change the table name as required

Do While Not rs.EOF
temp = rs("InvoiceID") 'Change field name as required

MyFileName = rs("InvoiceID") & ".PDF" 'You can explore assigning the other field names

' set form value to the Customer Name, so report will be filtered correctly

Me.cboName.Value = rs("InvoiceID") 'cboName is the combo box name on the form

' set the Me.cboName as filter for your report
DoCmd.OpenReport "rptInvoiceToProcess", acViewReport, Me.cboName
DoCmd.OutputTo acOutputReport, "", acFormatPDF, mypath & MyFileName
DoCmd.Close acReport, "Invoices to PDF"
rs.MoveNext
Loop
Set rs = Nothing
Set db = Nothing

End Sub

 

[healey21]

Thank you Trevor this has provided a working solution.

 

[firshad]

Trevor/Heaely,

i am using the same code, but when i use with a query it gives an error of 3061 expected 2 but when i use with the table it works OK.

and the error is in the this line

Set rs = db.OpenRecordset("SELECT [InvoiceID] FROM [tblInvoices]", dbOpenDynaset) 'Change the table name as required

Do i have to use different coding for a query?

Any thoughts.

Regards