Output A Report To Multiple PDFs (1 Viewer)

[Adam.Furtado]

Hello,
I have several reports that creates price sheets for all of my customers. It is a report with multiple subreports within, and I am trying to create a process to seperate them and export them all into [Customer Name].pdf. I have tried to scrap some code together from various forums, and it has been unsuccessful.

For example, one of the reports "rptPriceSheetQuarterly", has a field "txtCustomerName", that feeds the queries for the other subreports. I would like to create a new PDF when that field changes.

I understand I need some sort of looping code that will cut the report up dump them all into one folder, but I am totally out of my element here.

Any advice would be very helpful. Thanks.

 

[pbaldy]

I started with this:

http://www.granite.ab.ca/access/email/reporttomultiplerecipients.htm

 

[Adam.Furtado]

I have spent 2 days playing with this code and others, and I have had no luck.

This is what I have so far:

Private Sub Command23_Click()



Dim MyDB As DAO.Database, RS As DAO.Recordset
Dim strBody As String, lngCount As Long, lngRSCount As Long
DoCmd.RunCommand acCmdSaveRecord
Set MyDB = DBEngine.Workspaces(0).Databases(0)


Set RS = MyDB.OpenRecordset _
("SELECT DISTINCT [Customer] FROM tblCustomerList")


lngRSCount = RS.RecordCount
If lngRSCount = 0 Then
MsgBox "No price sheets to print", vbInformation
Else
RS.MoveLast
RS.MoveFirst
Do Until RS.EOF

Forms![frmcreatereport]![cboCustomerSelect] = RS![Customer]
Beep

DoCmd.OutputTo acOutputReport, "rptPriceSheetStandard", acFormatPDF, "C:\Users\furtadoa\Desktop\PriceSheetPDF" & "\" & rst![Customer] & ".pdf"
' Want to get the rest right before I start messing with the output.
Debug.Print RS![Customer] 'this will put each store ID in the VBA immediate window, so you can see that it spun through them. Only needed for testing


RS.MoveNext
Loop

End If
RS.Close
MyDB.Close
Set RS = Nothing
Set MyDB = Nothing
Close


MsgBox "Done exporting price sheets. ", vbInformation, "Done"

Exit Sub


End Sub

I removed the error message temporarily so that I could use the debugger, and it gives me "Error '424': Object Required Using DAO" on this line

DoCmd.OutputTo acOutputReport, "rptPriceSheetStandard", acFormatPDF, "C:\Users\furtadoa\Desktop\PriceSheetPDF" & "\" & rst![Customer] & ".pdf"

I'm assuming the output process is wrong. Can you assist?

Also, don't mind the comments. They are from another post that contributed to my Frankenstein code.

 

[pbaldy]

For starters, you declared and set "rs" but here used "rst".

 

[Adam.Furtado]

Okay, I fixed that. Now I have an error claiming incorrect syntax for my filter. in the OpenReport Event.

Error: 3075:

Syntax Error (missing operator) in query expression '(Customer=3A Composites)'.

It tells me the error lies in my output coding still, but here is my Open Report Event.

Private Sub Report_Open(Cancel As Integer)

Private Sub Report_Open(Cancel As Integer)
Me.Filter = "Customer=" & Forms![frmCreateReport]![cboCustomerSelect]
Me.FilterOn = True
End Sub

([cboCustomerSelect] is now an unbound text box)

I just realized that I was also following an old thread that you assisted on 5 years ago... Thanks for helping in the past and present.

Just in case you wanted to see the rest of the code:

Private Sub Command23_Click()



Dim MyDB As DAO.Database, RS As DAO.Recordset
Dim strBody As String, lngCount As Long, lngRSCount As Long
DoCmd.RunCommand acCmdSaveRecord
Set MyDB = DBEngine.Workspaces(0).Databases(0)


Set RS = MyDB.OpenRecordset _
("SELECT DISTINCT [Customer] FROM tblCustomerList")


lngRSCount = RS.RecordCount
If lngRSCount = 0 Then
MsgBox "No price sheets to print", vbInformation
Else
RS.MoveLast
RS.MoveFirst
Do Until RS.EOF

Forms![frmCreateReport]![cboCustomerSelect] = RS![Customer]
Beep

