how to pass ref to a recordset to another function (running out of time)

My problem is pretty simple but I can't figure it out.

I have 2 routines like these:

Sub myRoutine
   Dim RS as ADODB.Recordset
   Set RS = New ADODB.Recordset

   RS.Open(strSQL, dba, adOpenKeyset, adLockOptimistic)

   Call NewFunction(RS)

End Sub

Function NewFunction(RS as ADODB.Recordset)
'    get a record and close the recordset
End Function

I always get the error
Compile Error:
ByRef argument Type Mismatch

What's wrong?
DLockwoodAsked:
Who is Participating?
 
DanRollinsConnect With a Mentor Commented:
DLockwood, an EE Moderator will handle this for you.
Moderator, my recommended disposition is:

    Split points between: bobbit31 and raizon and EasyAim and acperkins@devx

DanRollins -- EE database cleanup volunteer
0
 
bobbit31Commented:
Function NewFunction(ByVal RS as ADODB.Recordset)
'    get a record and close the recordset
End Function

0
 
raizonCommented:
Why send the recordset to NewFunction?

Why not just send the querystring (strSQL) to NewFunction then open your recordset, get your record and then close the recordset in the same function.

I'm not sure but I think this will improve performance by passing a string instead of a recordset to a function.

Just a thought.

Raizon
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
gencrossCommented:
At what point to you get the error.  I run this code fine.  Is it when you are selected the record in NewFunction?  If so, what is the code in there.

What does you db connection look like?

The only thing I had to do to run this code is remove the pars around RS.Open.
0
 
EasyAimCommented:
Yeah, you need to tell us exactly what line your compile error is occurring.
0
 
DLockwoodAuthor Commented:
You guys are all great!
I appreciate the help and the speed.

I think that Bobbit31 hit the nail right on the head by inserting the byVal reference into the top of my function.

I was getting the error on the line that actually calls the function and passes the RS.

Thanks again to everyone!!

DL
0
 
DLockwoodAuthor Commented:
OK I spoke toon soon.
It still give me an error at the point where I call the function.

Here is the exact point I am trying to accomplish. I am fixing a program for a client in which the author uses 40 different recordset objects and doesn't ever close or set them to nothing.

I thought the easiest answer would be to create a funciton that is passed the recordset object. Then the function can check to see if it is open, and close it, and also check to see if it is set to nothing, and set it.

When I call the function and try to pass it the recordset object, I get the type mismatch error.

Thanks for the help!
0
 
raizonCommented:
I would in the queryunload event

check each rs state and if it is open then close it

Private Sub Form_QueryUnload
  If rs.state = 1 Then rs.close
  Set rs = Nothing
End Sub
0
 
DLockwoodAuthor Commented:
Raizon you are on the right track, but I don't think you understand the issue.

I will use your code to be clearer.

Yours is:

Private Sub Form_QueryUnload
 If rs.state = 1 Then rs.close
 Set rs = Nothing
End Sub

What I am trying to do is this:

Public Sub ClearRecordset(byref rs as ADODB.Recordset)
 If rs.state = 1 Then rs.close
 Set rs = Nothing
End Sub

then within another procedure I could simply put:

Call ClearRecordset(myCurrentRS)

and it would check and close appropriately.

Does that make sense?
0
 
raizonCommented:
Yeah I'm following what you are trying to do.

Personally Ive never had any luck passing a recordset ByRef.

What error do you get with this using ByVal?


Public Sub ClearRecordset(ByVal rs as ADODB.Recordset)
If rs.state = 1 Then rs.close
Set rs = Nothing
End Sub

0
 
DLockwoodAuthor Commented:
It's the same error. "Type mismatch"

In theory this should work real slick, but something is not right.
0
 
raizonCommented:
If rs.state is = 0 when you call this function then there is no recordset and therefor nothing to pass.  Then I could see getting this error message.

I understand what you are trying to accomplish.

How difficult would it be to replace

Call ClearRecordset(myCurrentRS)

WIth

If rs.State = 1 Then rs.Close

Then destroy the object when the application is closed.

Just another thought
0
 
DLockwoodAuthor Commented:
Raizon you are on the right track, but I don't think you understand the issue.

I will use your code to be clearer.

Yours is:

Private Sub Form_QueryUnload
 If rs.state = 1 Then rs.close
 Set rs = Nothing
End Sub

What I am trying to do is this:

Public Sub ClearRecordset(byref rs as ADODB.Recordset)
 If rs.state = 1 Then rs.close
 Set rs = Nothing
End Sub

then within another procedure I could simply put:

Call ClearRecordset(myCurrentRS)

and it would check and close appropriately.

Does that make sense?
0
 
DLockwoodAuthor Commented:
Raizon,

I have already started down that path. It make take a while, but it will definitely work.

Thanks for the help anyway.

DL
0
 
deightonCommented:
presumably you've tried

Function NewFunction(RS)
'    get a record and close the recordset
End Function

just to see if you can get it working?
0
 
raizonCommented:
My pleasure
0
 
EasyAimCommented:
Here's a really robust routine to close a recordset, whether it is open or not.....


'
'===================================================
Public Function adoCloseRS(rsTemp As ADODB.Recordset, Optional bKeepInstantiated As Boolean = False) As Boolean
 '
 '  painlessly close a recordset
 '
  On Error GoTo ErrorHandler
  If Not rsTemp Is Nothing Then
    If rsTemp.State <> adStateClosed Then rsTemp.Close
  End If
  If Not bKeepInstantiated Then Set rsTemp = Nothing
  adoCloseRS = True
  Exit Function
