Getting Module-Wide Scope for Recordsets

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi!

I am writing some fairly extensive VBA logic within a form module,
with multiple recordsets being defined and used. How/where can
I define them so that they are accessible from various procedures
within my form? The resources I have indicate this is possible, but
nowhere can I find the exact syntax, etc. to make it work!

Can someone out there help?

Thanks,
Tom
 
To create a recordset variable that is available to all the procedures in a
module, declare it in the General Declarations section of the module (i.e.
at the top with the Option statements).

Unless you need to keep the recordset open between calls, you could declare
it without one routine and pass it to others that need to work with it. This
example shows how one routine can work with a recordset declared in another:

Private Sub DoSomething()
Dim rs As DAO.Recordset
Call LoadIt(rs) 'This routine initializes it.
Call WortWithIt(rs) 'This does something with it.
rs.Close 'Now we are finished with it
Set rs = Nothing
End Sub

Private Sub LoadIt(rst As DAO.Recordset)
Set rst = dbEngine(0)(0).OpenRecordset("SELECT * FROM MyTable;")
End Sub

Private Sub WorkWithIt(rs As DAO.Recordset)
rs.MoveLast
End Sub
 
Tom,

You can write your code within a form module in a public sub such as Public Sub
MyVBALogic and then call that sub from any procedure in the form module by Call
MyVBALogic. Or you can write your code within a form module in a public function
such as Public Function CheckMyValue() and then use the function in any
procedure in the form module by If CheckMyValue() = True.
 
Further to the other replies (both of which are fine), I think that
this is what you are actually asking for:

Declare the database & recordset variables at the start of your form
module, before any procedures or functions. This will make those items
globally visible throughout that whole code module:

private db as database, rs1 as recordset, rs2 as recordset, ...

Then initialize them in Form_Open or Form_Load:

set db = currentdb()
set rs1 = db.openrecordset (...)
set rs2 = db.openrecordset (...)

Then you can use those items, directly, from any other procedures &
functions within that code module.

Then close them in Form_Close or Form_Unload:

set rs1 = nothing
set rs2 = nothing
set db = nothing

Of course, you may want to choose better names than "db", "rs1" etc.
for names that are globally visible through the whole code module.
Personally I would call them gDB, gRS1, etc. YMMV.

HTH,
TC
 
Back
Top