automated report

[jamesmor]

I have a rather large data file that I need to produce a report from, its basically a commision report with all a reps sales in the detail field with some calculations in the report footer, now in this file is about 50 reps and I didn't want to have to split it into smaller files so I'm thinking of trying to use the below code to output the report directly to the printer. I was wondering if this would be the best way to do it or if there is a better one.

This code isn't complete as I don't have the report made yet.

Option Compare Database

Option Explicit

'------------------------------------------------------------
' Orders
'
'------------------------------------------------------------
Function Orders()
'On Error GoTo Orders_Err

Dim db As Database
Dim rst As DAO.Recordset
Dim rstRep As DAO.Recordset
Dim strRep As String
'Dim strEMail As String
Dim qryDef As DAO.QueryDef

Set db = CurrentDb

Set rstRep = db.OpenRecordset("SELECT DISTINCT Active_Customers_Final.Team_Lead FROM Active_Customers_Final ORDER BY Active_Customers_Final.Team_Lead;", dbOpenDynaset)

On Error Resume Next
DoCmd.DeleteObject acQuery, "qryTmp"
'On Error GoTo Orders_Err

With rstRep
Do While Not .EOF
strRep = .Fields(0)
Set qryDef = db.CreateQueryDef("qryTmp", "SELECT * FROM Active_Customers_Final WHERE Active_Customers_Final.Team_Lead = """ & strRep & """ ORDER BY Active_Customers_Final.Team_Lead;")
DoCmd.OpenReport "", acViewNormal, "", "", acNormal
DoCmd.DeleteObject acQuery, "qryTmp"
.MoveNext
Loop
End With

 

[john471]

Why would you not run/produce one report based on (a query based on)Active_Customers_Final, ordered on Team_Lead, and grouped on Team_Lead, with a Team_Lead section footer for the calculated values, and appropriate Force New Page settings ?

I've just noticed your commented out variable 'Dim strEMail As String
Are you going to email each Team_Lead report to different people ???

If that's the case, you may well need something like what you've got (although I don't know), in which case, inside your loop, your ORDER BY Active_Customers_Final.Team_Lead clause is redundant, because you are selecting only one at a time.

I know Pat would know for sure, but I rather think that by deleting and creating a querydef for each iteration of the loop you are adding an unnecessary overhead, as access would need to re-compile / optimise the query each time. It might be worth trying one (predefined) querydef with a parameter based on a textbox field in a form, then open that form and change the value of the text-box on each iteration.

Just a thought.

HTH

John.