Array Subscript out of Range

[pcdaveh]

I'm reading a table and steping through each row. I have declared two dynamic arrays. While trying to populate the arrays I'm getting Subscript out of Range. Please help.

Option Compare Database
Option Explicit

Sub FindTiers()
Dim db As Database
Dim rst As Recordset
Dim lsEntity() As String
Dim lsOwners() As String
Dim intI As Integer
Dim intRecords As Integer

Set db = CurrentDb()
Set rst = db.OpenRecordset("tbl_TranslationIncomeApplication&quot
intRecords = rst.RecordCount
For intI = 0 To intRecords
lsEntity(intI) = rst![ProjectPrefixOwned] 'Error here
lsOwners(intI) = rst![ProjectPrefixOwner] 'Error here
rst.MoveNext

Next intI
End Sub

 

[jebry]

Hi!

You need to dimension your arrays. After the line that says intRecords = rst.RecordCount you need to put:

Redim IsEntity(0 to intRecords)
Redim IsOwners(0 to intRecords)

That should take care of the problem.

 

[pcdaveh]

Thanks!

 

[Kalin]

Use Redim Preserve to preserve the already defined array values..... Grtz,

Kalin

 

[MichaelRed]

From the post, you are using DAO. The RecordCount property of a DAO recordset is not reliable in your situation, unless you are SURE that the recordset has been traversed. You should add:

rst.MoveLast
rst.MoveFirst beteween the recordset instantiation and the use of the .RecordCount property.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[MichaelRed]

On a seperate theme, the procedure -as posted- is nearly useless, as the arrays are declared within the procedure and thus go out of scope (e.g. are 'lost') when it terminiates. Further, other than an exercise in coding a VB procedure, it would appear to be somewhat awkward even if the results were saved, as the results set culd easily be achieved with a simple select query. This has all of the (BAD) flavor of an arbitrary (HOMEWORK) assignment.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[genomon]

Also, you must invoke .MoveLast before RecordCount stores the correct value; set upper array bound to RecordCount - 1 as it is base zero.