Passing RecordSet as parameter

  • Thread starter Thread starter Paul Johnson
  • Start date Start date
P

Paul Johnson

Is it possible to pass a RecordsSet object to a sub as a parameter? I took
some trial-and-error stabs at this, and couldn't make it work. I tried
passing objects ByVal and ByRef, but the understanding of these differences
is a little beyond me. As a workaround, I ended up passing a query
statement as a string value to the sub, and the sub then opened a recordset
with CurrentDb.OpenRecordSet(strSQL), and that worked fine. I'm just asking
out of curiosity.

As I think about it, I like the workaround idea, because I know that the
recordset object only has scope in the sub that creates it from the string--
it's not being created in one place and passed around to other subs.

Are there any good references or articles that will shed some light on this?

TIA

Paul Johnson
 
You can pass a recordset as an argument in a function call.

Dim db as DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("Select FirstName From Employees")
Call ListRecords(rs)
rs.Close
Set rs = Nothing
Set db = Nothing
'...
End

Function ListRecords(prs As Recordset)
With prs
Do Until .eof
Debug.Print .Fields(1)
.MoveNext
Loop
End With
End
 
Paul said:
Is it possible to pass a RecordsSet object to a sub as a parameter? I took
some trial-and-error stabs at this, and couldn't make it work. I tried
passing objects ByVal and ByRef, but the understanding of these differences
is a little beyond me. As a workaround, I ended up passing a query
statement as a string value to the sub, and the sub then opened a recordset
with CurrentDb.OpenRecordSet(strSQL), and that worked fine. I'm just asking
out of curiosity.
Click to expand...


Sure you can. It's done by declaring the procedure this
way:

Sub MyProc(rs As Recordset)
rs.MoveFirst
. . .
End Sub

Then call the procedure like this:

Set myrs = db.OpenRecordset( . . .)
MyProc myrs
 
Thanks for the quick response! I have it working now.

My problem: I didn't use the "Call" keyword when calling the sub. Rather, I
was putting the parameter in Parentheses and passing control to the sub
without using the Call keyword (the other way to call it, as Marshall Barton
illustrates, would be to get rid of the parentheses). Of course, the
debugger gave me messages that had little to do with the actual problem, as
far as I could tell. Back to "Subs-101" for me...

Call MySub(param)
or
MySub param

Got it.

Paul Johnson
 
Back
Top