Error code solution (1 Viewer)

[PJAY]

I have a database that consist of student student name in a particular school. At the end of every term the school send a news letter to every parent.
but my database generate all news letters as report contain each child name in one single pdf file.
now i am trying to generate the same pdf report file but this time i want it to be individual file for each child.
example if i a have 10 students, when i click a button the database should generate 10 pdf reports each file containing the information of one student and the file save withe the value of a field in the query.
this is the code:


Dim rpt As Report
Dim strFolderPath As String
Dim objFSO As Object
Dim objFolder As Object
Dim i As Integer


Set rpt = Reports("Nest-Term1-Bill") 'Replace "Report1" with the name of your report

strFolderPath = Environ("USERPROFILE") & "\Desktop\Note PDF\"

'Check if the folder exists, create it if not
Set objFSO = CreateObject("Scripting.FileSystemObject")
If Not objFSO.FolderExists(strFolderPath) Then
Set objFolder = objFSO.CreateFolder(strFolderPath)
End If

For i = 0 To rpt.Pages - 1

rpt.PrintOut acSelection, , , , i 'print the current page


Dim fileName As String

fileName = rpt.Controls("StudentFullName").Value 'Replace "NameField" with the name of the field in the page header that you want to use as the file name

DoCmd.OutputTo acOutputReport, "Nest-Term1-Bill", acFormatPDF, fileName & ".pdf"

Next i


MsgBox "PDF files have been created and saved in the Note PDF folder on the desktop.", vbInformation


and i am getting this error

please can anyone help me? i am a beginner in access programming.

 

[arnelgp]

there is somewhat similar discussion below:
Help with creating a button to export PDF file | Page 2 | Access World Forums (access-programmers.co.uk)

 

[Gasman]

Need to say on what line the error occurs?
I would use a recordset and issue the report per record.

 

[Gasman]

Crossposted at https://www.utteraccess.com/topics/2065578?post=unread

and getting responses there as now supplying more information

 

[Pat Hartman]

There are two ways to output a report to a pdf for a specific record.
1. Open the report using OpenReport and use the Where argument to provide the ID of the record you want to filter for. Then use OutputTo, and finally close the report before using MoveNext to get the next record to report on.
2. Use a different method to filter the report such as using a WHERE clause in the report's RecordSource query that references a hidden form control. In this case, each pass through the loop populates the hidden form control and then uses OutputTo to create the PDF. Using this method there is NO need to open, then close the report.

Does it matter? Yes, indeed it matters very much if you are creating more than a couple of reports for each execution. Method #1 is very prominent in old Access examples and most of the time, you get away with it because you are not creating hundreds of reports at one time. However, it is very costly to open the report and then close it. And, if you don't expressly close the report, you could run into out of memory issues. You see very few examples of #2. If you have the code for #1, you can easily modify it for technique #2.

The method you are using to create a new PDF for each page of the report, is the worst possible option. It works only in the case where each "report" is fixed at one page, never longer. Never shorter. Notice, I did not even include it in my "two" options

 

[Gasman]

Pat,
Could you explain how that form control gets populated within a loop please?

 

[Pat Hartman]

air code

    Dim db As DAO.Database
    Dim qd As DAO.QueryDef
    Dim rs As DAO.Recordset
    Dim Path As String
    Dim FileName As String

    Set db = CurrentDB()
    Set qd = db.Querydefs!qMonthlyCommissionsReport
    Set rs = qd.OpenRecordset
    Path = "C:\myPath\"

    Do While Not rs.EOF
        Me.txtHiddenPersonID = rs!PersonID    ''<<<<<<<<<  populate hidden, unbound, form control
        FileName = "Commission_" & rs!LastName & "_" & rs!PersonID & "_" & Format(Date(), "yyyymmdd") & ".Pdf"
        DoCmd.OutputTo acOutputReport, "rptCommission", acFormPDF, Path & FileName, False
        rs.MoveNext
    Loop

This would of course be easier if MS had included the WHERE option for the OutputTo method. Coding the report this way has one limitation and that is that the report becomes forever bound to a particular form. To overcome this problem, I use a hidden form that I open whenever the app opens and this is the form I use when I need to use this technique. Rather than having to figure this out on the fly or change to it later, I simply ALWAYS use the hidden form whether I think I might ever have to print the report from multiple forms or not. So, even in the case where I could reference the open form for this purpose, I just copy the relevant ID to the hidden form and the RecordSource of my reports just always references the hidden form.

When the report has the all or each option, the Criteria is:

Where PersonID = Forms!frmHiddenForm!PersonID or Forms!frmHiddenForm!PersonID Is Null

 

[Gasman]

Thank you Pat
I suppose a Tempvar could be used instead, so the report only ever depends on that?

 

[Pat Hartman]

I suppose a Tempvar could be used instead, so the report only ever depends on that?

That is an option but I've been using this technique since long before tempvars came into Access.

I don't like tempvars because they can be spread all over the application and they are hard to corral. Using the hidden form gives me a nice advantage when I am testing. I can make the hidden form visible so I can see that the parameter is being correctly set. I can also update it directly to test different code paths.

 

[Gasman]

I was curious as to how you would imlplement your suggestion and can see I was on the right track.
The hidden report form is a neat idea.

I was always reluctant to tie a report to a form, in case the report could be used elsewhere.
For that reason I started using a few TempVars.
Then I found you can amend the qdf if a query, or just change the source yourself.

 

[Pat Hartman]

PS, when I do use TempVars, I always define them in a standard module created just to define global variables.

 

[Pat Hartman]

I was always reluctant to tie a report to a form, in case the report could be used elsewhere.

Typically, it isn't a problem. Usually you just have the two options - one or all. Once you solve a problem and that solution is "universal", it should become second nature to just use the "solution" always rather than just sometimes. You are better positioned should requirements change. I've also found that most applications have a limited number of "IDs" that are used this way and they are usually used one at a time so the form can have just a single unbound control to hold the ID for this instance of running some report. With TempVars, I would create a unique TempVar for each unique ID

 

[Gasman]

PS, when I do use TempVars, I always define them in a standard module created just to define global variables.

Never thought of that.

 

[tvanstiphout]

Never thought of that.

See also: https://nolongerset.com/a-safer-way-to-use-tempvars/

 

[Gasman]

Thank you Tom,
I will review that link.
Happy New Year to you.