[COLOR="Red"][B]DoCmd.OutputTo acOutputReport, "rptPriceSheetStandard", acFormatPDF, "C:\Users\furtadoa\Desktop\PriceSheetPDF" & "\" & RS![Customer] & ".pdf"[/B][/COLOR]


Debug.Print RS![Customer] 'this will put each store ID in the VBA immediate window, so you can see that it spun through them. Only needed for testing


RS.MoveNext
Loop

End If
RS.Close
MyDB.Close
Set RS = Nothing
Set MyDB = Nothing
Close


MsgBox "Done exporting price sheets. ", vbInformation, "Done"

Exit Sub


End Sub

 

[pbaldy]

Because your field is text, the filter line needs to be:

Me.Filter = "Customer='" & Forms![frmCreateReport]![cboCustomerSelect] & "'"

which should work, unless the data could have an apostrophe, in which case there is a work around.

 

[Adam.Furtado]

Unfortunately, I'm getting the same error. It still directs me to my OutputTo line.

 

[pbaldy]

Does the report open correctly directly from the navigation pane? Can you post the db here?

 

[Adam.Furtado]

Unfortunately, I can't attach it, but I put it on my Google, Drive... which is useless having just learned I have too few posts to paste a link in my post.

It is 2.62 mb because I had to draw in the linked tables, so it's pretty hefty right now.

 

[Adam.Furtado]

Aha! I recreated it without the unneccesary features.

I am looking to perform this operation through the frmCreateReport. Should be obvious which button is the go-to. Trying to produce "rptPriceSheetStandard", which is not functional right now, but rptPriceSheetQuarterly is.

Reports are dependent on the combo box on frmCreateReport.

 

[pbaldy]

This doesn't appear to have the code I suggested in post 6. Both reports appear to require tblCustomerNotes, which isn't in the sample so I can't really test.

 

[Adam.Furtado]

Okay, I fixed the "Notes" part, so it should be fully functional. I removed all of the test buttons except for Command23 on frmCreateReport, as that is the only relevant one. I also checked the filter code on the Open_Report event, but I don't understand how it is differs from the code in Post #6.

Thanks for your help, by the way.

 

[Adam.Furtado]

SUCCESS! Can't believe i missed that apostrophe.

For those searching, this is my final code to output to multiple PDFs....

In OpenReport Event:

Private Sub Report_Open(Cancel As Integer)
Me.Filter = "Customer='" & Forms![frmCreateReport]![txtCustomerSelect] & "'"
Me.FilterOn = True
End Sub

OnClick Event for button:

Private Sub Command23_Click()



Dim MyDB As DAO.Database, RS As DAO.Recordset
Dim strBody As String, lngCount As Long, lngRSCount As Long
DoCmd.RunCommand acCmdSaveRecord
Set MyDB = DBEngine.Workspaces(0).Databases(0)


Set RS = MyDB.OpenRecordset _
("SELECT DISTINCT [Customer] FROM tblCustomerList")


lngRSCount = RS.RecordCount
If lngRSCount = 0 Then
MsgBox "No price sheets to print", vbInformation
Else
RS.MoveLast
RS.MoveFirst
Do Until RS.EOF

Forms![frmCreateReport]![txtCustomerSelect] = RS![Customer]
Beep

DoCmd.OutputTo acOutputReport, "rptPriceSheetStandard", acFormatPDF, "C:\Users\furtadoa\Desktop\PriceSheetPDF" & "\" & RS![Customer] & ".pdf"


Debug.Print RS![Customer] 'this will put each store ID in the VBA immediate window, so you can see that it spun through them. Only needed for testing


RS.MoveNext
Loop

End If
RS.Close
MyDB.Close
Set RS = Nothing
Set MyDB = Nothing
Close


MsgBox "Done exporting price sheets. ", vbInformation, "Done"

Exit Sub


End Sub

pbaldy, thank you 10000x over. I owe you!

 

[pbaldy]

Happy to help, and I'm gonna collect!! I've got a lot of family in MA!

 

[gwenise]

Adam. This code was so very helpful to me. I also needed to create multiple pdf files from one report and I just adapted this code for my needs.

Thanks.