I need to make this run faster... (1 Viewer)

Hello everyone. I have here a function I wrote to forecast sales. Basically, I’m trying to adapt Excel's 'Forecast' function for use in Access 97. The code below works, but is rather slow. I'm hoping someone can help me improve it. The function pulls from a table, tblMonthlySalesAll, that contains 3 fields, Item Number [Item], Month [YYYY-MM], and sales [Quantity]. There is one record per item per month.

Function MyForecast(Point As Integer, Item As String) As Double
On Error GoTo MyForecast_Error
Dim I As Integer, rstA As Recordset

‘ Open recordset containing the monthly sales for Item
Set rstA = CurrentDb.OpenRecordset("SELECT tblMonthlySalesAll.Quantity FROM tblMonthlySalesAll WHERE (((tblMonthlySalesAll.[Item]) = '" & Item & "')) GROUP BY tblMonthlySalesAll.Quantity, tblMonthlySalesAll.[YYYY-MM] ORDER BY tblMonthlySalesAll.[YYYY-MM];")
‘ Declare size of array
ReDim kY(rstA.RecordCount) As Variant
ReDim kX(rstA.RecordCount) As Variant
‘ Populate the array
For I = 1 To rstA.RecordCount
kY(I) = rstA![Quantity]
kX(I) = I
If rstA.EOF = False Then rstA.MoveNext
Next
‘ Do forecast
MyForecast = Fix(Excel.WorksheetFunction.Forecast(Point, kY, kX) * 100) / 100
If MyForecast < 0 Then MyForecast = 0

‘error handling
MyForecast_Exit:
rstA.Close
Exit Function

MyForecast_Error:
Resume MyForecast_Exit
End Function

I believe the bottleneck is in the For… Next loop. If so, does anyone know a better method for creating an array from a recordset? Or, am I barking up the wrong tree? Any help, hints, comments, or questions are welcome. Thank you for your time and consideration.

~Abby N


[This message has been edited by Abby N (edited 05-16-2001).]