Splitting a report into separate pdf's based on group

[Kaye960]

I have an inventory report that I run by Sales Rep that shows the inventory for all of their clients. I need to be able to create separate pdf's for each client. There may be several pages for each one. Any help would be greatly appreciated.

Here is what I have now:

Private Sub Command32_Click()
On Error GoTo Err_Command32_Click
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim qdf As QueryDef

Set db = CurrentDb
Set qdf = db.QueryDefs("qry_BM_InvRepConsolNoZeroC(Final") 'My parameter query
qdf.Parameters(0) = [Forms]![frmInventoryReportParameters]![cmbSalesperson] 'Form control
Set rst = qdf.OpenRecordset

 
 
  Do While Not rst.EOF
    strRptFilter = "[ClientName] = " & rst.Fields("ClientName")
 

DoCmd.OutputTo acOutputReport, "rpt_BM_InvRepConsolNoZeros", acFormatPDF, "R:\Sandy's Playground" & "\" & rst.Fields("ClientName") & ".pdf"
    DoEvents
    rst.MoveNext
Loop

rst.Close
Set rst = Nothing
Set qdf = Nothing
Set db = Nothing
Exit_Command32_Click:
    Exit Sub

Err_Command32_Click:
    MsgBox Err.Description
    Resume Exit_Command32_Click
    
End Sub
Private Sub Command33_Click()
On Error GoTo Err_Command33_Click

    Dim stDocName As String

    stDocName = "rpt_BM_InvRepConsol2"
    DoCmd.OpenReport stDocName, acPreview

Exit_Command33_Click:
    Exit Sub

Err_Command33_Click:
    MsgBox Err.Description
    Resume Exit_Command33_Click

I keep getting "Item not found in this collection" error



What am I doing wrong?

[ItsMe]

You should avoid special characters in the names of your columns/fields and objects.

I have not tried to use the Parameters collection with querydefs. Why did you choose to go this route? I looked at MSDN and it seems there is not much that can be done with a Parameter Object. So unless you are going to use your Query for different forms, there does not seem to be a need to access the Parameters collection. In other words, it seems the only assignment you can make is the Value. So, might as well hard code the form name and control name into the query object.

Having said that, it seems you want to loop through some records and add dynamic criteria. In that case, you would need to change your code significantly.

There are several different ways you could go about this. The simplest would be to add a WHERE clause to your DAO recordset at the time it is opened. So something like ...

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strRptFilter As String

'Need to determine if ClientName and
'cmbSalesperson are type number ot
'type string
Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT * FROM qry_BM_InvRepConsolNoZeroC(Final WHERE [ClientName] = " & Me.cmbSalesperson)   'Not sure if this is a typo here ... eroC(Final)

While Not rst.EOF
strRptFilter = "[ClientName] = " & rst.Fields("ClientName") 'Is this of type number?

DoCmd.OutputTo acOutputReport, "rpt_BM_InvRepConsolNoZeros", acFormatPDF, "R:\Sandy's Playground" & "\" & rst.Fields("ClientName") & ".pdf"
rst.MoveNext
Wend

rst.Close
Set rst = Nothing
Set db = Nothing

[Kaye960]

Thank you for you help. I have change the name of my query to remove the special characters. I know enough vba to be dangerous, but I have gotten pretty good at being able to find the code I need and modify it to work in my databases. I used this one, after trying several others that didn't work, because it did not require the Where clause that kept giving me "too few parameter" errors. It's still not quite right and I am not sure how to fix it. My query filters the report by Salesperson based on the value on the form cmbSalesperson, ClientName is String. What I need now is when the Salesperson is chosen, the report runs for all of the clients and creates a pdf file for each client.

Based on your response, here is what I have, but I think I missed something somewhere.

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strRptFilter As String


Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT * FROM qry_BM_InvRepConsolNoZeroC_Final WHERE [Salesperson] = " & Me.cmbSalesperson)

While Not rst.EOF
strRptFilter = "[ClientName] = " & rst.Fields("ClientName") 

DoCmd.OutputTo acOutputReport, "rpt_BM_InvRepConsolNoZeros", acFormatPDF, "R:\Sandy's Playground" & "\" & rst.Fields("ClientName") & ".pdf"
rst.MoveNext
Wend

rst.Close
Set rst = Nothing
Set db = Nothing

Am I getting closer?

[ItsMe]

You have a few problems and your code is getting closer to something that will work. Ultimately, you may want to refactor your code to get better performance. The error is likely because qry_BM_InvRepConsolNoZeroC_Final does not have a Salesperson column and or there is a parameter hard coded in the object. Remove any Parameters and make your query object a simple SELECT query , being sure to include the Salesperson column. Then you will need some code like the following. Test without the loop so you do not error inside the loop and freeze your PC.

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strRptFilter As String

Set db = CurrentDb
'Set rst = db.OpenRecordset("SELECT * FROM qry_BM_InvRepConsolNoZeroC_Final WHERE [Salesperson] = " & Me.cmbSalesperson)

'If Salesperson is text you will need to use the following syntax. Make sure the bound column in your combo is the correct one.
Set rst = db.OpenRecordset("SELECT * FROM qry_BM_InvRepConsolNoZeroC_Final WHERE [Salesperson] = '" & Me.cmbSalesperson & "'")

'While Not rst.EOF

'Since ClientName is text you need to use the following syntax.
strRptFilter = "[ClientName] = '" & rst.Fields("ClientName") & "'"

'After you get the expected parameter error out of the way, you will
'need to include code to apply the where criteria to your report's Filter property

DoCmd.OpenReport "rpt_BM_InvRepConsolNoZeros", , , strRptFilter
DoCmd.SelectObject acReport, "rpt_BM_InvRepConsolNoZeros"

'Now we can output the object
DoCmd.OutputTo acOutputReport, "rpt_BM_InvRepConsolNoZeros", acFormatPDF, "R:\Sandy's Playground" & "\" & rst.Fields("ClientName") & ".pdf"

'We need to close the report
DoCmd.Close acReport, "rpt_BM_InvRepConsolNoZeros"

'rst.MoveNext
'Wend

rst.Close
Set rst = Nothing
Set db = Nothing

[Kaye960]

I think I understand. Unfortunately the underlying queries are based on several other queries with parameters due to date fields. I'm going to have to reevaluate the entire process to see if there is an easier way to create the query with out all of the parameters.
Thank you so much for your help!

[ItsMe]

IIRC, parameterized queries are not happy within a DAO recordset. There is a work around, but it is not simple.

[Kaye960]

I have it working now! Thank you so much for all of you help.