passing recordsets as parameter in a sub

[martin123123]

how can i do this.

when i try, i only get the error subscript out of range.
i call the sub

Fill_List myRecordSet


the declaration looks like this

private sub Fill_List(ByVal r as Recordset) (i've tried byref too..)
begin
...bla.bbla...bla...
End Sub

I get an error when i try to do something with the recordset inside the sub. e.g r.fields.count or r.movefirst

 

[CajunCenturion]

Yes, you can pass an RS as a parameter, but you should pass it by reference. One that you need to be sure is that you're not mixing DAO and ADO recordsets.

The following explicitly uses ADO for all recordsets.

Dim myRecordSet as ADODB.Recordset

Fill_List myRecordSet

Private sub Fill_List(RS as ADODB.Recordset)
begin
...bla.bbla...bla...
End Sub

(By ref is the default)

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[martin123123]

but does it work with an already opened recordset?

 

[martin123123]

you see, when i try what you describe in my code, i get ByRef Argument Type Mismatch error...

don't know why

 

[CCLINT]

Make sure you are not mixing ADO and DAO recordsets (use the correct/full identifier)

 

[martin123123]

it doesn't work!
i declare this as a global

dim Person as ADODB.RecordSet
Set Person = New ADODB.Recordset

Person.Open sql, myConnection

Fill_list Person



and the fill_list sub looks like this:

private sub fill_list(ByRef Person as ADODB.Recordset)
begin

end sub

The debugger stops when I call the fill_list procedure and says "ByRef argument type mismatch"


It works if I change my parameter declaration to byval, but I want it to work with byref

 

[CajunCenturion]

I don't see anything wrong with the code that has been posted, except that you don't actually need the ByRef. I wonder you could acutally cut and paste the actual code from your application. (can tell by capitalization)

Are you using Option Explicit?
If you're declaring Person as Global, then why are you using a "Dim" statement (as opposed to Public)? And if you do require it to be global, then why are you passing it as a ByRef parameter?

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[martin123123]

Im using option explicit.

It's a global and im using the dim statement.
changed it now to public.

The sub need to have a recordset parameter because it is called several times. I have three recordsets and the sub needs to know which to use every time it is called.

anyway, I've sort of figured it out. I've declared the recordset parameter in the sub as ByVal, and then it worked

But it seems that the recordset still behaves as it should as a ByRef. For example, if I close the ByVal recordset in the sub, the global recordset that was passed on to the sub is closed to. But with ByVal the two recordsets should be separate? Can someone explain this?

 

[CajunCenturion]

It's quite likely due to way in which VB manages pointers. VB does handle string pointers at BTSRs which already have a level of indirection built in, therefore passing by value is the correct way to pass a pointer to a string.

As a suggestion, I would change the name of the parameter from Person, since Person is declared globally. It shouldn't matter as far as execution is concerned, but it might appear confusing to the next person who reads the code.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[Databaseguy]

I pass recordsets all the time. Keeps me from opening them multiple times and/or allows me to use it from multiple calling procs.

Private Sub SomeSub()
Dim myRst as new ADODB.Recordset
rstMyRst.Open "tblMyTbl", CurrentProject.Connection, adOpenDynamic, adLockOptimistic
Call myProc(myRst)
End Sub

Public Sub testPassRst(myRst as adodb.recordset)
With myRst
.addnew
.fields(0) = "Some Value"
.update
.End With
End SUb