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"
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
