[RESOLVED] Keep track of an adodb Recordset

[morleyz]

I have an application with a routine that generates a recordset and then passes that to a report window. I would like to close the recordset when the report is closed, but I can't because I lose track of the recordset. How do I identify a distinct recordset so I can tie to a form so it can be closed later. Here's a simplified version of the code:

sub doReport()
     Dim myRecordset AS ADODB.RECORDSET
     Dim myReport as reportWindow

     SQLQuery = "SELECT * FROM myTable"
     DB.QUERY SQLQuery, myRecordset     'This populates my recordset

     Set myReport.Recordset = myRecordset
     myReport.show
End sub

What I'd like to do is something along the lines of:

private myRecordsets(10,10)

sub doReport()
     Dim myRecordset AS ADODB.RECORDSET
     Dim myReport as reportWindow

     SQLQuery = "SELECT * FROM myTable"
     DB.QUERY SQLQuery, myRecordset     'This populates my recordset

     Set myReport.Recordset = myRecordset
     addReport myReport.hwnd, myRecordset.SOMEIDENTIFIER
     myReport.show
End sub

sub closeRecordset(formID)
     'Finds the matching recordset for the given formID and closes it
End sub

sub addReport(formID, recordsetID)
     'Add the formID and recordsetID to array
End sub

Hopefully that makes sense, I just don't know what to use to uniquely identify a recordset (like form.hwnd) in memory so I can refer back to it in the close routine.

Any suggestions?

[brucevde]

I would like to close the recordset when the report is closed,

Why make it so complicated? Can't you just do MyReport.Recordset.Close when the ReportWindow shutsdown.

[wes4dbt]

I would not wait to close it.

myreport.Show vbModal
set myRecordset = Nothing

[morleyz]

the reason I need to identify and wait to close is that this is an MDI application with multiple report windows open at the same time, call called from the same routine.

If I close it right away, the report errors out because the recordset is closed.

[si_the_geek]

I would not wait to close it.

You forgot to actually close it

myreport.Show vbModal
myRecordset.Close
set myRecordset = Nothing

If I close it right away, the report errors out because the recordset is closed.

How about bruce's suggestion?

[morleyz]

I tried Bruce's method and the Crystal viewer always errored when loading. I'm going to try it again that way and see what problem I had. That seemed like the most logical way to me too, but it wasn't working when I tried it.

[morleyz]

Alright, you guys get the bonus points for making me figure out why the easy way didn't work. I was getting automation error with the Crystal view when I tried this before. I changed the order of the way I reference things and it seems to accomplish what I want (and what you suggested) by having the recordset be a variable along with the form and have the form close it when it's unloaded.

the better news is it seems to have fixed my memory leak...Thanks to both of you.