On 27 Feb 2005 17:23:18 -0800, "eliffman" <el******@gmail.com> wrote:
I'm trying to populate an array with the records from a recordset. But
I'm getting an error. Here's the code:
Dim db As Database, qd As QueryDef, rs As Recordset
Dim MyArray() As Double, i As Integer
Set db = CurrentDb
Set qd = db.QueryDefs("MyQuery")
Set rs = qd.OpenRecordset
rs.MoveFirst
i = 0
For i = 0 To rs.RecordCount - 1
MyArray(i) = rs![PaymentI] 'Error occurs here
rs.MoveNext
i = i + 1
Next i
Any help would be greatly appreciated.
The cause of your problem is that you haven't specified the dimensions of your
array before trying to use it. You have a second problem in that you are
using rs.RecordCount incorrectly. .RecordCount is only guaranteed to return
the number of records read from the recordset so far, not the total number of
records in the recordset. Finally, it's redundant to initialize a variable
prior to using it in a For loop, though it won't hurt anything.
I'm going to suggest you use a collection instead of an array, but first I'll
show you a way to fix the code using the array...
=====
Dim dbs As DAO.Database, DAO.qdf As QueryDef, DAO.rst As Recordset
Dim MyArray() As Double
Dim lngIndex As Long
Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("MyQuery")
Set rst = qdf.OpenRecordset
If rst.RecordCount > 0 Then
' RecordCount always greater than zero if recordset has records,
' zero otherwise.
Do Until rst.EOF
' Expand array to make room for new element, and keep existing data.
Redim Preserve MyArray(0 To lngIndex)
MyArray(lngIndex) = rst![PaymentI]
rst.MoveNext
lngIndex = lngIndex + 1
Loop
End If
rst.Close
Set rst = Nothing
Set qdf = Nothing
Set dbs = Nothing
=====
In cases like this, I generally find it makes more sense to use a collection
than an array, though.
=====
Dim dbs As DAO.Database, DAO.qdf As QueryDef, DAO.rst As Recordset
Dim colPayments As New VBA.Collection
Dim lngIndex As Long
Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("MyQuery")
Set rst = qdf.OpenRecordset
If rst.RecordCount > 0 Then
' RecordCount always greater than zero if recordset has records,
' zero otherwise.
Do Until rst.EOF
' If you don't specify .Value, the field object gets added instead
' of the value.
colPayments.Add rst![PaymentI].Value ' <- .Value is important here!
rst.MoveNext
lngIndex = lngIndex + 1
Loop
End If
rst.Close
Set rst = Nothing
Set qdf = Nothing
Set dbs = Nothing
=====
