Recordsets in Subroutines

[dhstein]

I have a Subroutine like btnButton1_Click() and I open a recordset there.
Then I want to call another routine like SubDoSomeProcess (Parameters). In
SubDoSomeProcess can I access the same recordset? Do I have to pass the
recordset name? If I do a FindFirst in the main routine what happens if I do
a FindNext in the subroutine? Thanks for any help on this.

 

[Dirk Goldgar]

I have a Subroutine like btnButton1_Click() and I open a recordset there.
Then I want to call another routine like SubDoSomeProcess (Parameters).
In
SubDoSomeProcess can I access the same recordset? Do I have to pass the
recordset name? If I do a FindFirst in the main routine what happens if I
do
a FindNext in the subroutine? Thanks for any help on this.

Normally you would pass the recordset object itself -- not its name -- as an
argument to the second procedure. Like this:

'----- start of example code -----
Private Sub btnButton1_Click()

Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("SomeQuery")

rs.FindFirst "ID = 123"

If Not rs.NoMatch Then
Call subDoSomeProcess(rs)
End Sub

rs.Close

End Sub

Sub subDoSomeProcess(ArgRS As DAO.Recordset)

Do While Not rs.NoMatch

' ... do something with current record ...

' Find next matching record
rs.FindNext "ID = " & rs!ID

Loop

End Sub
'----- end of example code -----

Because it's the same recordset object in both procedures, the state of the
recordset on entry to subDoSomeProcess is the same as it was in
btnButton1_Click() right before subDoSomeProcess is called. And when
control returns to the calling procedure, the recordset will be in whatever
state the called routine left it in.

 

[dhstein]

Dick,

Thanks for that clear answer. One more question. If I don't pass the
recordset object, can I use the recordset "rs" in SubDoSomeProcess without
affecting the record pointer in the original calling routine? Thanks.
David

 

[Dirk Goldgar]

Dick,

Thanks for that clear answer. One more question. If I don't pass the
recordset object, can I use the recordset "rs" in SubDoSomeProcess without
affecting the record pointer in the original calling routine? Thanks.

No -- or yes, depending on what you mean. If you don't pass it as an
argument, the recordset that was declared in the calling routine would not
be known to the called routine ("subDoSomeProcess") at all. You could
declare a *different* recordset in subDoSomeProcess, and it could have the
same name "rs", but that wouldn't affect the original recordset at all. For
example,

'----- start of example code -----
Private Sub btnButton1_Click()

Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("SomeQuery")

rs.FindFirst "ID = 123"

Call subDoSomeProcess

rs.Close

End Sub

Sub subDoSomeProcess()

Dim rs As DAO.Recordset ' <-- this is a new recordset

Set rs = CurrentDb.OpenRecordset("SomeOtherQuery")

' ... do something with the recordset ...

rs.Close

End Sub
'----- end of example code -----

In subDoSomeProcess, the recordset declared as "rs" is a totally different
object from the like-named recordset in btnButton1_Click, so nothing you do
to either recordset has any effect on the other.

It is *possible* to declare a recordset object at the module level or even
global in scope, in which the same recordset object could be shared among
multiple procedures. However, that requires special handling, and should be
the exception, not the rule.

By the way, I just reread my earlier response and noticed an error. I gave
this example there:

Sub subDoSomeProcess(ArgRS As DAO.Recordset)

Do While Not rs.NoMatch

' ... do something with current record ...

' Find next matching record
rs.FindNext "ID = " & rs!ID

Loop

End Sub

Because I gave the argument recordset the name "ArgRS", I should have used
that name throughout the procedure, not "rs": "ArgRS.NoMatch",
"ArgRS.FindNext". The name "rs" would not be known in that procedure.

 

[dhstein]

Dick,

Thanks once again for your very clear explanation.

David