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,
     adLockReadOnly

rs.MoveFirst

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
      (qryBlohmProductivityTotal.SumOfBlohmPersonnelProd uctivity)
      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
      qryBlohmProductivityTotal.Operator;"

      ctrl.Properties.Item(7).Value = sRowSource
      rs.MoveNext
    Else
      ctrl.Properties.Item(21).Value = False
    End If
  End If
Next


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!

Thanks


John P. Greiner
[email protected]

Is this: "ctrl.Properties.Item(21).Value = False" a boolean?


mmcdonal

Yes, it is a boolean. (It is the Visability Property) I should have annotated where the code stops working. It halts at:

ctrl.Properties.Item(7).Value = sRowSource

John P. Greiner
[email protected]