ACCESS Design Tips & Code

ACG Developer Products
Free File Library
Other Web Sites
News & User Groups
Home

Current Tip/Code Sample: Changing the Record Source for a Graph in a Report

Archive of Tips and Code Samples

Tables

Queries and Record Sets

Forms

Reports

VBA Modules & API

Multi-User

 

General

If you have an Access tip or code sample that you'd like to share, mail it to us and we may add to our pages here for other Access Users.
Copyright © 1996-2006 ACG Soft, All Rights Reserved














Back to the Tips Index

 Changing the Record Source for a Graph Object in Reports

A common request is to change and set the Record source for a Microsoft Graph object in an Access report at run time.  The MS Graph control does not directly allow you to change its record source at run time, producing an error if you attempt to do so.  However there are two solutions that can serve as a work around to allow dynamic graph objects within an Access report.

1) Use a generic query as the row source for the chart (something that returns some number of rows), and then use either DAO or ADO depending on what data access method you are using to change the SQL string for that query before you open the report.   Then in your report, in the On Print event of the section that contains the graph, add code like this: 

  On Error Resume Next
  Me.Graph1.Object.Refresh
  Me.Graph1.Object.Application.Update 
 

  Where "Graph1" is the name of the Chart object in your report.

2) Alternately, you can use a dynamic (run time) record set to fill the Graph object and set its data series as well.   To do so, you fill the Data Sheet sub object of the Graph object with data that you want displayed.  Then tell Graph to refresh itself and the new data will be displayed in the Graph, including changes in series, data point and data.  Note however that any settings you have made for colors of bars or pie pieces etc., will be retained.  

The code below shows how to implement this method when it is run in the On Print event of the section of the report that contains the Graph object.   It is shown using DAO data access code, however the same method will work with ADO data access methods and with record set returned from SQL Server Views and Stored Procedures.   Note that if you need to change Graph titles and other objects within the Graph, those changes are not shown here and you should consult the Graph objects help file in the programming section.

Solution:


  On Error Resume Next
  Dim objGraph As Object, objDS As Object, rsData As Recordset
  Dim intRowMax as Integer, intColMax as Integer, arrData As Variant
  Dim i as Integer, j as Integer

  Set objGraph = Me!Graph1.Object
  Set objDS = objGraph.Application.DataSheet
  Set rsData = CurrentDb.OpenRecordset("Select * from YourTargetDataSource")

  

  'Use the GetRows record set method to return the data in an array
  'Set GetRows to some reasonably large number to accommodate the data
  'it will only fill the array with the actual amount of data returned

  arrData = rsData.GetRows(200) 
  intRowMax = UBound(arrData, 1)
  intColMax = UBound(arrData, 2)

  'Graph's Data Sheet cells are in row, column format, _
      starting at 1,1 with the row headers
  'GetRows data array is in column, row format starting _
      at 0,0, where row 0 is the first row of data; no field headers

    objDS.cells.Clear  'clear out any previous data

  'Add the column heads from the recordset's fields to the data sheet
  'These are the data series names
  For i = 0 To rsData.Fields.Count - 1
     objDS.Cells(1, i + 1) = rsData.Fields(i).name
  Next i 
  rsData.Close 

  'Now add the data to each column
  For i = 0 To intRowMax
     For j = 0 To intColMax
         objDS.cells(i + 2, j + 1) = arrData(j, i)  'Note these are reversed!
     Next j
  Next i 

  Set objDS = Nothing 
  DoEvents 
  objGraph.Refresh 
  Set objGraph = Nothing 
 

 

Back to the Tips Index