By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
458,110 Members | 1,531 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 458,110 IT Pros & Developers. It's quick & easy.

"Subscript Out of Range" When Setting Array Elements

P: n/a
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.

Nov 13 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
On 27 Feb 2005 17:23:18 -0800, "eliffman" <el******@gmail.com> wrote:

You have not yet dimensioned the array.
If you know the number of elements, write:
Dim MyArray(20) as Double
or to allocate dynamically, write:
Redim MyArray(rs.RecordCount)
and don't forget to:
Erase MyArray
at the bottom of your procedure.

And consider using Currency rather than Double.

-Tom.

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.


Nov 13 '05 #2

P: n/a
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

=====

Nov 13 '05 #3

P: n/a
Thanks so much - it worked perfectly. I didn't realize I needed to
redefine the array since it initially contains no data. The collections
method seems like a better way to go, but I am using this function to
pass an array to the IRR function.

Nov 13 '05 #4

P: n/a
rkc
Steve Jorgensen wrote:
Dim dbs As DAO.Database, DAO.qdf As QueryDef, DAO.rst As Recordset
Dim MyArray() As Double
Dim lngIndex As Long


I've always wondered why people that insist on using type
prefixes as a naming convention accept dim dbs as Database and qdf as
QueryDef, reject index as long in favour of lngIndex, but again accept
MyArray() as double instead of dblarrMyArray. I know it's just
aircode, but come on, use a prefix or don't.


Nov 13 '05 #5

P: n/a
On Mon, 28 Feb 2005 03:36:12 GMT, rkc <rk*@rochester.yabba.dabba.do.rr.bomb>
wrote:
Steve Jorgensen wrote:
Dim dbs As DAO.Database, DAO.qdf As QueryDef, DAO.rst As Recordset
Dim MyArray() As Double
Dim lngIndex As Long


I've always wondered why people that insist on using type
prefixes as a naming convention accept dim dbs as Database and qdf as
QueryDef, reject index as long in favour of lngIndex, but again accept
MyArray() as double instead of dblarrMyArray. I know it's just
aircode, but come on, use a prefix or don't.


Right - I would usually use "Dim adblMyArray() As Double".
Nov 13 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.