export report with subreports to individual PDF's

S

Squid1622

Registered User.
Local time
Today, 12:01
Joined
May 14, 2012
Messages
49
Hi All. I need some help with my report and their subsequent subreports. Currently I am generating a report with 3 separate sub reports and all the reports are based on a series of parameters specified on a form (ie. Start Date, End Date, ID, etc...). Each sub report is based off it's own query and is linked to the main report by a specific ID. What I'm trying to do is run the report and have it automatically split itself for each record and then save each record as it's own PDF.

Here's the code as I have it, and it does not work. Every move leads me to a new error.:banghead:

Code: 
Private Sub cmdReport_Click()
    Dim ReportName As String
    ReportName = Me.cmboReport
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim qdfSnow As QueryDef
    Dim qdfUtilities As QueryDef
    Dim qdfPestControl As QueryDef
    Dim qdfProperty As QueryDef
        
If ReportName = "Spend by Property" Then
Set db = CurrentDb
Set qdfSnow = db.QueryDefs("qrySnowSpend") 'My parameter query for a subreport
Set qdfUtilities = db.QueryDefs("qryUtilitySpend") 'My parameter query for a subreport
Set qdfPestControl = db.QueryDefs("qryPestControlSpend") 'My parameter query for a subreport
Set qdfProperty = db.QueryDefs("qryActivePropertiesReport") 'My parameter query for the main report
qdfSnow.Parameters("DatePaid") = [Forms]![frmReportMenu]![rptStartDate] & [Forms]![frmReportMenu]![rptEndDate] 'Form control
qdfUtilities.Parameters("DatePaid") = [Forms]![frmReportMenu]![rptStartDate] & [Forms]![frmReportMenu]![rptEndDate] 'Form control
qdfPestControl.Parameters("DatePaid") = [Forms]![frmReportMenu]![rptStartDate] & [Forms]![frmReportMenu]![rptEndDate] 'Form control
Set rst = qdfProperty.OpenRecordset 'set qdfProperty as Recordset because this is the main report. All other qdf's are subreports.
Do While Not rst.EOF
    strRptFilter = "[PropID] = " & rst.Fields("propName")
 
    DoCmd.OutputTo acOutputReport, "Spend by Property", acFormatPDF, "z:\Utilities\Real Estate Database Project\Test" & "\" & rst.Fields("propName") & Format(Date, "mmddyyyy") & ".pdf"
    DoEvents
    rst.MoveNext
Loop
rst.Close
    
     DoCmd.OpenReport ReportName, acPreview
    'DoCmd.OpenReport stDocName, acNormal, , "[propID] = " & Me.txtPropID
Else
    DoCmd.OpenReport ReportName, acPreview
End If
   
End Sub

Any and all help is greatly appreciated!
 
Simplest for you is probably to open the report in preview mode before the OutputTo line using the filter variable, then close it after the OutputTo.
 
Will this give me individual pdf's? The problem I think I'm facing is not the export of the report as a whole (I was able to do that), but in getting the report to divide itself by location and save each locations information as separate pdf's.
 
So I simplified the code, but I'm still getting errors. This one says there's a missing operator and it's pointing to the line with my filter variables.

Private Sub cmdReport_Click()
Dim ReportName As String
ReportName = Me.cmboReport

If ReportName = "Spend by Property" Then
DoCmd.OpenReport "Spend by Property", acViewPreview, , "rptStartDate = #" & Me.rptStartDate & "#" & "rptEndDate = #" & Me.rptEndDate & "#"
DoCmd.OutputTo acOutputReport, "Spend by Property", acFormatPDF, "z:\Utilities\Real Estate Database Project\Test\" & "propName" & Format(Date, "mmddyyyy") & ".pdf"
DoCmd.Close

'DoCmd.OpenReport stDocName, acNormal, , "[propID] = " & Me.txtPropID
Else
DoCmd.OpenReport ReportName, acPreview
End If

End Sub
Click to expand...
 
Ok, I fixed the syntax error and got the code above to work. It opened, exported, and then closed the file. However, It exported one big PDF file. I need it to open the big pdf file, read it, and separate the big pdf file automatically into smaller pdfs by location.
 
It should export the filtered report. Does that wherecondition filter down to a single location? Looks like your original filter was closer to the mark:

strRptFilter = "[PropID] = " & rst.Fields("propName")

perhaps that needs to be incorporated?
 
No problem, post back if you get stuck.
 
Ok, so I've applied the changes you recommended and it does work... kind of. It will open and it will save individual pdf files (yay!) However, each file is the same record. Below is the new code and from what I see there are two potential problems.

1. The recordset is not moving to the next record and is just stuck on the initial record.
2. The control source on the form must specify which location I'm looking for.

I tend to believe is might be the control source on the form because the code is telling it to look there, not at the query to which the control source points. The query is set such that if there is a null value in the combo box, then it returns all locations. How do I force the code to recognize the null value and produce all the records.

Here is the code as it currently works.
Private Sub cmdReport_Click()
Dim ReportName As String
ReportName = Me.cmboReport

