Looped output for report with subreport not working

[Sebbyy]

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:

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]

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)

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

 

[Sebbyy]

I'm quite new to using VBA so I don't really understand what you mean?

 

[Sebbyy]

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)

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

this is what i have.

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]

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

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

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

 

[Sebbyy]

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?

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]

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

 

[Sebbyy]

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

I have my report set to the me.txtFID but my debugger is pulling up an error on .FID. Should it be !FID?

 

[Sebbyy]

I have my report set to the me.txtFID but my debugger is pulling up an error on .FID. Should it be !FID?

I think I might have got it to work with !FID

 

[Cronk]

Aplologies. Yes, indeed it should be !FID.