Trouble reading all the values from recordset into array 1

[BusMgr]

I am trying to set up an array so that I can find the max value in a table. The table is set up with field state, then months. I can read the first two months, (Jan, Feb), but when I try to read the rest I get an error in reading the rest.

I want to dynamically plot the monthly sales by state. I can do that once I find the max value. The min will be zero.

Thanks for your help.

Code as follows.


Public Function maxvalue() As Single
Dim rst As ADODB.Recordset
Dim vardata As Variant
Dim intCtr As Integer, intI As Integer

Dim intLargest As Integer

Set rst = New ADODB.Recordset

Set rst.ActiveConnection = CurrentProject.Connection
rst.CursorType = adOpenForwardOnly
rst.LockType = adLockReadOnly
rst.Open "MonthlySales"

vardata = rst.GetRows( _
Fields:=Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"))


rst.Close
Set rst = Nothing

'Number of Rows
intCtr = UBound(vardata, 11) + 1 (this is where the problem arises, if I set the second value to greater then 2)

'Loop through all the rows, printing out the data
For intI = intCtr - 1 To 0 Step -1
Debug.Print vardata(0, intI), , vardata(1, intI)
Next intI

End Function

Thanks for your help in advance.

 

[PHV]

The number of rows:
either UBound(vardata, 2) + 1
or rst.RecordCount (before rst.Close)

For intI = UBound(vardata, 2) To 0 Step -1
Debug.Print vardata(0, intI), vardata(1, intI), ..., vardata(11, intI)
Next intI

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[BusMgr]

Not there yet.

I should end up with an array the is 'vardata' records by 12 columns.

If I try to print out that array, I get an error message at the

intCtr = UBound(vardata, 11) + 1

line that states "Subscript out of range". It shows intCtr = 0. I tried to put a line in with
i=rst.recordcount to test for values, but I get i = -1.

Thanks for your help.

 

[PHV]

Have you just tried the For .. Next code I suggested you ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[BusMgr]

Thanks PH

I went back and read the Ubound and GetRows help section. My print loop was wrong, you were correct.

Thanks

Karl
aka BusMgr