2 dymensional AAAAAHHHHH!!!!

[javajom]

Hi I,m having trouble finding information/tutorials about dynamic arrays, the problem is that I'm trying to use a 2 dimensional array and having trouble getting it to work. Before I used 2 arrays, which worked well but now I'm trying to use a 2 dimensional one. It seems that the line of code:
ReDim Preserve data5(UBound(data5) + 1) doesn't work as it's for a single array, so how do I get it to add a element to the expanding array?

Dim data5() As String
Dim data4() As String
Dim data3() As String
Dim data2() As String
Dim data1() 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 data5(0 To i, 0 To 1)
ReDim data4(0 To i, 0 To 1)
ReDim data3(0 To i, 0 To 1)
ReDim data2(0 To i, 0 To 1)
ReDim data1(0 To i, 0 To 1)


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

End If

 

[doco]

You say it doesnt work; in what way? Also, you are creating arrays that expand the data horizontally essentially placing one row for each column. Is this really what you want?

ReDim Preserve data5(UBound(data5) + 1)

This is only expanding a single dimension and does not preserve the lower bound with it.

 

[javajom]

yes I need to expand all elements but just being a humbly beginner I'm not sure how to expand a 2 dimensional array every time the function finds a record set that equals 5. Whats the best way?

 

[georgedwilkinson]

ReDim Preserve data5(UBound(data5) + 1, 1)

You must tell redim about both dimensions.

 

[javajom]

Tried but it says run time error '9' 'subscript out of range' can't understand as ReDim data5(0 To i, 0 To 1) states that there are 0 to i rows with 0 and 1 (2) columns. Confused.com

 

[georgedwilkinson]

My bad. You can only change the last "dimension" of a multi-dimensional array with "preserve".

Try:

myRecordSet.Open mySQL
ReDim data5(0 To 1, 0 To i)
'snip


While myRecordSet.EOF = False
If myRecordSet.Fields(2).Value = 5 Then
data5(0, i) = (myRecordSet.Fields(1).Value)
data5(1, i) = (myRecordSet.Fields(0).Value)
i = i + 1
ReDim Preserve data5(1, UBound(data5, 2) + 1) 'Why not use "i" here? 
'ReDim Preserve data5(1, i) 'Should do the same thing

End If

 

[doco]

ReDim Preserve data5(1, UBound(data5, 2) + 1) 'Why not use "i" here?

I wondered the same thing.

 

[ChrisO]

In a standard module: -

Private Type Parent
    Element1 As String
    Element2 As String
End Type

Public Type Child
    Data() As Parent
End Type

Behind a Form: -

Private Sub cmdTestIt_Click()
    Dim MyData As Child
    Dim lngX   As Long
    
    For lngX = 0 To 5
        ReDim Preserve MyData.Data(lngX)
        MyData.Data(lngX).Element1 = CStr(lngX)
        MyData.Data(lngX).Element2 = CStr(lngX ^ 2)
    Next lngX

    For lngX = 0 To 5
        Debug.Print MyData.Data(lngX).Element1 & "   " & MyData.Data(lngX).Element2
    Next lngX

End Sub

Hope that makes some sense.

Regards,
Chris.

 

[javajom]

tried and failed

Hi,
Tried but still deluding me, heres my code I've done:

Public Function protect()

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

Dim myRecordSet As New ADODB.Recordset
myRecordSet.ActiveConnection = cnn1
Dim data5() As String
Dim data4() As String
Dim data3() As String
Dim data2() As String
Dim data1() As String



Dim pWord As String
Dim hello As String
Dim user As String
Dim num As Integer
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim l As Integer
Dim m As Integer

Dim count1 As Integer
Dim count2 As Integer
Dim count3 As Integer
Dim count4 As Integer
Dim count5 As Integer

Dim ans As Boolean

ans = False
Dim intCount

count1 = 0
count2 = 0
count3 = 0
count4 = 0
count5 = 0
pWord = ""
hello = ""
user = ""
num = 0
i = 0
j = 0
k = 0
l = 0
m = 0

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

myRecordSet.Open mySQL
ReDim data5(i, 0 To 1)
ReDim data4(j, 0 To 1)
ReDim data3(k, 0 To 1)
ReDim data2(l, 0 To 1)
ReDim data1(m, 0 To 1)



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

The line ReDim Preserve data5(1, i) still occurs the subscript out of range run time error. Starting to pull hair out now.

 

[doco]

You need to redimension your array prior to initializing it with new data not after...

 

[javajom]

you mean something like:

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

instead of:

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

 

[doco]

You need to 'expand' (ie redim) your array to accept the new data while 'preserving' the existing array. Then append the new data...

 

[javajom]

Plese could you show me what you mean as I'm a beginner.

 

[javajom]

Thanks,
Please could you show me what you mean as I'm going around in circles with this one, I've just started with VB espec ReDims.

 

[ChrisO]

Did you even try the code I gave you?

If ‘No’ then why not?
If ‘Yes’ then show me the code you tried.

 

[georgedwilkinson]

Tried but still deluding me, heres my code I've done:

...snip...
While myRecordSet.EOF = False
If myRecordSet.Fields(2).Value = 5 Then
data5(i, 0) = (myRecordSet.Fields(1).Value)
data5(i, 1) = (myRecordSet.Fields(0).Value)
i = i + 1
ReDim Preserve data5(1, i)
End If
Wend

The line ReDim Preserve data5(1, i) still occurs the subscript out of range run time error. Starting to pull hair out now.

Don't pull your hair out. It will fall out by itself by the time you're my age.

I'm assuming this is happening on the third time through the while loop that you get a "hit". That is because the variable "i" is now "2" which is larger than the first dimension of the array. Your problem is that you are putting data in a place that does not exist.

Your code:

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

Should be:

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

Please, before you post back, put a break on "data5(0, i) = (myRecordSet.Fields(1).Value)" and a watch on "i". Single step (with F8) through your code until you get an error. When you post back, provide us with the value of "i" when the error occurred along with the code for the loop when you got the error.

 

[doco]

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

Shouldn't that code be

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

In the first snip above the array is not being expanded to allow his additional data PRIOR TO the addition of data.