Fill an array from a table (1 Viewer)

Panos

New member
Local time
Today, 13:53
Joined
Feb 1, 2008
Messages
1
Hi there,

I need to fill an array with two field data from a table in access.

This is my code:

Code:
Function FillIndefArray()
   Dim dbSample As DAO.Database
   Dim rstSample As DAO.Recordset
   Dim intArrayCount As Integer
   Dim intArrayCount2 As Integer
   Dim aryTestArray() As Variant
   
   Dim intCounter As Long
   
   Set dbSample = CurrentDb()
   Set rstSample = dbSample.OpenRecordset("AllocationViewTmp")
   intArrayCount = 0
   ReDim Preserve aryTestArray(0, 0)
   
   ' Fill the array.
   With rstSample
      .MoveFirst
      Do Until rstSample.EOF
         ' Fill the array row with the last name.
         aryTestArray(intArrayCount, intArrayCount) = ![ItemOption]
         aryTestArray(intArrayCount, intArrayCount) = ![PutInQty]
         ' Increase the number of elements in the array
         ' by one to accommodate the next record.
          ReDim Preserve aryTestArray(UBound(aryTestArray) + 1, UBound(aryTestArray) + 1)         
intArrayCount = intArrayCount + 1
         .MoveNext
      Loop
   ' Remove the remaining empty array row.
   ReDim Preserve aryTestArray(UBound(aryTestArray) - 1, UBound(aryTestArray) - 1)
   .Close
   End With
   dbSample.Close
   
   ' View the array contents.
   For intCounter = 0 To intArrayCount - 1
       Debug.Print aryTestArray(intCounter, intCounter)
   Next intCounter
End Function


I get a subscript out of range error in this line: ReDim Preserve aryTestArray(UBound(aryTestArray) + 1, UBound(aryTestArray) + 1)

Any advice?

Regards.
 

ajetrumpet

Banned
Local time
Today, 15:53
Joined
Jun 22, 2007
Messages
5,638
I get a subscript out of range error in this line: ReDim Preserve aryTestArray(UBound(aryTestArray) + 1, UBound(aryTestArray) + 1)
That's probably because you are trying to resize both dimensions of the array. I don't believe you can do that when you use the Preserve word.

Just to question this procedure even more, why are you resizing the dimensions after every loop? This process can be done with a simple declaration of a dynamic array, which you already have in your declarations section. Then, you change it to a fixed array. Why? I would just leave it dynamic, and let it fill as you loop through your set.

Another thing too: It looks like you are replacing the first addition to the array with the second one. In the loop code, you are filling the element (0, 0) twice. This will probably overwrite the first value you put in there. You need to add an integer value to one of the dimensions so you can store the second field value in a separate element.
 

Users who are viewing this thread

Top Bottom