Help with Creating Charts

[dakota81]

I'm trying to create a report with a chart, and I'm not figuring out the chart wizard with Access 97. I couldn't find any help on Microsoft's help site (it never wen't further than "use the chart wizard" in any of its documents). Is there another site somewhere that explains the wizard and/or chart setup better?

 

[plawrence]

I forget where I found the origin of this (probably at

http://www.mvps.org/access)

but basically, don't use the chart wizard. You want to fill in the chart's datasheet.

This is an example I could find quick. I called the FillDataSheet routine with

FillDataSheet Me.GraphFtrAreaTCCMod.Object, rs

sending it the graph object and recordset to fill the datasheet.

Here's what I have in my FillDataSheet module (I filled in the first column with an array, you can use the appropriate info from your recordset--I also checked for nulls since they don't print):


'-----------------
Option Explicit
Option Compare Database
Public bolNull As Boolean

Public Sub FillDataSheet(GraphObject As Object, rs As DAO.Recordset)

'This will fill the datasheet of the Chart
'with the data in the recordset
'RowSourceType and RowSource must be blank

Dim oSheet As Graph.DataSheet
Dim r As Integer 'row counter
Dim c As Integer 'column counter
Dim strLegend As String
Dim varNulls(), vvarNull, varNulls1(), vvarNull1, strDate As Variant
Dim strnulls As String
On Error GoTo ErrorTrap
Set oSheet = GraphObject.Application.DataSheet

'clear old data
oSheet.Cells.ClearContents

'1st Row Field Names = Legend entries
For c = 1 To rs.Fields.Count
oSheet.Cells(1, c) = rs(c - 1).Name
Next

'Fill in Date column to show dates to end of year 2002
r = 2
Dim strDates()
strDates = Array("5/1/2002", "6/1/2002", "7/1/2002", "8/1/2002", "9/1/2002", "10/1/2002", "11/1/2002", "12/1/2002&quot
For Each strDate In strDates
oSheet.Cells(r, 1).Value = strDate
r = r + 1
Next strDate

'now data rows
r = 2
bolNull = False
Do Until rs.EOF
For c = 2 To rs.Fields.Count
If IsNull(rs(c - 1).Value) Or IsError(rs(c - 1).Value) Then
oSheet.Cells(r, c) = 0
bolNull = True
Else
oSheet.Cells(r, c) = rs(c - 1).Value
End If
Next
r = r + 1
rs.MoveNext
Loop

Set oSheet = Nothing

Exit Sub

ErrorTrap:
If Err.Number = 6 Then
Resume Next
Else: MsgBox Err.Number & vbTab & Err.Description
End If

End Sub
'----------------

Just format the chart in your report or form how you want it to appear.

Patrick

 

[dakota81]

I appreciate the help, though that sample will not work in Access 97, or else I don't have enough options installed...