Subscript Error -Dynamic Array

[Craig]

I have the following code (below)...i have been trying to
use a dynamic array. However, it gets down to that
first 'if' statement and then at the line 'TheArray(n) =
tbl1(0)' it gives an error that says 'Subscript out of
range'

Can anyone tell me what i'm doing wrong? Thank you for
any help!!!

Craig

Dim TheArray() As String
Dim n As Integer
Dim tbl1 As DAO.Recordset
Dim db As DAO.Database

n = 0 'initialize value of n to 0

Set db = CurrentDb

Set tbl1 = db.OpenRecordset("Criags List", dbOpenDynaset)

If IsNull(tbl1(0)) Then
tbl1.MoveNext
Else
TheArray(n) = tbl1(0)
n = (n + 1)
TheArray(n) = tbl1(2)
End If

 

[LarryP]

Well, for openers, you set up what's intended to be a
dynamic array, but you never allocated any memory to it;
all it has is a name, but no element(s). If I recall all
that "book-larnin'" correctly, you have to create (Redim)
at least one "pigeonhole" before you can store anything in
the array. Other parts of your code may elicit further
comment from the wise denizens of this newsgroup, but
while you await those, you might look into the Redim thing.

 

[Craig]

Larry...

thank you for the reply...but I don't understand what
a "pigeonhole" is...could you give me an example?

 

[Guest]

You do need to use Redim to allocate your contiguous block of memory that is your array (of data, pointers, whatever).

Dim strMyString() As String

....

Redim strMyString(0 to 50)

David

 

[John Spencer (MVP)]

You need to redim the array and you need to keep the values you've entered.

Dim TheArray() As String
Dim n As Integer
Dim tbl1 As DAO.Recordset
Dim db As DAO.Database

n = 0 'initialize value of n to 0

Set db = CurrentDb

Set tbl1 = db.OpenRecordset("Criags List", dbOpenDynaset)

If IsNull(tbl1(0)) Then
tbl1.MoveNext
Else
Redim Preserve TheArray(N + 2)
TheArray(n) = tbl1(0)
TheArray(n+1) = tbl1(2)
n = (n + 2)
End If