Update Graph in Design View (VBA?)

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello all,

I have a graph on a report that works great. In design view the graph
just shows default data, then when the report runs it pulls in data from a
query. I can get into the datasheet for the graph object and paste in the
"real" data to make the graph look in design view like it will in preview
view. As this data is always changing is there a way that I can write some
code so that whenever I open the report in design view it will automatically
update the datasheet of the graph object so it shows the "real" data in
design view.

Sub UpdateAccessGraph(QueryName As String, GraphName As String, ReportName
As String)

Dim MyChart As Object

Set MyChart = Reports(ReportName).Controls("Graph")
'Copies the query, could do it with nested loops and one cell at a time
if desired
DoCmd.OpenQuery (QueryName)
DoCmd.RunCommand acCmdSelectAllRecords
DoCmd.RunCommand acCmdCopy
DoCmd.Close

'Clear the chart data and paste the new data
MyChart.Application.DataSheet.Cells.Clear
MyChart.Application.DataSheet.Range("00").Paste
MyChart.Application.Update

'clean up objects
Set MyChart = Nothing
End Sub

Here's some dummy code for what I'm trying to accomplish, although it
doesn't work.
 
Troy,

Access saves a model of the chart in your report design, it doesn't link to
live data until you actually run the report, the same way you cannot see real
data in the design view of the report, it will not refresh data for your
graph, you could copy real data to the chart's data sheet, but that will stay
until you copy again, which is not practical and it will not accomplish what
you are trying to do,
 
Back
Top