Returning a recordset from a function

[jgoodnight]

I have a function "ReturnDocumentRules" that returns a recordset. Everything works if I do not close the recordset and set it to nothing, but I know this is bad programming practice. If I do close the recordset and set it to nothing, obviously after I have set the function to the recordset, return value is still set to nothing.

My code:

Public Function ReturnDocumentRules() As Recordset
    'Open DB and retrieve rules from Rules table that apply to selected document
    Dim db As Database, rs As Recordset
    Set db = OpenDatabase("H:\9000\9000.mdb")
    Set rs = db.OpenRecordset("SELECT rule FROM Rules WHERE docID=" & frm_mainMenu.cb_DocType.Value _
        & " ORDER BY docID", dbOpenSnapshot)
 
    Set ReturnDocumentRules = rs
 
    rs.Close
    db.Close
    Set rs = Nothing
    Set db = Nothing
 
End Function
 

How can I return this recordset and still properly close it in the function?

[danp129]

You should be able to return a disconnected recordset:
4GuysFromRolla.com - Using Disconnected Recordsets

[mrmelvin]

An additional option would be to declare class level variables in the general declaration of the class. Then use the those varables in your returndocumentrules function. Then close the db and recordset after your done calling the function in you main calling code. For example:
public class abc()
Dim db As Database, rs As Recordset
function ReturnDocumentRules()
db = x
rs = y
end function
private sub button_click()
returndocumentrules()
rs.close
db.close
end sub
end class

[jgoodnight]

Thanks Dan,

The disconnected recordsets worked perfectly. In addition to that link, I read this and this to get it implemented.

[odedruts]

i m also stuck in this problem

i cant close the record set
thanks