Solved

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

Posted on 2002-05-02
34
297 Views
Last Modified: 2012-06-27
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?
0
Comment
Question by:DLockwood
  • 10
  • 7
  • 5
  • +8
34 Comments
 
LVL 18

Expert Comment

by:bobbit31
ID: 6986304
Function NewFunction(ByVal RS as ADODB.Recordset)
'    get a record and close the recordset
End Function

0
 
LVL 5

Expert Comment

by:raizon
ID: 6986307
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
 
LVL 4

Expert Comment

by:gencross
ID: 6986322
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
 

Expert Comment

by:EasyAim
ID: 6986336
Yeah, you need to tell us exactly what line your compile error is occurring.
0
 

Author Comment

by:DLockwood
ID: 6986357
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
 

Author Comment

by:DLockwood
ID: 6986402
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
 
LVL 5

Expert Comment

by:raizon
ID: 6986413
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
 

Author Comment

by:DLockwood
ID: 6986433
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
 
LVL 5

Expert Comment

by:raizon
ID: 6986453
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
 

Author Comment

by:DLockwood
ID: 6986465
It's the same error. "Type mismatch"

In theory this should work real slick, but something is not right.
0
 
LVL 5

Expert Comment

by:raizon
ID: 6986474
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
 

Author Comment

by:DLockwood
ID: 6986485
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
 

Author Comment

by:DLockwood
ID: 6986492
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
 
LVL 18

Expert Comment

by:deighton
ID: 6986503
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
 
LVL 5

Expert Comment

by:raizon
ID: 6986504
My pleasure
0
 

Expert Comment

by:EasyAim
ID: 6986588
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
 

Author Comment

by:DLockwood
ID: 6986604
EasyAim

What is the syntax for calling this function?
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Expert Comment

by:EasyAim
ID: 6986616


  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
 

Author Comment

by:DLockwood
ID: 6986662
EasyAim,

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

Any ideas?
0
 
LVL 26

Expert Comment

by:EDDYKT
ID: 6986731
RS.Open(strSQL, dba, adOpenKeyset, adLockOptimistic)


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


0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 6986737
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
 
LVL 5

Expert Comment

by:raizon
ID: 6986764
Good call acperkins,

I completly overlooked that.
0
 

Author Comment

by:DLockwood
ID: 6986862
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
 
LVL 5

Expert Comment

by:raizon
ID: 6986872
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 6986925
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
 
LVL 18

Expert Comment

by:bobbit31
ID: 6986945
> 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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 6986958
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 6986976
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 6986993
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
 
LVL 5

Expert Comment

by:rkot2000
ID: 6988700
>>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
 
LVL 5

Expert Comment

by:rkot2000
ID: 6988732
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
 

Author Comment

by:DLockwood
ID: 6989195
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
 
LVL 49

Accepted Solution

by:
DanRollins earned 75 total points
ID: 7698788
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
 

Expert Comment

by:DigitalXtreme
ID: 7706859
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

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now