Run Time Error '9': Subscript Out of Range, Ole Object Question

[Renee]

Good Morning all,

I am trying to apply a solution posted on 7/21/05 by SA (titled: Report
with Graph Ole, can I manipul...). Here is the modified code:

Private Sub Report_Activate()
Dim objGraph As Object, objDS As Object, rsData As Recordset
Dim intRowMax%, intColMax%, arrData As Variant
Dim i%, j%

Set objGraph = Me!OLEUnbound0.Object
'get the datasheet object
Set objDS = objGraph.Application.DataSheet
'open the target recordset
Set rsData = CurrentDb.OpenRecordset("TRANSFORM
Avg(qry_Y_Report.Ratio_Value) " & _
"AS AvgOfRatio_Value " & _
"SELECT qry_Y_Report.[Subject], " & _
"qry_Y_Report.SumOfCost " & _
"FROM qry_Y_Report " & _
"WHERE qry_Y_Report.staffID = '" &
Me.OpenArgs & "'" & _
"GROUP BY qry_Y_Report.[Subject], " & _
"qry_Y_Report.SumOfCost " & _
"PIVOT qry_Y_Report.StaffID; ")

'load the data into an array using GetRows, this reads the data once and is
fast
arrData = rsData.GetRows(200)
intRowMax = UBound(arrData, 1)
intColMax = UBound(arrData, 2)
'data sheet cells are in row, column, starting at 1,1 with the headers like
Excel
'array is in column, row starting at 0, where row 0 is the first Row of
data; no headers
'Add the column heads using the field names of the recordset
objDS.cells.Clear
For i = 0 To rsData.Fields.Count - 1
objDS.cells(1, i + 1) = rsData.Fields(i).Name
Next i
'now add the data from the recordset
For i = 0 To intRowMax
For j = 0 To intColMax
objDS.cells(i + 2, j + 1) = arrData(j, i)
Next j
Next i
Set objDS = Nothing
DoEvents
objGraph.Refresh
'optional to save the dataset in the graph's datasheet, so you'll see it in
design mode
Me.OLEUnbound0.Object.Application.Update
Set objGraph = Nothing
End Sub

The error I am getting is:
Run Time Error '9': Subscript is out of Range
and the editor highlights the inner for loop: "objDS.cells(i + 2, j + 1) =
arrData(j, i)"

any suggestions?

Thank you much!

 

[Friedi]

I am using the same Code received from ACG Soft and have the same problem-
only with 'on error resume next ' the array fills in the colums and rows.
but only on the "On Page event" but then the following problem occurs:

The Graph doesn't display the proper data from its record source, instead it
displays either the data from a prior record or the sample data in the Graph
data
sheet.

Some Visual Basic code to the On Print event of the section of the report
that contains the Graph object.

Do you know What is the 'ON PRINT EVENT' is in Access 2002 ?
Perhaps we could join our efforts to get a solution of our identical problem.

Friedi

 

[Renee]

I changed "objDS.cells(i + 2, j + 1) = > arrData(j, i)"
to "objDS.cells(i + 2, j + 1) = > arrData(i, j)"
and the runtime error 9 stopped, but it isn't filtering...

 

[Friedi]

No, that's not a solution - cells start with row in a first position which is
your 'i' and with colums in the second position, which is your 'j'.
Data arrays just work the other way around and start colums in the 1st
position. The change you made is in conflict with this principle. So use"on
error resume next"

For printing, you must use event: Onprint in the Detail Section of your
report.
--
Friedi
HDC - MS Access Development

I changed "objDS.cells(i + 2, j + 1) = > arrData(j, i)"
to "objDS.cells(i + 2, j + 1) = > arrData(i, j)"
and the runtime error 9 stopped, but it isn't filtering...

Good Morning all,

I am trying to apply a solution posted on 7/21/05 by SA (titled: Report
with Graph Ole, can I manipul...). Here is the modified code:

Private Sub Report_Activate()
Dim objGraph As Object, objDS As Object, rsData As Recordset
Dim intRowMax%, intColMax%, arrData As Variant
Dim i%, j%

Set objGraph = Me!OLEUnbound0.Object
'get the datasheet object
Set objDS = objGraph.Application.DataSheet
'open the target recordset
Set rsData = CurrentDb.OpenRecordset("TRANSFORM
Avg(qry_Y_Report.Ratio_Value) " & _
"AS AvgOfRatio_Value " & _
"SELECT qry_Y_Report.[Subject], " & _
"qry_Y_Report.SumOfCost " & _
"FROM qry_Y_Report " & _
"WHERE qry_Y_Report.staffID = '" &
Me.OpenArgs & "'" & _
"GROUP BY qry_Y_Report.[Subject], " & _
"qry_Y_Report.SumOfCost " & _
"PIVOT qry_Y_Report.StaffID; ")

'load the data into an array using GetRows, this reads the data once and is
fast
arrData = rsData.GetRows(200)
intRowMax = UBound(arrData, 1)
intColMax = UBound(arrData, 2)
'data sheet cells are in row, column, starting at 1,1 with the headers like
Excel
'array is in column, row starting at 0, where row 0 is the first Row of
data; no headers
'Add the column heads using the field names of the recordset
objDS.cells.Clear
For i = 0 To rsData.Fields.Count - 1
objDS.cells(1, i + 1) = rsData.Fields(i).Name
Next i
'now add the data from the recordset
For i = 0 To intRowMax
For j = 0 To intColMax
objDS.cells(i + 2, j + 1) = arrData(j, i)
Next j
Next i
Set objDS = Nothing
DoEvents
objGraph.Refresh
'optional to save the dataset in the graph's datasheet, so you'll see it in
design mode
Me.OLEUnbound0.Object.Application.Update
Set objGraph = Nothing
End Sub

The error I am getting is:
Run Time Error '9': Subscript is out of Range
and the editor highlights the inner for loop: "objDS.cells(i + 2, j + 1) =
arrData(j, i)"

any suggestions?

Thank you much!