Accessing recordsets

[Chunk]

I have a general question on accessing recordsets

My VBA creates a recordset in module1.

I purposefully did not close the connection or the recordset.

I would like to be able to re-use the recordset in a couple of different modules (say 2 and 3).

Is this even possible? Im looking for any sites, literature, etc that can help. Any direction you can give is greatly appreciated.

Thanks in advance.

-Chunk

[June7]

Declare recordset object variable in module header. Google "public global variable".

[Chunk]

June7,

Can I pass it like any other variable?
Module2

Public rs as adodb.recordset

Module1

Dim rs as adodb.recordset

Create recordset

Module2.rs = rs

I tested by putting all of the code into the same module, declaring the recordset variable (rs) as Public.

Created in first sub, second sub is reporting a Rub-time Error '91': Object variable or with block variable not set.

I'll send code in a few hours, after work.

Respectfully,

-Chunk

[June7]

If you declare as public in header then don't use Dim in procedure.
Consider DAO recordset.

Public rs As DAO.Recordset

Sub MySub()
Set rs = CurrentDb.OpenRecordset("SELECT * FROM MyTable")
'do something with recordset
End Sub

Be aware that public/global variable loses value in unhandled run-time error.