Looped output for report with subreport not working (1 Viewer)

S

Sebbyy

Registered User.
Local time
Today, 14:16
Joined
Jul 26, 2017
Messages
19
So I have a report that i want to auto output for all FIDs with the date attached in a PDF and I can get the main report to cycle through the FIDs. However I have subreport linked through FIDs to the main report which does not get the linked FID so my subreport ends up being 200 pages of data.

My code for the output is:
Code: 
Private Sub Command115_Click()

    Dim MyRs As DAO.Recordset
    Dim strPathAndFile As String, strDate As String
    Dim strFamilyName As String

    strDate = Format(Date, "ddmmyyyy")
    
    Set MyRs = CurrentDb.OpenRecordset("FIDq")


      
    With MyRs
        .MoveFirst
            Do While Not .EOF

                strFamilyName = !FamilyName
                strPathAndFile = "L:\Lab_NickH\Seb\Database Work\Family Reports\" & strFamilyName & " " & strDate & ".pdf"
                DoCmd.OutputTo acOutputReport, "Family Report Output", acFormatPDF, strPathAndFile
    
               .MoveNext
            Loop
    End With

    Set MyRs = Nothing


End Sub
 
Ranman256

Ranman256

Well-known member
Local time
Today, 00:16
Joined
Apr 9, 2015
Messages
4,337
you must loop the main report to the iD.
Better to use a form, the form has a list box to loop thru.
the query of the report looks at the list box to get the ID.
the report opens ONLY for that ID (as does the subreport)

Code: 
sub btnRun_click()

vDir = "c:\folder\"

For i = 0 To lstUser.ListCount - 1
   vUsr = lstUser.ItemData(i)   'get next user in list
   lstUser = vUsr        'set the list

    vFileName = vUsr & "_" & lstRpt & format(date,"yymmdd-hhnn") & ".pdf"
    docmd.OutputTo  acOutputReport ,lstRpt ,acFormatPDF, vFilename

 Next
end sub
 
S

Sebbyy

Registered User.
Local time
Today, 14:16
Joined
Jul 26, 2017
Messages
19
I'm quite new to using VBA so I don't really understand what you mean?
 
S

Sebbyy

Registered User.
Local time
Today, 14:16
Joined
Jul 26, 2017
Messages
19
Ranman256 said:
you must loop the main report to the iD.
Better to use a form, the form has a list box to loop thru.
the query of the report looks at the list box to get the ID.
the report opens ONLY for that ID (as does the subreport)
Click to expand...

I have had a go at this but i'm fucking it up.

this is what i have.

Code: 
strFamilyName = List119!FamilyName
strDate = Format(Date, "ddmmyyyy")
strPathAndFile = "L:\Lab_NickH\Seb\Database Work\Family Reports\" & strFamilyName & " " & strDate & ".pdf"
                
For i = 0 To List119.ListCount - 1
strUsr = List119.ItemData(i)
List119 = strUsr
DoCmd.OutputTo acOutputReport, "Family Report Output", acFormatPDF, strPathAndFile
Next
 
Cronk

Cronk

Registered User.
Local time
Today, 14:16
Joined
Jul 4, 2013
Messages
2,772
So I have a report that i want to auto output for all FID

I'll presume that it's a unique record identifier and you want a separate report output for each record.

With a normal report you can set a filter, or where condition.
eg docmd.openreport "Family Report Output",acNormal, "","FID=" & [somevalue]

So you need to have a loop going through your recordset
Code: 
Do While Not .EOF
      ....
      docmd.openreport "Family Report Output",acNormal, "","FID=" & .FID
      .MoveNext
Loop

You want to generate PDFs using docmd.output to which does not provide for a filter. You can however, set the recordsource of the report to be a query with a condition in it that the FID is equal to the contents of a text box on your form and in the recordset loop, put the FID value in the text box before generating the report

Code: 
   me.txtFID= .FID
   DoCmd.OutputTo acOutputReport, "Family Report Output", acFormatPDF, strPathAndFile
 
