collect method of recordset failed

[rexhuston]

hi,
I am trying to use the atpvbaen function of excell to make
some calcultions. Everything is going ok until I hit tis
line:
rec![YTM] = XLapp.Application.Run("ATPVBAEN.XLA!Yield",
report_date_string, end_date, rate, pr, 100, 2, 0)

I get the error Method Collect of Object Recordset failed.
I double checked the parameters in this function to make
sure they are the right type. Anybody have any ideas on
what kind of problems cause this error? My guess is it
would be a reference error, but I cant figure it out.

Thanks,
rex

 

[Douglas J. Steele]

See whether assigning the value of the function call to a variable works,
and then pass that variable to the recordset field.

 

[rexhuston]

I created a var of type double and set the yield = to
that. I now get a mismatch type error. The yield should be
a double so I dont know whats causing that. This mismatch
is probably causing the other error though. Thanks for
your help.

Rex

-----Original Message-----
See whether assigning the value of the function call to a variable works,
and then pass that variable to the recordset field.

--
Doug Steele, Microsoft Access MVP

hi,
I am trying to use the atpvbaen function of excell to make
some calcultions. Everything is going ok until I hit tis
line:
rec![YTM] = XLapp.Application.Run("ATPVBAEN.XLA!Yield",
report_date_string, end_date, rate, pr, 100, 2, 0)

I get the error Method Collect of Object Recordset failed.
I double checked the parameters in this function to make
sure they are the right type. Anybody have any ideas on
what kind of problems cause this error? My guess is it
would be a reference error, but I cant figure it out.

Thanks,
rex

.

 

[CBauman]

#N/A Error in cell was causing Method 'Collect' of object 'Recordset' failed

I was getting the same error. I believe what was causing my error was a VLOOKUP resulting in a #N/A error value in a cell I was referencing and then trying to assign a recordset. I fixed the VLOOKUP issue so I had valid data in the cell and it resolved the issue. My code is below for the recordset operation I am using.

Set oRstClosed = CurrentDb.OpenRecordset("HSBC_Periodic_Fee_Comp_Closed", dbOpenDynaset)
With oRstClosed
.AddNew
!AccountBaseCurrency = oSheet.Cells(intRow, 3).Value
!PercentageOfYear = oSheet.Cells(intRow, 11).Value
!FeeFromDate = oSheet.Cells(intRow, 8).Value
!FeeToDate = oSheet.Cells(intRow, 7).Value
!FeeAmount = oSheet.Cells(intRow, 10).Value
!BasisAmount = oSheet.Cells(intRow, 4).Value
!FeeRate = oSheet.Cells(intRow, 5).Value
!AccountNumber = oSheet.Cells(intRow, 1).Value
!AccountCreateDate = oSheet.Cells(intRow, 6).Value
!InvestmentStrategyName = oSheet.Cells(intRow, 2).Value
!AccountSolutionName = oSheet.Cells(intRow, 14).Value
!BookingCenterID = oSheet.Cells(intRow, 13).Value
' !BasisAmount = oSheet.Cells(intRow, 9).Value
.Update
End With
oRstSearch.Close
oRstClosed.Close
Set oRstSearch = Nothing
Set oRstClosed = Nothing