Split Report into Multiple based on Groupings 1

[thall29323]

VBA nearly newbie here trying to programmatically create separate reports from one report.

Current situation: I have a sales report whose data is grouped by rep. The report uses a query for its records.

Needed: To separate the report so that each reps report is printed separately.


I know I can do this by creating reports for each rep, but I have 60+ reps to report, seems a bit much to do that.

Anyone have any suggestions, or need more info?

Thanks for any and all ideas!

 

[Remou]

How about using the Where argument of OpenReport?

 

[thall29323]

That's what I was hoping for, but I don't know how to code it.

The query contains the rep IDs. I want a new report each time the rep ID changes. Right now, it breaks on this and creats a new page in a single report.

Can I use the Where argument in this scenario?

 

[Remou]

I was thinking of a set up roughly like this:

Dim rs As DAO.Recordset
Set rs=CurrentDB.OpenRecordset("tblReps")

Do While Not rs.EOF
  DoCmd.OpenReport "rptReps", acViewPreview, , "RepID=" & rs!ID
  rs.MoveNext
Loop

The above is typed, not tested.

 

[thall29323]

Ok.....I'm working through this. However I get a compile error: "Method or data member not found." The rs.EOF is highlighted. Am I missing a reference to a particular library?

Here's my code:

Function PrintDetailReports()
On Error GoTo PrintDetailReports_Err

Dim rs As DAO.Database
Set rs = CurrentDb.OpenRecordset("QReportBHRepCusTotDetail")

Do While Not rs.EOF
DoCmd.OpenReport "ReportBHRepTotDetail", , , "[SALESREP_ID]=" & rs!SALESREP_ID
rs.MoveNext
Loop


PrintDetailReports_Exit:
Exit Function

PrintDetailReports_Err:
MsgBox Error$
Resume PrintDetailReports_Exit

End Function

Thank you for helping me through this. I'm trying to convert from RPG to Access/VB/VBA........it ain't easy!!!

 

[Rolliee]

It sounds to me like you need a reference to the DAO library to include the DAO methods.

 

[thall29323]

I have the reference to the DAO 3.51 objects. What I don't know is whether there are others I need also.

 

[Remou]

Dim rs As DAO.Database
Should read
Dim rs As DAO.Recordset
I think your fingers slipped.

 

[thall29323]

That did it !!!!

Yea!!!

Thank you Remou! You have a star coming your way!