Multiple prints form Single Report for Individual Accounts

[firshad]

Hi,

I have a report , which i print every month and it consist of of more than 500 pages. This report is based on a Query called L_Inv2. i want to filter and loop this report based on the filed AccountReference with in the query. And save as PDF for individual accounts.

i have also created another query based on the L_Inv2, Called L_Inv4 which only got the record of account numbers as a AccountReference

i am trying to use below code but some how this is not working.

Private Sub Command43_Click()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strSQL As String
    
    Set db = CurrentDb
    
    Dim filterDefintion As String
    Dim filename As String

    strSQL = "Select AccountReference FROM L_Inv4"

    Set rs = db.OpenRecordset(strSQL)

    rs.MoveFirst

    Do While rs.EOF = False
        filterDefinition = "[AccountReference] = " & rs![AccountReference]
        filename = rs![CompanyName] & rs![AccountReference]
        filename = Replace(filename, "/", "-")
        DoCmd.OpenReport "L_Inv2", acViewReport, "qryReportInfo", filterDefinition, acNormal
        DoCmd.OutputTo acOutputReport, "L_Inv2", "PDFFormat(*.pdf)", "C\PROJECTS\" & filename & ".pdf", False, "", , acExportQualityPrint
        DoCmd.Close acReport, "L_Inv2"
        rs.MoveNext
    Loop
End Sub

Can some one please help me.

Please note i am using Access 2013.

Regards

Farhan

 

[Uncle Gizmo]

Hi Farhan, welcome to AWF.... Personally I would not use the filter myself. However I can show you how I would do it, and hopefully someone who has some experience of filtering Reports in this manner will provide an insight, and I will learn something! If no one can provide an answer, then at least you will have a back-up plan... There's some YouTube videos and some documentation on this website here:- Generate PDF Invoices in MS Access

 

[JHB]

..
i am trying to use below code but some how this is not working.
..

And what problems lies behind above the words, (is it a guessing competition)?

 

[firshad]

Now i have made some changes to the code.

Some how it does work with the table but when i change the setrs to a query it start giving me error of 3061 with expected 2.


Private Sub Command43_Click()

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim MyFileName As String
Dim mypath As String
Dim temp As String
mypath = "C:\Account Process\" 'Adjust the path to your location
Set db = CurrentDb()
Set rs = db.OpenRecordset("SELECT [AccountReference] FROM qryL_Inv2", dbOpenDynaset) 'Change the table name as required

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

MyFileName = rs("AccountReference") & ".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("AccountReference") 'cboName is the combo box name on the form

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

End Sub


Can some one please have a look

 

[JHB]

And in which line do you get the error?
Put the code in Code tags, click the #.

 

[firshad]

JHB,

Thanks for your quick reply, error is in the Set RS,

#Set rs = db.OpenRecordset("SELECT [AccountReference] FROM qryL_Inv2", dbOpenDynaset#

Thanks

 

[JHB]

I think you've some criteria in qryL_Inv2, how are they formed, (show the whole SQL-string)?
You should click the # sign, not type it in, (to get code tags).