Working with multidimensional, dynamic arrays

[ziwacky]

I have a recordset with two fields. I want to tranfer those fields into an array. I've been learning and working VBA for a few months and being avoiding the array stuff, but right now I need and don't know how to do it.<br><br>This is the code I have so far:<br><br>i = 0<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;ReDim ValueArray(i, 1)<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;Do While Not gv_rs.EOF<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ReDim Preserve ValueArray(i, 1)<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ValueArray(i, 0) = recorset!ID<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ValueArray(i, 1) = recordset!VALUE<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;i = i + 1<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;gv_rs.MoveNext<br>&nbsp;&nbsp;&nbsp;&nbsp;Loop<br><br>Instead of asking what is wrong I'm going to ask what is right. Please, don't tell me NOTHING! What's the right approach for this.<br><br>Thanx in advance.<br><br>Ziwacky

 

[aklein]

<b>movelast</b> to find out how many records there are, then dimension the array to that size initially, and don't redimension it inside the loop.<br><br>Never do in a loop what you can do outside the loop - it slows things down.<br><br>Other than that, it seems to do what I think you want.

 

[ziwacky]

Thanx aklein,<br><br>I figured your solution a little while after posting the msg, but it's good to have a confirmation of someone else.<br><br>Now I need to retrieve the information. Is this the right way of doing it?<br><br>For i = 0 To gv_rowint<br>&nbsp;&nbsp;&nbsp;&nbsp;sql = Insert Into table (fld1, fld2) <br>&nbsp;&nbsp;&nbsp;&nbsp;sql = sql & &quot;Values (&quot; & gv_ValueArray(i, 0) <br>&nbsp;&nbsp;&nbsp;&nbsp;sql = sql & &quot;, &quot; & gv_ValueArray(i, 1) & &quot&quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;db.execute sql<br>Next i<br><br>Thank you<br><br>Ziwacky