Call function with adodb.connection Problem

[ina]

Hello guys,

I have a problem with a function that call another function

So, for exampple I have this function, info that gives me all
information about client , and this function call another function
calling getclientnumber (adodb.connection, string).

the problem is that when I call this function I have a Loop result all
the time calling the same code. I do not know why might a problem with
the adodb.connection


Public Function Info(ByVal cndb As ADODB.Connection)
On Error GoTo Info_Err

'Variables description
'======================

Dim dtmStart As Date
Dim r As Integer, i As Integer
Dim size As Integer
Dim varInfo(1 To 1000, 1 To 68) As Variant
Dim strCode As String, strCurrency As String, Name as String
Dim rsCode As ADODB.Recordset
Dim vartblFee(1 To 4, 1 To 4) As Variant

Set rsCode = New ADODB.Recordset


rsCode.ActiveConnection = cndb
rstCode.Open "select * from clientcode"


r = 2

Do While Not rsCode.EOF

strCode = rsCode.Fields(1).Value
rsCode.MoveNext

strClientNumber = GetClientNumber(cndb, strtCode)
Name = rsCode.Fields(2).Value


Cells(r, 1) = strtCode
Cells(r, 2) = Name
Cells(r, 3) = ""
Cells(r, 4) = ""
Cells(r, 5) = ""
Cells(r, 6) = ""
Cells(r, 7) = strClientNumber

r = r + 1

Loop


rsCode.Close
Set rsCode = Nothing

cndb.Close
Set cndb = Nothing

Info_Err:
'ActiveCell.Value = CVErr(xlErrNA)

End Function



'Function GetClientNumber

Public Function GetClientNumber(ByVal cndb As ADODB.Connection, strCode
As String) As String
On Error GoTo GetClientNumber_Err

Dim strSQL As String
Dim rsClient As ADODB.Recordset
Dim strClientNumber As String, strClientCall As String


Set rsClient = New ADODB.Recordset


strSQL = "select clientnumber from Clientview where code = '" & strCode
& " '"


rsClient.ActiveConnection = cndb
rsClient.Open strSQL
strClient = rs.Fields(0).Value

strClientCall = GetClientNumber(cndb, strClient) 'problem here

rsClient.Close
cndb.Close

Set cndb = Nothing
Set rsClient = Nothing

Getcurrrency_Err:
GetClientNumber = CVErr(xlErrNA)
Exit Function

End Function

Any help is appreciated thank you

Ina

 

[Guest]

I don't understand the purpose of this section of your code:
strClientCall = GetClientNumber(cndb, strClient) ' where your problem is

rsClient.Close
cndb.Close

Set cndb = Nothing
Set rsClient = Nothing

Specific questions:
1) You already queried your database for the client by code number - now you
are requerying using the resulting Field(0).Value. Why? Doesn't seem to
make sense - seems redundant. And it may be the problem, since you have a
recursive function call here - the function calls itself. There seems no way
of stopping it from continually calling itself - that is, your first query
returns a value for client number. Then you call the function again using
the result - and the function calls itself again, using the new result - ...
this will continue indefinitely unless it errors out somewhere.
2) You close cndb within the function. This means you would lose the
connection before your main sub continues execution, so even if successful
the function call will cause you problems (also note that I believe you have
a typo in the code you sent: in the main sub you have rsCode.ActiveConnection
= cndb (should be rstCode)
3) The only statement you have that sets GetClientNumber's return value is
in the error handler, and returns #NA
4) You have no Exit Function above your error handler; instead you have it
immediately before End Function (which would make it redundant).

Unless you intend the function to call itself recursively, I think you could
rewrite it like this:

Public Function GetClientNumber(ByVal cndb As ADODB.Connection, strCode
As String) As String
On Error GoTo GetClientNumber_Err

Dim strSQL As String
Dim rsClient As ADODB.Recordset
Dim strClientNumber As String, strClient as String, strClientCall As String

Set rsClient = New ADODB.Recordset
strSQL = "select clientnumber from Clientview where code = '" & strCode
& " '"
rsClient.ActiveConnection = cndb
rsClient.Open strSQL
GetClientNumber= rs.Fields(0).Value ' Field 0 will contain clientnumber
here!

rsClient.Close ' close the recordset (since it is only used in the function)
' but don't close the connection (it is needed in the main sub)

Set rsClient = Nothing
Exit Function

Getcurrrency_Err:
GetClientNumber = CVErr(xlErrNA)

End Function

 

[ina]

thank you a lot much more clear for me, but I can not get the
codeclient for this function how can I pass this value to the other
function ( I mean function Info). Sorry for my ignorance

Ina

 

[ina]

Sorry Dales, : D

I got it, a little tired right now, THANK YOU A LOT Really