self definning dynamic array

[javajom]

Hi,
What I am trying to do is have a array which will grow as needed. I have written some code but doesn't work.

Dim cnn1 As ADODB.Connection
Set cnn1 = CurrentProject.Connection

Dim myRecordSet As New ADODB.Recordset
myRecordSet.ActiveConnection = cnn1

Dim data5(1) As String
Dim data5a(1) As String
Dim mySQL As String

mySQL = " SELECT userName, passWord, securityLevel"
mySQL = mySQL + " from secure111"
mySQL = mySQL + " WHERE(((secure111.securityLevel) >=0))"

myRecordSet.Open mySQL
While myRecordSet.EOF = False
If myRecordSet.Fields(2).Value = 5 Then
data5(i) = (myRecordSet.Fields(1).Value)
data5a(i) = ((myRecordSet.Fields(0).Value))
i = i + 1
ReDim Preserve data5(UBound(data5) + 1)
ReDim Preserve data5a(UBound(data5) + 1)
End If

Any ideas? Either tells me array is already defined or subscript out of range.

 

[doco]

try

ReDim Preserve data5(0 to UBound(data5) + 1)
ReDim Preserve data5a(0 to UBound(data5) + 1)

 

[ChrisO]

I would like to ask why you are using two parallel arrays.

Depending on what you are doing it looks like you could use a User Data Type instead.

Regards,
Chris.

 

[KernelK]

The primary problem is that your did not Dim your array as a dynamic array to start with, the code should be:

Dim cnn1 As ADODB.Connection
Set cnn1 = CurrentProject.Connection

Dim myRecordSet As New ADODB.Recordset
myRecordSet.ActiveConnection = cnn1

'You must leave the dimension blank to declare a dynamic array
Dim data5() As String
Dim data5a() As String
Dim mySQL As String

mySQL = " SELECT userName, passWord, securityLevel"
mySQL = mySQL + " from secure111"
mySQL = mySQL + " WHERE(((secure111.securityLevel) >=0))"

myRecordSet.Open mySQL

'ReDim to set your initial bound of zero so that you can use the array
ReDim data5(0)
ReDim data5a(0)

While myRecordSet.EOF = False
If myRecordSet.Fields(2).Value = 5 Then
data5(i) = (myRecordSet.Fields(1).Value)
data5a(i) = ((myRecordSet.Fields(0).Value))
i = i + 1
ReDim Preserve data5(UBound(data5) + 1)

'You originally had the UBound for data5 in the ReDim for data5a which would
'actually offset your boundary by one each loop
ReDim Preserve data5a(UBound(data5a) + 1)

End If

That should just about do it for ya.....just remember when the loop finishes that you will have an empty value for the highest dimension because you add a new one on each loop.

 

[javajom]

Many thanks.