export report with subreports to individual PDF's

[Squid1622]

Could there be reports with no records?

I don't think that's it. I specified a location that I know has records attached to it and it didn't work. So I saved the code in a word document, deleted the code from access, closed and saved, reopened and pasted the code and it started working again. It's all very odd. I'd hate to have to do this constantly, but honestly it's a minor inconvenience. I was just curious if I was doing something wrong.

 

[pbaldy]

That is very odd, shouldn't have to be done. If it happens again, I'd try decompiling the db (on a copy):

http://www.fmsinc.com/MicrosoftAccess/Errors/Bad_DLL_Calling_Convention.asp

 

[Squid1622]

Pbaldy,

Thank you very much for your help previously on this. The code has been working fine since the last time I posted in this thread. Recently I tried to amend this code to save two reports at the same time. I must not have coded something correct, because I get stuck in an infinite loop. Can you please help? Below is my new code.

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 "Trash Spend by DOM", acPreview
DoCmd.OutputTo acOutputReport, "Trash Spend by DOM", acFormatPDF, sPath & rst.Fields("DOM") & " " & "Summary Graphs" & ".pdf"
DoCmd.Close
DoCmd.OpenReport "subrptTrashSpendbyDOM", acPreview
DoCmd.OutputTo acOutputReport, "subrptTrashSpendDOM", acFormatPDF, sPath & rst.Fields("DOM") & " " & "Terminal Information" & ".pdf"
DoCmd.Close
DoEvents
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
Set qdf = Nothing
Set db = Nothing
End Sub

 

[pbaldy]

Hmm, normally that would be due to missing the MoveNext, but you've got that in there. You realize you aren't using strRptFilter, so every output will have the full report in it (unless the report uses it, in which case you don't need to open in preview first).

 

[Squid1622]

The whole thing is designed to allow me to run a report based on a query that takes an input from a combo box, then chop that up and save it as a pdf. If I don't put an input in the combo box that the full report generates. If there is a way to do this, where nothing previews and the report just saves, I would be very interested in how to do that. I'm not proud to say that I copied this code from elsewhere, so I'm not even sure what the strRptFilter does (or is supposed to do) inside the code.

 

[pbaldy]

strRptFilter doesn't do anything in the code you posted. If the reports that are output are filtered, there must be code in the report's open event.

Are you still getting the endless loop? Can you attach the db here?

 

[Squid1622]

Unfortunately the DB is too big to post and contains data that my employer considers secret.

Basically the code above runs from a button. It pulls 2 reports at the same time. The 1st report is Summary graphs for each District Manager. The 2nd report is the granular data for each property.

Each report only needs to run once to be printed and saved. I'm wondering if report number 2 is causing the vba to run both reports each time it encounters a new property. Because each District Manager has many properties, it would appear like an infinite loop because I never let it finish running.

 

[pbaldy]

That code is designed to output multiples, like a separate individualized report for each customer or something. Try replacing the loop with:

If Not rst.EOF Then

DoCmd.OutputTo acOutputReport, "Trash Spend by DOM", acFormatPDF, sPath & rst.Fields("DOM") & " " & "Summary Graphs" & ".pdf"

DoCmd.OutputTo acOutputReport, "subrptTrashSpendDOM", acFormatPDF, sPath & rst.Fields("DOM") & " " & "Terminal Information" & ".pdf"

End If

 

[Squid1622]

I can't seem to make it work. Here's what I did and I'm getting an error message.

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 Not rst.EOF Then
    DoCmd.OutputTo acOutputReport, "Trash Spend by DOM", acFormatPDF, sPath & rst.Fields("DOM") & " " & "Summary Graphs" & ".pdf"
    DoCmd.OutputTo acOutputReport, "subrptTrashSpendDOM", acFormatPDF, sPath & rst.Fields("DOM") & " " & "Terminal Information" & ".pdf"
End If

rst.Close
Set rst = Nothing
Set qdf = Nothing
Set db = Nothing
End Sub

 

[pbaldy]

I said replace the loop; you took out the recordset opening code. Just replace from "Do..." to "Loop" with what I posted.

 

[Squid1622]

That worked perfectly. Thank you!

 

[pbaldy]

Happy to help!