Open recordset on report open, close on report close

[bulrush]

A2003 on WinXP

I'm in a situation where I need to open a recordset named "summset" when a report opens, in a Group1_Print event I need to save records with summary totals to an existing table "CommSumm", then when the report closes I need to close my recordset. I need this opened recordset ("summset") available in all areas of the report. I did make my varitable a public variable at the report level:
public summset as dao.recordset

I was putting my recordset open in my Report_Activate event but it is not getting fired before the Group1_Print event. Then I put my recordset close in my Report_Deactivate event.

Private Sub Report_Activate()
Dim mo As String, yr As String
Dim crit As String

On Error GoTo MyError

Call InitGlobal ' Basically grabs network username. Does not touch recordsets.
Set MyDB = CurrentDb()
Set errset = MyDB.OpenRecordset("ErrTable", dbOpenDynaset) ' Used to save errors
Set summset = MyDB.OpenRecordset("CommSumm", dbOpenDynaset)

' First delete all records for this period from table CommSumm.
mo = Format(Forms("00Main")!txtBeginDate, "mm")
yr = Format(Forms("00main")!txtBeginDate, "yyyy")

crit = "DELETE FROM CommSumm WHERE period='" & yr & "-" & mo & "';"
DoCmd.SetWarnings False
DoCmd.RunSQL (crit)
DoCmd.SetWarnings True

Which events do I open my recordset and close my recordset in?

You see, when Group1_Print fires, I try to add records to summset but I get a "object not set" error. So I put in a few Msgbox's, and Report_Activate was not getting fired before Group1_Print, so the summset was not getting opened.

NOTE: I commonly have the report in design mode, then go to preview mode. This may have something to do with Report_Activate not getting fired. I need to find out events which get fired when I go from design mode to report preview mode.

 

[gemma-the-husky]

i don't understand why you need to do this as part of the report

I don't really understand why you need to do it at all - but surely you can do he whole thing as part of the open process for the report - why would it need to conincide with the reports group events? on that basis it could be a separate process fired by whatever event is used to prepare the report

eg, have two buttons -
1 - extract subtotal data
2 - prepare report

or just manage the two functions on one button.


(hope that makes sense - ie it can't make any difference {or even be apparent, probably} to the user preparing the report, that the code is doing some other stuff at the same time)

 

[bulrush]

1. This report does complex calculations, so it would be a bad idea for me to duplicate this code somewhere else. Because if I duplicated the calculation code, the chance of having a difference is about 100%.

2. This report prints details on salesman commissions, with a subtotal for a product group, then a total for each salesman. Later on, I want a summary report, which only prints the total commission for each salesman. This is, essentially, 2 different report types. You cannot have 2 report types in the same report in Access. So I need to save the summary data (total for each salesman) in another table, then run a report on that other table.

3. I figured this out. I can open my recordset in Report_Activate, and close the recordsets in Report_Close.