If ReportName = "Spend by Property" Then
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim qdf As QueryDef
Set db = CurrentDb
Set qdf = db.QueryDefs("qryActivePropertiesReport") 'My parameter query
qdf.Parameters(0) = [Forms]![frmReportMenu]![cmboPropID] 'Form control
Set rst = qdf.OpenRecordset
Do While Not rst.EOF
strRptFilter = "[PropID] = " & rst.Fields("PropID")

DoCmd.OpenReport "Spend by Property", acPreview
DoCmd.OutputTo acOutputReport, "Spend by Property", acFormatPDF, "z:\Utilities\Real Estate Database Project\Test" & "\" & rst.Fields("propName") & Format(Date, "mmddyyyy") & ".pdf"
DoEvents
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
Set qdf = Nothing
Set db = Nothing
Else
DoCmd.OpenReport ReportName, acPreview
End If

End Sub
Click to expand...
 
The OpenReport needs to use the filter, like:

DoCmd.OpenReport "Spend by Property", acPreview, , strRptFilter

You also need to close the report after the OutputTo, or you'll get the same report every pass.
 
I'm confused, it looks like your query is filtering to a single ID? If so, the loop would only run once (presuming that's a single record).
 
I don't know what happened, but as soon as I put the DoCmd.Close in, everything runs perfect. I don't know about referencing to a single ID, but here's the code.

Private Sub cmdReport_Click()
Dim ReportName As String
ReportName = Me.cmboReport

If ReportName = "Spend by Property" Then
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim qdf As QueryDef
Set db = CurrentDb
Set qdf = db.QueryDefs("qryActivePropertiesReport") 'My parameter query
qdf.Parameters(0) = [Forms]![frmReportMenu]![cmboPropID] 'Form control
Set rst = qdf.OpenRecordset
Do While Not rst.EOF
strRptFilter = "[PropID] = " & rst.Fields("PropID")

DoCmd.OpenReport "Spend by Property", acPreview
DoCmd.OutputTo acOutputReport, "Spend by Property", acFormatPDF, "z:\Utilities\Real Estate Database Project\Test" & "\" & rst.Fields("propName") & Format(Date, "mmddyyyy") & ".pdf"
DoCmd.Close
DoEvents
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
Set qdf = Nothing
Set db = Nothing
Else
DoCmd.OpenReport ReportName, acPreview
End If

End Sub
Click to expand...
 
As I mentioned, without the close the report stays open and never updates to a new ID.
 
pbaldy said:
Happy to help!
Click to expand...
So I have a new question. I've gotten this code to work and I have several different iterations that save reports in their own locations. In addition, this code also emails the report to the email linked to the record. The problem I'm running into is that the email opens up in a draft window in outlook and won't move to the next record until I send that specific email. This is fine if I'm running a report for a single location, but if I run a batch for all locations it is/will be tedious to have to physically review and send each email from the outlook draft window. Is there a way to bypass the draft window in outlook and automatically send the email via the code below?

Private Sub cmdSpendDOMPrint_Click()
DoCmd.Close
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim qdf As QueryDef
Dim sBasePath As String
Dim sPath As String

sBasePath = "Z:\Utilities\Real Estate Database Project\Test\"
sPath = sBasePath & Format(Date, "mmddyyyy") & "\"
If Dir(sPath) = "" Then
MkDir sPath
Else
End If
Set db = CurrentDb
Set qdf = db.QueryDefs("qrySpendbyDOMReport") 'My parameter query
qdf.Parameters(0) = [Forms]![frmReportMenu]![cmboDomID] 'Form control
Set rst = qdf.OpenRecordset

Do While Not rst.EOF
strRptFilter = "[propDomID] = " & rst.Fields("propDomID")
DoCmd.OpenReport "Spend by DOM", acPreview
DoCmd.OutputTo acOutputReport, "Spend by DOM", acFormatPDF, sPath & rst.Fields("DistrictOpsManager") & ".pdf"
DoCmd.SendObject acSendReport, "Spend by DOM", acFormatPDF, rst.Fields("ManagerEmail"), , , rst.Fields("DistrictOpsManager") & " " & "Property Spend"
DoCmd.Close
DoEvents
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
Set qdf = Nothing
Set db = Nothing
End Sub
Click to expand...
 
The EditMessage argument of SendObject controls that. You haven't specified, and the default is leave it open for editing.
 
Last question for a while... I promise:p. At random intervals I get an error 2501 (OpenReport Action was cancelled) Why does this error occur every so often?
 
Could there be reports with no records?
 
You must log in or register to reply here.

Similar threads

J
Pop-up message when converting Access report to PDF
2
Replies
24
Views
672
Edgar_
E
F
Solved Mask with sub-mask filtered by 2 combined drop-down boxes and a checkbox, opening two different reports depending on the checkbox
Replies
6
Views
431
Faoineag
F
A
Combining multiple reports into a PDF export
Replies
2
Views
518
KitaYama
K
C
Solved Passing form in OpenArgs
Replies
14
Views
544
ClaraBarton
C
C
filtering subreport from subform
Replies
4
Views
769
ClaraBarton
C

Users who are viewing this thread

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