'--------------------------------------
ErrorHandler:
  If Err.Number = 3219 Then   ' pending update exists
    rsTemp.CancelUpdate
    Resume
  Else
    adoCloseRS = False
  End If
End Function
0
 
DLockwoodAuthor Commented:
EasyAim

What is the syntax for calling this function?
0
 
EasyAimCommented:


  After you get through using a recordset for any reason, merely call it.


  You originally had:

Private Sub Form_QueryUnload
  If rs.state = 1 Then rs.close
  Set rs = Nothing
End Sub



 I would propose:

Private Sub Form_QueryUnload
  adoCloseRS  RS
End Sub



    adoCloseRS  RS
0
 
DLockwoodAuthor Commented:
EasyAim,

Sometimes I get a Type Mismatch Error and other times I don't.

Any ideas?
0
 
EDDYKTCommented:
RS.Open(strSQL, dba, adOpenKeyset, adLockOptimistic)


What is strszl and dba. Take out the () from this statement


0
 
Anthony PerkinsCommented:
You are calling a Function (that implicitly returns a Variant) as follows:

Call NewFunction(RS)

Function NewFunction(RS as ADODB.Recordset)
'    get a record and close the recordset
End Function

When in fact the Function should be a Sub:

Sub NewFunction(RS as ADODB.Recordset)
'    get a record and close the recordset
End Sub

Anthony
0
 
raizonCommented:
Good call acperkins,

I completly overlooked that.
0
 
DLockwoodAuthor Commented:
I have tried it as Sub and as Function. Sometimes I get the "byRef error Type Mismatch" and sometimes I don't.

I have double checked and double checked and the variable that throws the error is created exactly the same as the ones that work.

Any other ideas?
0
 
raizonCommented:
Is the recordset open on the ones that throw the error?

If the recordset isn't open then I don't beleive you can pass it to a function/sub


0
 
Anthony PerkinsCommented:
In case you are doing this, the following:

Call NewFunction(RS)

is not the same as:

NewFunction(RS)

In the first case you are attempting to pass the variable ByRef, in the second you are attempting passing it ByVal.  The keyword is attempting.  It depends on your function/sub declaration as well.

Anthony
0
 
bobbit31Commented:
> In the first case you are attempting to pass the variable ByRef, in the second you are attempting passing
it ByVal.  

huh? i thought vb passes arguments ByRef as the default

and i didn't know there was a difference b/w

Call NewFunction(RS)

and

NewFunction RS

I remember a past post where we were debating the difference b/w the two and i believe it was for backwards compatibility.

then again, i could be wrong
0
 
Anthony PerkinsCommented:
bobbit31

Call NewFunction(RS)

is equivalent to (they both pass RS ByRef)

NewFunction RS

On the other hand:

NewFunction (RS)

RS is passed ByVal

Anthony
0
 
Anthony PerkinsCommented:
Actually to be totally accurate this is true of simple variables.  So if RS is of type Integer, for example, than this is true.  In the case of objects, such as a Recordset you can not call with the syntax

NewFunction (RS)

without getting a Type Mismatch.

Anthony
0
 
Anthony PerkinsCommented:
DLockwood,

But to get back to your question, I am not sure how you are implementing it, but the following works as expected:

Private Sub Command1_Click()
Dim rs As ADODB.Recordset

Set rs = New ADODB.Recordset
With rs
    .Source = "Select * From Customers"
    .ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program Files\Microsoft Visual Studio\VB98\NWIND.MDB"
    .CursorType = adOpenForwardOnly
    .LockType = adLockReadOnly
    .Open Options:=adCmdText
    NewFunction rs
End With
Set rs = Nothing

End Sub

Sub NewFunction(rs As ADODB.Recordset)

If rs.State = adopenstate Then
    rs.Close
End If

End Sub

Anthony
0
 
rkot2000Commented:
>>Sometimes I get a Type Mismatch Error and other times I don't.
Maybe you have two different libraries(ADODB AND DAO) in your project.

In this case you need to declare rs as object and use type of :
Sub NewFunction(rs As object)

If rs typeof is ADODB.Recordset then
If rs.State = adopenstate Then
   rs.Close
End If
end if
End Sub
0
 
rkot2000Commented:
Small correction


If TypeOf rs Is ADODB.Recordset Then

ElseIf TypeOf rs Is DAO.Recordset Then
  'close dao
else
  stop
  'use TypeName to get actual object name/type  
End If


or you can use TypeName() to get actual name

any way changing to an object you can spot your problem

0
 
DLockwoodAuthor Commented:
To the EE administrators.

This question never had a functioing answer for my project, but there were 4 Experts that deserve recognition and the points. These guys busted their tails trying to work it through with me.

I would like to ad the 75 points from Quesition ID 20296330, and 20296330, which both should be deleted. Then I would like to split the 150 points as follows for each of the following experts.

bobbit31 - 25
acperkins - 25
raizon - 50
EasyAim - 50

Thanks to everyone - your help is appreciated.

DL
0
 
DigitalXtremeCommented:
EasyAim,

Please collect your points here:
http://www.experts-exchange.com/Visual_Basic/Q_20452392.html

raizon,

Please collect your points here:
http://www.experts-exchange.com/Visual_Basic/Q_20452391.html

acperkins,

Please collect your points here:
http://www.experts-exchange.com/Visual_Basic/Q_20452390.html

bobbit31,

Please collect your points here:
http://www.experts-exchange.com/Visual_Basic/Q_20452389.html

DigitalXtreme
CS Moderator
0
All Courses

From novice to tech pro — start learning today.