473,324 Members | 2,178 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

"Subscript Out of Range" When Setting Array Elements

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
5 19944
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
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

13
by: Dan R Brown | last post by:
I have a large form that is generated dynamically in a jsp using xml / xslt. So, to break up this form into several "tabbed" sections, I break up the form using <div> tags. Each <div...
13
by: Mike Austin | last post by:
Hi all. Just working on a small virtual machine, and thought about using vector iterators instead of pointer arithmetic. Question is, why does an iterator plus any number out of range not...
8
by: sam | last post by:
hey everybody, this is my first time posting here. i'm pretty new to python and programming in general (as you'll soon work out for yourselves...) i'm trying to code a version of a selection...
5
by: ubg001 | last post by:
Hey all, I am using Access 2007 and tried to import an excel file by using the Import Wizard. I completed all steps and received an error when I clicked "Finish"... the error came back as...
4
by: Han | last post by:
when I exe my project in vs.net2005,I got the error following: Debug Assertion Failed! Program:........ File:c:\program files\microsoft visual studio 8\vc\include\vector Line:756 ...
17
by: David C. Ullrich | last post by:
Having a hard time phrasing this in the form of a question... The other day I saw a thread where someone asked about overrideable properties and nobody offered the advice that properties are...
3
by: Lax | last post by:
Isn't it "technically" meaningless to call C a "row major language," since there are no such things as multidimensional arrays in C. In C you can define arrays of arrays, and the way that the...
13
by: kj | last post by:
Is there a special pythonic idiom for iterating over a list (or tuple) two elements at a time? I mean, other than for i in range(0, len(a), 2): frobnicate(a, a) ?
7
by: tallman | last post by:
Hi all, I am using the mlabwrap module in python for my thesis and I have encountered an error : some times when I call my matlab function from my python code it stops and shows this error "...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.