Access 97 mulitple line graphs, need help

powellm1976

Registered User.
Local time
Today, 10:13
Joined
Aug 23, 2002
Messages
13
What I'm looking for is to be able to display multiple lines on a graph. I want to be able to display five separate lines for five years of sales. I have one year working great but I have no idea how to get the other to work. If someone could help me I would be forever in debt.

Mark
 
I had similar problem with Access. I found out that if you are using MS Graph, you can have multiple line ONLY if you are using the same x-axis for all your lines (so you can't have 200 data for year 1 and then 500 for year 2, it has to be the same).

The way I found to make it work was to use automation and create my graphs with Excel and embedded it as an OLEObject in my report. Not quite simple but you have all the flexibility of Excel... I can post my code if you think you need it.
 
Mario, i could be doing with a look at your code as this will be my next hurdle! I have heard that Access isn't good for graphs go doing one in excel is cool with me!
 
I think looking at your code will help a ton. The info would all be the same ex. if year 2000 started at $500 then all the other years would also start there. Thanks
 
This should do it. Make sure to add an OLE Object to your report (Insert, Object..., MS Excel Chart) and name it OLEExcelObject. Your report should be named ReportName and your table of data --> Data. Only 2 series are there but the same principle will work for any numbers. Have Fun

Private Sub CreateGraph()
Dim db As Database
Dim rstData As Recordset
Dim xlWrkSht As Worksheet
Dim xlGraph As Chart
Dim iCols As Integer
Dim NumberOfData As Integer
Dim Xl As New Excel.Application


'Excel MUST be opened. If not, graph won't work
Xl.Workbooks.Add.Activate

Set xlGraph = Reports!ReportName!OLEExcelObject.Charts("Chart1")
Set xlWrkSht = Reports!ReportName!OLEExcelObject.Worksheets("Sheet1")
Set db = CurrentDb()
Set rstData = db.OpenRecordset("Data")

'new set of data
xlGraph.ChartArea.ClearContents

'copying the columns title if needed
For iCols = 0 To rstData.Fields.Count - 1
xlWrkSht.Cells(1, iCols + 1).Value = rstData.Fields(iCols).Name
Next

'counting how many of new data
While xlWrkSht.Range("B" & NumberOfData + 1) <> ""
NumberOfData = NumberOfData + 1
Wend

xlGraph.Activate

'first serie
xlGraph.SetSourceData _
Source:=xlWrkSht.Range("a1:b" & NumberOfData), _
PlotBy:=xlColumns
'Second serie
xlGraph.SeriesCollection.Add _
Source:=xlWrkSht.Range("c1:d" & NumberOfData)

'close excel. MUST be closed before the report
Xl.Quit

'release every pointer. free up memory
Set Xl = Nothing
Set xlWrkSht = Nothing
Set xlGraph = Nothing
Set db = Nothing
Set rstData = Nothing
Set rstHist = Nothing
End Sub
 

Users who are viewing this thread

Back
Top Bottom