Ok, here is the rundown.
I have created a report. This report has 16 graphs. Each of these graphs references the same query to show the data. All I want to do, is upon opening the report, change the filter for each chart so that each chart represents 1 person from the query. (The query can have 1 to 16 people depending on the selected date range.)
Here is my code that is in the Report_Open Event
Dim rs As New ADODB.Recordset
Dim name As String
Dim sRowSource As String
Dim ctrl As Access.Control
rs.Open "Select distinct operator from qryblohmproductivitytotal
order by operator", CurrentProject.Connection, adOpenStatic,
For Each ctrl In Me.Report.Controls
If Left(ctrl.Properties(1).Value, 5) = "Graph" Then
If rs.EOF = False Then
name = rs.Fields(0)
sRowSource = "TRANSFORM Sum
AS SumOfSumOfBlohmPersonnelProductivity SELECT
(Format([Date],""Short Date"")) AS Expr1 FROM
ryBlohmProductivityTotal INNER JOIN tblOperator ON
qryBlohmProductivityTotal.Operator = tblOperator.Name WHERE
(((qryBlohmProductivityTotal.Operator)=""" & name & """))
GROUP BY (Int([Date])), (Format([Date],""Short Date"")) PIVOT
ctrl.Properties.Item(7).Value = sRowSource
ctrl.Properties.Item(21).Value = False
When the report is run by clicking the button I get the following error :
Run-time error '2113':
The value you entered isn't valid for this field.
I press Debug, then I press continue and the form loads properly with everything just as I want it.
I think that the Graph object is taking too long to load which is why this error is occuring. Can anyone stear me into the correct direction to fix this!
John P. Greiner