Can't figure out creating charts via VB in access

[Guest_imported]

If anyone could help me I would greatly appreciate it. I need to do some things in visual basic to the data I want to make a chart with, can someone please help me figure out how to create a chart via visual basic code instead of the chart wizard thing. Just assume I have a table called Table1 with two columns (Date1 and Price1) that I want to chart if that helps to make the response easier. I am not an expert at visual basic, but I can usually grind my way through until I get it to work...this one is tough though.
Thanks,
Jim

 

[cmmrfrds]

I just discovered how to use a web component chart in a form, which is done through VBA code. First you need to install an ActiveX chart control on your form and use that area in your code. I happen to use a stored procedure but you can just use an SQL string. Here is all the code and comments from my Form.

Option Compare Database
Private owcChartSp As OWC.ChartSpace
Public begDate As Date, endDate As Date

''---------
'-- Need to install a reference to Microsoft office Web Components 9.0
'-- Load the DLL from the Office directory MSOWC.DLL -- will need to browse
'-- for the DLL in C:\Program Files\Microsoft Office\Office\msowc.dll
'-- Create a chart area on the form from Active X Control
'-- install activeX control Microsoft Office Chart 9.0 and give it the same name
'-- in the property as used in the code. for example, owcChart
''----------
Private Sub Form_Load()
'--

''Set owcChartSp = New ChartSpace
Set owcChartSp = Me.owcChart.Object

Dim cnn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rst As New ADODB.Recordset
Dim param1 As Parameter, param2 As Parameter
begDate = #10/1/2001#
endDate = #10/31/2001#

' Connect
Set cnn = CurrentProject.Connection

Set cmd.ActiveConnection = cnn
Set param1 = cmd.CreateParameter("Input", adDBDate, adParamInput)
cmd.Parameters.Append param1
param1.Value = begDate
Set param2 = cmd.CreateParameter("Input", adDBDate, adParamInput)
cmd.Parameters.Append param2
param2.Value = endDate

' Set up a command object for the stored procedure.
cmd.CommandText = "dbo.sp_employeeRevenueProgress"
cmd.CommandType = adCmdStoredProc

' Execute command to run stored procedure
''cmd.Execute

Set rst = cmd.Execute

Call FillChartFromRecordSet(owcChartSp, rst, "tmpEmployeeName", _
"tmpBilledRevenuePerc", _
"tmpUnbilledRevenuePerc", _
"tmpTotal", _
"tmpDraw&quot

Set cnn = Nothing

End Sub

Function FillChartFromRecordSet(owcChartSp As OWC.ChartSpace, _
rst As ADODB.Recordset, _
categoryField As String, _
valueField As String, _
valueField2 As String, _
valueField3 As String, _
valueField4 As String)

Dim owcChart As OWC.WCChart

owcChartSp.Clear
Set owcChartSp.DataSource = rst
Set owcChart = owcChartSp.Charts.Add()
owcChartSp.Charts(0).HasTitle = True
owcChartSp.Charts(0).Title.Caption = "Employee Revenue Status " & _
begDate & " thru " & endDate
owcChartSp.Charts(0).Title.Font.Underline = True
owcChartSp.Charts(0).SeriesCollection.Add (0)
owcChartSp.Charts(0).SeriesCollection.Add (1)
owcChartSp.Charts(0).SeriesCollection.Add (2)
owcChartSp.Charts(0).SeriesCollection.Add (3)
owcChartSp.Charts(0).SeriesCollection(0).Caption = "Billed"
owcChartSp.Charts(0).SeriesCollection(1).Caption = "Unbilled"
owcChartSp.Charts(0).SeriesCollection(2).Caption = "Total"
owcChartSp.Charts(0).SeriesCollection(3).Caption = "Draw"

owcChartSp.Charts(0).HasLegend = True
owcChart.Legend.Interior.Color = "aqua"
owcChartSp.Charts(0).Interior.Color = "beige"
owcChartSp.Charts(0).Legend.Position = chLegendPositionTop


owcChartSp.Charts(0).Axes(0).Position = chAxisPositionTop
owcChartSp.Charts(0).SeriesCollection(0).Interior.Color = "blue"
owcChartSp.Charts(0).SeriesCollection(1).Interior.Color = "yellow"
owcChartSp.Charts(0).SeriesCollection(2).Interior.Color = "red"
owcChartSp.Charts(0).SeriesCollection(3).Interior.Color = "teal"

With owcChart
.Type = chChartTypeBarClustered
.HasLegend = True
' .Axes(0).HasTitle = true
' .Axes(0).Title.Caption = "Revenue Billed and Unbilled"
.Axes(1).HasTitle = True
.Axes(1).Title.Caption = "E M P L O Y E E S"
.Axes(0).HasMinorGridlines = True
.Axes(1).HasMajorGridlines = True

' Hook up the two necessary data fields.
.SetData chDimCategories, 0, categoryField
.SeriesCollection(0).SetData chDimValues, 0, valueField
.SeriesCollection(1).SetData chDimValues, 0, valueField2
.SeriesCollection(2).SetData chDimValues, 0, valueField3
.SeriesCollection(3).SetData chDimValues, 0, valueField4

' value. Also, show the category name.
' Use ":" as the separator between
' the category name and the percentage.
With .SeriesCollection(0).DataLabelsCollection.Add
.HasValue = True

.HasCategoryName = False
.HasValue = False
.HasSeriesName = False
' .Separator = ": "

' Use 8pt black text on a white background,
' in Tahoma font.
.Interior.Color = "white"
.Border.Color = "black"
With .Font
.Name = "Tahoma"
.Color = "black"
.Bold = True
.Size = 10
End With
End With
With .SeriesCollection(1).DataLabelsCollection.Add
.HasValue = True

.HasCategoryName = False
.HasValue = False
.HasSeriesName = False
' .Separator = ": "

' Use 8pt black text on a white background,
' in Tahoma font.
.Interior.Color = "white"
.Border.Color = "black"
With .Font
.Name = "Tahoma"
.Color = "black"
.Bold = True
.Size = 10
End With
End With
End With

End Function

 

[Guest_imported]

I tried to find the DLL you stated, in that location I found several files called Graph8 (.exe, .aw, .cnt, .olb, .srb). I could not find an MSOWC.DLL file. Can I do this VB program with Graph8? or is it totally different? I have Office 97 on the computer I'm doing this on...that is probably why it is different. Sorry for so many questions, I appreciate your quick responce very much.

 

[genomon]

Graph8 is the software that supports MSGraph (not what you want). You should also have an activex control shipped with access97 called MSChart Control V6.0 (OLEDB). You will need to click on "more tools" in your toolbox to find it. Once installed (add to your toolbox - instructions on how to do this in help), there are all kinds of properties and methods at your disposal (the graph is able to be altered by the user as well, so be careful). In a nutshell, you load it up with values from an array and set properties. Here is a simple example illustrating some of them from one of my apps:
With chtChartBlockC
.ChartData = yourArray 'populate with your data
.ColumnCount = 3
.ColumnLabelCount = 3
.Legend.Location.LocationType = VtChLocationTypeTopRight
.Column = 1
.ColumnLabel = "Whatever1"
.Column = 2
.ColumnLabel = "Whatever2"
.Column = 3
.ColumnLabel = "Whatever3"
.Refresh
End With

You will have to declare and populate your source array where & how you see fit. There is also a working example that shipped with examples in Office97, but not sure about the stand-alone Access97 (probably depends on which version). Good luck!!
-Geno