Any Help with my code;

[neoklis]

Hi,

I 've converted the code to ADO method. It was working fine in DAO but know I get the following error and i can't figure it out the soloution.

Run-time error ‘3265’

Item cannot be found in the collection corresponding to the requested name or ordinal

Function ChangeQuantUnit(Material, quant_a, unit_a, unit_b)

Static M As New ADODB.Recordset
Static Mat_SQL As String

ChangeQuantUnit = 0

Mat_SQL = "SELECT SAP_Materials.Material, SAP_Materials.K, SAP_Materials.Base_K, SAP_Materials.KG, SAP_Materials.Base_KG, SAP_Materials.MTR, SAP_Materials.Base_MTR, SAP_Materials.ST, SAP_Materials.Base_ST FROM SAP_Materials WHERE (((SAP_Materials.Material)=""" + Material + """));"
M.Open (Mat_SQL), CurrentProject.Connection, adOpenKeyset, adLockOptimistic

If unit_a = "LB" Then
ChangeQuantUnit = (quant_a / 2.2)
Else
If M(unit_a) * M("BASE_" + unit_b) > 0 Then 'this is the line that crashes
ChangeQuantUnit = quant_a * M(unit_b) * M("BASE_" + unit_a) / (M(unit_a) * M("BASE_" + unit_b))
End If
End If

M.Close

End Function

 

[The_Doc_Man]

Mode mixing is possibly a culprit here.

Function ChangeQuantUnit(Material, quant_a, unit_a, unit_b)

Declares UNIT_A and UNIT_B to VARIANT by default (if I remember correctly).

M(unit_a)

Is ambiguous. If unit_a is input as a number, this says "Take field #unit_a from the recordset." But if unit_a is a string (other than "LB"), there has to be a field with the same name as the contents of unit_a.

M("BASE_" + unit_b)

Is not ambiguous, but if unit_b is NOT a string, the named field will contain an impossible character that is the equivalent of CHR$(unit_b).

So I guess my first thought is to tightly track your inputs for unit_a and unit_b to assure they are what you intented.