Dynamic Array Declaration

[ajetrumpet]

Can someone tell me if there is a declaration error of the dynamic array in the following code please:

Option Compare Database
Option Explicit

Function t()

Dim db As Database
Dim rs As Recordset
Dim myarray() As String, i As Integer
  Set db = CurrentDb
  Set rs = db.OpenRecordset("tableTEST", dbOpenDynaset)

i = 0

With rs
  .MoveFirst

While Not .EOF
  myarray(i) = !Abc
    Debug.Print myarray(i)
      .MoveNext
Wend
  .Close
End With

Set rs = Nothing
Set db = Nothing

End Function

I am getting a subscript out of range error on the "= !Abc" LINE OF CODE. thanks for any help!

 

[boblarson]

Does your table actually have a field named Abc?

 

[ajetrumpet]

Does your table actually have a field named Abc?

Yes, and it is a text field...

I also have all of the object libraries that I think I need (i.e. - Access 11.0, Office 11.0, etc...)

 

[Banana]

You have to redimension the array before you can use it.

Example:

ReDim MyArray(rs.Recordcount

 

[ajetrumpet]

Banana,

Are you sure? The help menu does not specify this, although I WILL try it. I'm sure it will work if you say so. I just wonder: what is the meaning of even declaring the array to begin with if you have to reDIM it after you have your spec? One just might as well DIM it initially after the recordset is created, no?

 

[Banana]

Pretty sure you have to define the upper limit for all dynamic arrays. Don't ever recall creating a dynamic arrays without the ReDim statement; would love to be proven wrong.

Dim statement will not work, even if it appears later in the procedures, because all local variables are initialized as soon as the procedure is called, which is why we have ReDim statement and an option to preserve the content with "Redim Preserve".

It works OK for small dataset but if you had quite large dataset or more importantly, needed to modify different elements quickly, collection may make more sense. The only problem is that collection hold everything as a variant (which is great for holding a variety of datatype, but not so great if you know it's all same datatype, and especially not if it's not object variables).

 

[chergh]

Banana is right, if you have a dynamic array you need to use redim.

You can't use the recordset count, or any other variable, when declaring an array using the dim statement as it requires a constant.

 

[gemma-the-husky]

having a redim array must affect the way the compiler stores the variable

presumably the variable declaration just tokenises the variable name without allocating stack space for storage (becuase it doesnt know how much space to allocate). When you redim it it must then allocate a fixed block of memory. so until the array is redimmed, any attempt to use it is bound to produce a subscript out of range error.