Solved Empty Array (1 Viewer)

pooldead

Registered User.
Local time
Today, 02:45
Joined
Sep 4, 2019
Messages
136
So the following code doesn't throw any error, however it is creating an empty array, when there should be 4 array records. Not sure what exactly needs to change, but I'm thinking I'm missing a step to build a complete array rather than resetting the array with each iteration.
Code:
Dim vArray As Variant, i As Index, vPath As String, tblPaths As TableDef, dbs As Database
    
Set dbs = CurrentDb

Set tblPaths = dbs.TableDefs!tmpPaths
Set rs2 = dbs.OpenRecordset("tmpPaths")
With rs2
    For Each i In tblPaths.Indexes
        Do While Not rs2.EOF
            vPath = rs2.Fields("Paths")
            vArray = Array(vPath)
            rs2.MoveNext
        Loop
    Next i
End With
rs2.Close
Set rs2 = Nothing
MsgBox vArray 'For testing purposes
 

Isaac

Lifelong Learner
Local time
Today, 02:45
Joined
Mar 14, 2017
Messages
8,738
Even if you do it correctly, you will still just end up with vArray being whatever the LAST iteration of the loop made it.......

What you are doing would be similar to running the below code and wondering why lngResult would always be 10.

Code:
Sub Test()
Dim lngResult As Long, x As Long

For x = 1 To 10
    lngResult = x
Next x
MsgBox lngResult
End Sub
 

pooldead

Registered User.
Local time
Today, 02:45
Joined
Sep 4, 2019
Messages
136
@Isaac Yeah I had a feeling I am missing the piece that moves to the next position in the array. I'm new with arrays, so how would I cycle through the recordset and store each "vPath" as a new item in the array?
 

pooldead

Registered User.
Local time
Today, 02:45
Joined
Sep 4, 2019
Messages
136
@Gasman I am using the index with the recordset as opposed to the array to make sure it cycles through appropriately. It may be unnecessary code, but I wasn't sure and it didn't error 🤔
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:45
Joined
Oct 29, 2018
Messages
21,357
@Gasman I am using the index with the recordset as opposed to the array to make sure it cycles through appropriately. It may be unnecessary code, but I wasn't sure and it didn't error 🤔
Hi. The syntax should be similar to what I showed you earlier.
 

pooldead

Registered User.
Local time
Today, 02:45
Joined
Sep 4, 2019
Messages
136
I guess I'm just struggling with the incremental part. How do I define the array size appropriately? I've tried doing vArray(i) = Array(vPath), but I usually get either a type mismatch error or object is not set error.

The way you showed me earlier did work for an instance of just one record, I just haven't been successful with two or more records (yet).
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:45
Joined
Oct 29, 2018
Messages
21,357
I guess I'm just struggling with the incremental part. How do I define the array size appropriately? I've tried doing vArray(i) = Array(vPath), but I usually get either a type mismatch error or object is not set error.

The way you showed me earlier did work for an instance of just one record, I just haven't been successful with two or more records (yet).
You can assign the size of an array when you declare it, or assign the size after you declare it using the Redim statement.

For example, if you want an array with 10 rows/items, you could declare it like this.
Code:
Dim myArray(10) As String
 

pooldead

Registered User.
Local time
Today, 02:45
Joined
Sep 4, 2019
Messages
136
Would it work if I Redim'd it to the size of the recordset? Something like:

Code:
Set rs2 = dbs.OpenRecordset("tmpPaths")
    Redim vArray(rs2.recordcount)
 

Isaac

Lifelong Learner
Local time
Today, 02:45
Joined
Mar 14, 2017
Messages
8,738
It looks to me like conceptually you need 2 arrays.
An "inner" array, which would appear to need its values from the inner loop (Do While Not)
An "outer" array, which would need to get ALL cumulative values--and produce your final result.

I think on each of them you need to re-size the Array - at least I generally always include a method to re-size an array, unless the ENTIRE array can be populated in one fell swoop from a collection, or something, which isn't your case.

No?

Here is an example that might give you some ideas on having both an Outer and an Inner loop (like you do), and having an Outer and an Inner array, and progressively filling the Outer one with all the iterations of the inner ones--including all its iterations.

