Array Problems

[MikeC]

I'm attempting to use a dynamic array and I'm having two problems:

1) I can't seem to remember how to append a new row to the array.
Consequently, I seem to be populating only one row at a time as I loop
through my ADODB recordset.

2) A "Subscript out of range" error occurs when I attempt to reference an
array element by row column. However, I *can* successfully reference the
array by column 0 or 1. Perhaps, the solution to problem #1 will also solve
problem #2.

The below code is running in Access 2002 SP3. The O/S is Windows XP SP2.

Can anyone tell me what I'm doing wrong and how to fix it?

Below is the relevant code fragment:
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
Dim varPmtDetail() As Variant

With rst1

'Set cursor location to client so that provider will support
RecordCount property.
.CursorLocation = adUseClient

'Open tblPaymentDetail table to read detail records.
.Open strSQL, cnn1, adOpenForwardOnly, adLockReadOnly, adCmdText

.MoveFirst
For i = 0 To .RecordCount - 1
'Loop through recordset and append(?) each pair of values to the
dynamic array.
varPmtDetail = Array(!PmtDetailID, !PartialPaymentAmt)

curTotalPmt = curTotalPmt + !PartialPaymentAmt

'Subscript out of range error occurs on next line.
Debug.Print varPmtDetail(i, 0)
Debug.Print varPmtDetail(i, 1)

'The below debug lines work fine if I comment the above 2 debug
statements.
Debug.Print varPmtDetail(0)
Debug.Print varPmtDetail(1)
.MoveNext
Next i
End With
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

 

[Brendan Reynolds]

Here is how you would add a row at a time to the array (I've used the
Employees table in Northwind, so that I could test the result before
posting) ...

Public Sub TestArray1()

Dim varTest()
Dim rst As ADODB.Recordset
Dim lngRows As Long

Set rst = New ADODB.Recordset
With rst
.ActiveConnection = CurrentProject.Connection
.Source = "SELECT FirstName, LastName FROM Employees"
.Open
Do Until .EOF
ReDim Preserve varTest(1, lngRows + 1)
varTest(0, lngRows) = .Fields("FirstName")
varTest(1, lngRows) = .Fields("LastName")
lngRows = lngRows + 1
.MoveNext
Loop
.Close
End With
For lngRows = 0 To UBound(varTest, 2)
Debug.Print varTest(0, lngRows); " "; varTest(1, lngRows)
Next lngRows

End Sub

However, once you have the recordcount, you can dimension the array once,
and avoid the need to use Preserve, which will be much more efficient ...

Public Sub TestArray2()

Dim varTest()
Dim rst As ADODB.Recordset
Dim lngRows As Long

Set rst = New ADODB.Recordset
With rst
.ActiveConnection = CurrentProject.Connection
.CursorLocation = adUseClient
.Source = "SELECT FirstName, LastName FROM Employees"
.Open
ReDim varTest(1, .RecordCount - 1)
Do Until .EOF
varTest(0, lngRows) = .Fields("FirstName")
varTest(1, lngRows) = .Fields("LastName")
lngRows = lngRows + 1
.MoveNext
Loop
.Close
End With
For lngRows = 0 To UBound(varTest, 2)
Debug.Print varTest(0, lngRows); " "; varTest(1, lngRows)
Next lngRows

End Sub

 

[MikeC]

Excellent code. Thanks Brendan.

I can now see part of the problem I was having. The wording of the online
help ("Declaring Arrays") had lead me to think that the first element in the
below array was the row and the second array element was the column. After
reading your code, I see that I had it backwards!

I also prefer the way you are using the recordset properties. I'll adopt
this method as my new standard.

Thanks again.