Solved Building and Updating an Array of Variables from a Recordset

[pooldead]

New problem! I'm trying to build an array that stores a variable each time a loop is done in a recordset. The variable is a file path that is built based on a piece of data from the recordset. How would I make sure the code adds each new variable as the code loops, instead of creating a new array each time? My thought so far is but I know it isn't quite there yet:

'Below is just an example path
Do While Not rs.EOF
    .AddNew
        ePath = "C:\Users\users\" & rs1.fields("Mgr ID") & ".xlsx"
    .Update
    rs.Movenext
Loop

 

[theDBguy]

Hi. The code you posted adds a new record to a table, not to an array. If you were trying to populate an array in a loop, you would normally have something similar to this:

ArrayName(index)="something you want to store in the array"

Hope that helps...

 

[pooldead]

Hi. The code you posted adds a new record to a table, not to an array. If you were trying to populate an array in a loop, you would normally have something similar to this:

ArrayName(index)="something you want to store in the array"

Hope that helps...

So if I'm adding the "ePath" variable to the array, obviously it will change each time with the Mgr ID. Would I do ArrayName(i)=ePath to store the variable in the next position in the array?

 

[theDBguy]

So if I'm adding the "ePath" variable to the array, obviously it will change each time with the Mgr ID. Would I do ArrayName(i)=ePath to store the variable in the next position in the array?

Yes, that would be the idea. As long as you increment (i) with each pass through the loop.

 

[pooldead]

It took me a bit of debugging, but I got there. Thanks @theDBguy

 

[theDBguy]

It took me a bit of debugging, but I got there. Thanks @theDBguy

Hi. You're very welcome. We all go through the same process. Good luck with your project.