Not sure if this will be more confusing or less, but the point is the outer array here ends up with a # of levels that equals the sum of all the inner one's iterations. In this case technically I could have used Redim Preserve ArrayName(1 to FixedNumber), but I used (1 to Variable) because that will be more relevant to your case.

Code:
Sub Example()
Dim arrOuter() As Long, arrInner() As Long
Dim lngOuter As Long, lngInner As Long, lngTotal As Long

For lngOuter = 1 To 5
    For lngInner = 1 To 5
        ReDim Preserve arrInner(1 To lngInner)
        arrInner(lngInner) = lngInner
        lngTotal = lngTotal + 1
    Next lngInner
       
    ReDim Preserve arrOuter(1 To lngTotal)
    arrOuter(lngTotal) = lngTotal
   
Next lngOuter

MsgBox UBound(arrOuter)

End Sub
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 02:45
Joined
Oct 29, 2018
Messages
21,357
Would it work if I Redim'd it to the size of the recordset? Something like:

Code:
Set rs2 = dbs.OpenRecordset("tmpPaths")
    Redim vArray(rs2.recordcount)
Yes, you could do that.
 

Gasman

Enthusiastic Amateur
Local time
Today, 09:45
Joined
Sep 21, 2011
Messages
14,038
Would it work if I Redim'd it to the size of the recordset? Something like:

Code:
Set rs2 = dbs.OpenRecordset("tmpPaths")
    Redim vArray(rs2.recordcount)
You could just as easy Dim there as well?
 

pooldead

Registered User.
Local time
Today, 02:45
Joined
Sep 4, 2019
Messages
136
So I found a slightly simpler bit of code that worked on test, however when I ran it through the test I did "Dim vArray(4) as Variant" and did not include the "x" variable or the ReDim. Those 2 pieces I added when I switched back to my recordset to set a variable size array. Now I am getting a Type Mismatch error on the ReDim line....

Code:
Dim vArray() As Variant, results As Variant, x As Variant

Set rs2 = dbs.OpenRecordset("SELECT Paths FROM tmpPaths;")
    x = rs2.GetRows()
    ReDim Preserve vArray(x) As Variant
    While Not rs2.EOF
        vArray(i) = rs2.Fields("Paths")
        rs2.MoveNext
        i = i + 1
    Wend
'From here down is for testing
results = Join(vArray, ", ")
msgbox results
rs2.Close
Set rs2 = Nothing
 

Isaac

Lifelong Learner
Local time
Today, 02:45
Joined
Mar 14, 2017
Messages
8,738
So I found a slightly simpler bit of code that worked on test, however when I ran it through the test I did "Dim vArray(4) as Variant" and did not include the "x" variable or the ReDim. Those 2 pieces I added when I switched back to my recordset to set a variable size array. Now I am getting a Type Mismatch error on the ReDim line....

Code:
Dim vArray() As Variant, results As Variant, x As Variant

Set rs2 = dbs.OpenRecordset("SELECT Paths FROM tmpPaths;")
    x = rs2.GetRows()
    ReDim Preserve vArray(x) As Variant
    While Not rs2.EOF
        vArray(i) = rs2.Fields("Paths")
        rs2.MoveNext
        i = i + 1
    Wend
'From here down is for testing
results = Join(vArray, ", ")
msgbox results
rs2.Close
Set rs2 = Nothing
Leave off the as Variant ... see my example.
 

pooldead

Registered User.
Local time
Today, 02:45
Joined
Sep 4, 2019
Messages
136
I took out the As Variant on the ReDim line, but same error
 

pooldead

Registered User.
Local time
Today, 02:45
Joined
Sep 4, 2019
Messages
136
X is apparently empty, but at the moment it should be 4. I tried modifying the x line to be (with no luck):
Code:
X = rs2.getrows(rs2.fields("Paths"))
 

Isaac

Lifelong Learner
Local time
Today, 02:45
Joined
Mar 14, 2017
Messages
8,738
An empty value in the recordset field? Will have to account for that.

Maybe step back and describe what you want to do in non-code terms and start over..
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:45
Joined
Oct 29, 2018
Messages
21,357
Now I am getting a Type Mismatch error on the ReDim line....
Hi. I think you're getting a "Type Mismatch" error because you're trying to use ReDim with another array (x). When you use GetRows(), you get back a two-dimensional array, I think.
 

Users who are viewing this thread

Top Bottom