S

Sebbyy

Registered User.
Local time
Today, 14:16
Joined
Jul 26, 2017
Messages
19
Sorry, I've been busy and now had a go at this.

I understand how it would work but don't get how to make the code to set the textbox to set the FID, output then cycle onto the next in code?

Code: 
Private Sub Command115_Click()
    Dim MyRs As DAO.Recordset
    Dim strPathAndFile As String, strDate As String
    Dim strFamilyName As String

Set MyRs = CurrentDb.OpenRecordset("FIDq")

strDate = Format(Date, "ddmmyyyy")


Me.txtFID = qFID
With MyRs
        .MoveFirst
            Do While Not .EOF
            strFamilyName = !FamilyName
            strPathAndFile = "L:\Lab_NickH\Seb\Database Work\Family Reports\" & strFamilyName & " " & strDate & ".pdf"
            DoCmd.OutputTo acOutputReport, "FamR output", acFormatPDF, strPathAndFile
        .MoveNext
Loop
End With
Set MyRs = Nothing
End Sub
 
Cronk

Cronk

Registered User.
Local time
Today, 14:16
Joined
Jul 4, 2013
Messages
2,772
Code: 
Private Sub Command115_Click()
    Dim MyRs As DAO.Recordset
    Dim strPathAndFile As String, strDate As String
    Dim strFamilyName As String

Set MyRs = CurrentDb.OpenRecordset("FIDq")

strDate = Format(Date, "ddmmyyyy")

Me.txtFID = qFID   'Delete - you can't set a text box to equal a query
With MyRs
        .MoveFirst
            Do While Not .EOF
            strFamilyName = !FamilyName
            strPathAndFile = "L:\Lab_NickH\Seb\Database Work\Family Reports\" & strFamilyName & " " & strDate & ".pdf"
             me.txtFID= .FID  
            DoCmd.OutputTo acOutputReport, "FamR output", acFormatPDF, strPathAndFile
        .MoveNext
Loop
End With
Set MyRs = Nothing
End Sub

But I'll repeat, the report has to be based on a query using the contents of me.txtFID
 
S

Sebbyy

Registered User.
Local time
Today, 14:16
Joined
Jul 26, 2017
Messages
19
Cronk said:
Code: 
Private Sub Command115_Click()
    Dim MyRs As DAO.Recordset
    Dim strPathAndFile As String, strDate As String
    Dim strFamilyName As String

Set MyRs = CurrentDb.OpenRecordset("FIDq")

strDate = Format(Date, "ddmmyyyy")

Me.txtFID = qFID   'Delete - you can't set a text box to equal a query
With MyRs
        .MoveFirst
            Do While Not .EOF
            strFamilyName = !FamilyName
            strPathAndFile = "L:\Lab_NickH\Seb\Database Work\Family Reports\" & strFamilyName & " " & strDate & ".pdf"
             me.txtFID= .FID  
            DoCmd.OutputTo acOutputReport, "FamR output", acFormatPDF, strPathAndFile
        .MoveNext
Loop
End With
Set MyRs = Nothing
End Sub

But I'll repeat, the report has to be based on a query using the contents of me.txtFID
Click to expand...
I have my report set to the me.txtFID but my debugger is pulling up an error on .FID. Should it be !FID?
 
Cronk

Cronk

Registered User.
Local time
Today, 14:16
Joined
Jul 4, 2013
Messages
2,772
Aplologies. Yes, indeed it should be !FID.
 
You must log in or register to reply here.

Similar threads

A
Report output to filepath variable
Replies
4
Views
415
Antony Cole
A
M
VBA output report to PDF and email
Replies
18
Views
759
Pat Hartman
P
O
Printing/Saving Multiple Reports to PDF
Replies
5
Views
585
sonic8
S
MajP
Load Local PDF into Webbrowser
Replies
7
Views
306
Gasman
Gasman
A
Solved Loop recordset and print reports
Replies
5
Views
439
Pat Hartman
P

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom