Passing two recordsets to another sub

[mxtreme]

Is this possible to pass two recordsets to another sub??? I assumed using the code ...

-------------------------------------
dim rs1 as dao.recordset
dim rs2 as dao.recordset

code...

code...

bulkSelect (rs1, rs2)

Pass to another sub

Public Sub bulkSelect(rs1 As dao.recordset, rs2 As dao.recordset)

--------------------------

However, loading the method is fine but when the call is made, an error occurs, " Compile error, Expected: = "


Any help appreciated.

Shaun.

 

[RoyVidar]

Works fine on my setup, you might try to call it with the keyword call:

[tt]call bulkSelect(rs1, rs2)[/tt]

- and, the public sub is in a module, not a forms/reports module

Roy-Vidar

 

[mxtreme]

i put "call" in front of the statement and initially it is fine but when it runs, error occurs...

" Compile error, ByRef argument type mismatch "

Additionally, I didn't understand your last statement about the modules, could you rephrase or clarify if possible.

Thanx for the help,

Shaun.

 

[RoyVidar]

Only way I'm able to reproduce the error, is by changing the datatype of rs either in the public sub declaration or calling sub declaration (the type mismatch). You are sure rs and rs2 are declared as dao.recordsets when the call is made? Are there any public variables having the same names...

You can check the recordset with the following snippet (on both recordset):

[tt]if typeof rs is dao.recordset
msgbox "DAO"
else if typeof rs is ado.recordset then
msgbox "ADO"
else
msgbox "Don't know"
end if[/tt]

- for this to work, both the DAO and ADO recordset libraries need to be checked

There are form modules and report modules, and there are modules (insert module...). I was asking whether this public sub was located in a module and not in a report or forms module - latter is often recommended, though if it is withing the current form or report, it should also work.

Roy-Vidar

 

[mxtreme]

Ok, to answer a few questions.
The code has not been written with any reference to ADO objects. Both recordsets in question are DAO recordsets from a DAO Database reference.

The code is written in a form module, which i am assuming is the code generated with relation to a particular form. As this DB has two forms, I have this code relating to one form and there is another code segment held under the name of the other form used.

I ran the msgBox check code and both recordsets returned to be DAO recordsets. I am not sure what else I can tell you. I hope some of this is making sense.

Shaun.

 

[RoyVidar]

I've successfully run the following on versions 97 thru xp:

The first part, behind a button click on a form. Regarding the public sub, I've had it both within the same sub as the "button event", and in a separate module (meaning a module, not a forms/reports module).

No errors. As far as I've been able to tell by my testing, the "ByRef argument type mismatch" means there is a data type mismatch between the declaration of the public sub and the datatypes of the variables in the call.

Suggestions: You'll need to take a look at your code, declarations etc and check if what's being passed really are dao recordsets. Also check for duplicate names of both subs and variables with different scope.

Since this work on my setup, try building it incremental (new db, if that's what it takes), take it step by step and see at what point errors occur.

Finally, check out the possibilities of corruption (keyword search here would reval ways to deal with that)

[tt] Dim rs As DAO.Recordset
Dim rs2 As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("select * from table1")
Set rs2 = CurrentDb.OpenRecordset("select * from table2")

Do While Not rs.EOF
Debug.Print rs!id, "first rs"
rs.MoveNext
Loop
rs.MoveFirst
Do While Not rs2.EOF
Debug.Print rs2!id, "first rs2"
rs2.MoveNext
Loop
rs2.MoveFirst
Call testdao(rs, rs2)
rs.Close
rs2.Close
Set rs = Nothing
Set rs2 = Nothing


Public Sub testdao(rs As DAO.Recordset, rs2 As DAO.Recordset)
Do While Not rs.EOF
Debug.Print rs!id, "second rs"
rs.MoveNext
Loop
rs.MoveFirst
Do While Not rs2.EOF
Debug.Print rs2!id, "second rs2"
rs2.MoveNext
Loop
rs2.MoveFirst
End Sub[/tt]


Roy-Vidar

 

[mxtreme]

Thanx Roy, very valuable words of wisdom.

The answer to my quandary turned out to be relatively simple, i was declaring in non-standard access form.

I had been used to declaring varibales like this...

Dim rs, r1, rs2 As dao.recordset

but now I realise Access doesn't like it so I will now declare them as...

Dim rs As dao.recordset
Dim rs1 As dao.recordset
Dim rs2 As dao.recordset

Thanx for your help Roy, you solved my problems, although some may have been self-imposed.

Shaun.