Change Graph Recordsource and Format Graphs

[striker83716]

I am struggling with a access database I am working on. I have a form with a graph and a list box. After update of the listbox I would like to update the graph's recordsource. The recordsource will not be based off of the same table or query. I have this somewhat working, however when I select from the list box value who's recordsource has only 2 data series then select one that has more then 2, I get errors trying to change dataseries properties.
Any ideas?
Thanks

Private Chartobj As Graph.Chart
Private FilterSQL As Byte

Private Sub NewCategorySQL(gt As Integer)
Dim n As Byte, i As Byte
Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Dim myStatement As String
Dim myGraphType As Integer
Dim myCaption As String
Set dbs = CurrentDb
Set Chartobj = GraphContainer.Object.Application.Chart

myStatement = DLookup("Statement", "tblQueries", "idQuery=" & gt)
myGraphType = DLookup("GraphType", "tblPrgCategories", "QueryName=" & gt)
myCaption = DLookup("[Program Category]", "tblPrgCategories", "QueryName=" & gt)

If Not IsNull(myStatement) Then
dbs.QueryDefs.Delete "NameofYourQuery" 'Delete Query
Set qdf = dbs.CreateQueryDef("NameofYourQuery", myStatement)
qdf.SQL = myStatement
Chartobj.ChartType = myGraphType

Else
MsgBox "No data available"
Exit Sub
End If


Select Case gt
Case 1 'Schedule
Chartobj.ChartArea.ClearFormats
With Chartobj
.ChartType = myGraphType
.HasTitle = True
With Chartobj.ChartTitle
.Caption = myCaption
End With
.HasLegend = True
.HasAxis(xlValue) = True
.HasAxis(xlCategory) = True
.Height = 500
.Width = 500
.Legend.Position = xlLegendPositionBottom
.Legend.Fill.Visible = False
.Legend.Border.LineStyle = xlLineStyleNone
For n = 1 To Chartobj.SeriesCollection.Count
With Chartobj.SeriesCollection
.HasDataLabels = True
.DataLabels.Font.ColorIndex = 1
.DataLabels.Font.Size = 8
.DataLabels.NumberFormat = "###,###,###"
.DataLabels.Font.Background = xlBackgroundTransparent
End With
With Chartobj.Axes(xlValue)
.HasTitle = True
.AxisTitle.Caption = "Total Effect in Days"
.AxisTitle.Orientation = xlTickLabelOrientationUpward
.TickLabels.NumberFormat = "###,###,###"
End With
With Chartobj.Axes(xlCategory)
.HasTitle = True
.AxisTitle.Caption = "Shop Order"
.AxisTitle.Orientation = xlTickLabelOrientationHorizontal
End With
Next n
End With

Case 2 'Financials
Chartobj.ChartArea.ClearFormats
With Chartobj
.ChartType = myGraphType
.HasTitle = True
.HasLegend = True
.HasAxis(xlValue) = True
.HasAxis(xlCategory) = True
.Height = 500
.Width = 500
.Legend.Position = xlLegendPositionBottom
.Legend.Fill.Visible = False
.Legend.Border.LineStyle = xlLineStyleNone
.ApplyDataLabels (xlDataLabelsShowValue)
With Chartobj.ChartTitle
.Caption = myCaption
End With
With Chartobj.Axes(xlValue)
.HasTitle = True
.AxisTitle.Caption = "Manhours"
.AxisTitle.Orientation = xlTickLabelOrientationUpward
.TickLabels.NumberFormat = "###,###,###"
End With
With Chartobj.Axes(xlCategory)
.HasTitle = True
.AxisTitle.Caption = "Contract"
.AxisTitle.Orientation = xlTickLabelOrientationHorizontal
End With
End With

Case 3 'Quality Assurance
Chartobj.ChartArea.ClearFormats
With Chartobj
.ChartType = myGraphType
.HasTitle = True
.HasAxis(xlValue) = True
.HasAxis(xlCategory) = True
.HasLegend = True
.Height = 500
.Width = 500
.Legend.Position = xlLegendPositionBottom
.Legend.Fill.Visible = False
.Legend.Border.LineStyle = xlLineStyleNone
With Chartobj.ChartTitle
.Caption = myCaption
End With
With Chartobj.Axes(xlValue)
.HasTitle = True
.AxisTitle.Caption = "Total Defects"
.AxisTitle.Orientation = xlTickLabelOrientationUpward
.TickLabels.NumberFormat = "###,###,###"
End With
With Chartobj.Axes(xlCategory)
.HasTitle = True
.AxisTitle.Caption = "Shop Order"
.AxisTitle.Orientation = xlTickLabelOrientationHorizontal
End With
For n = 1 To Chartobj.SeriesCollection.Count
With Chartobj.SeriesCollection
.HasDataLabels = False
End With
Next n
End With

Case 4 'Milestones
Chartobj.ChartArea.ClearFormats
With Chartobj
.ChartType = myGraphType
.HasTitle = True
.HasLegend = True
.HasAxis(xlValue) = True
.HasAxis(xlCategory) = True
.Height = 500
.Width = 500
.Legend.Position = xlLegendPositionBottom
.Legend.Fill.Visible = False
.Legend.Border.LineStyle = xlLineStyleNone
.Legend.Font.Size = 10
'.ApplyDataLabels (xlDataLabelsShowValue)
With Chartobj.ChartTitle
.Caption = myCaption
End With
For n = 1 To Chartobj.SeriesCollection.Count
With Chartobj.SeriesCollection
.HasDataLabels = True
.DataLabels.Font.ColorIndex = 1
.DataLabels.Font.Size = 8
.DataLabels.NumberFormat = "###,###,###"
.DataLabels.Font.Background = xlBackgroundTransparent
.DataLabels.Position = xlLabelPositionBelow
End With
Next n
With Chartobj.Axes(xlValue)
.HasTitle = True
.AxisTitle.Caption = "Deviation in Days"
.AxisTitle.Orientation = xlTickLabelOrientationUpward
.TickLabels.NumberFormat = "###,###,###"
End With
With Chartobj.Axes(xlCategory)
'.HasTitle = True
'.AxisTitle.Caption = ""
'.AxisTitle.Orientation = xlTickLabelOrientationHorizontal
.MajorTickMark = xlTickMarkNone
.TickLabelPosition = xlTickLabelPositionNone
End With

With Chartobj.Legend
.LegendEntries(1).LegendKey.MarkerBackgroundColorIndex = 3
.LegendEntries(1).LegendKey.MarkerForegroundColorIndex = 3
.LegendEntries(1).LegendKey.MarkerSize = 9
.LegendEntries(2).LegendKey.MarkerBackgroundColorIndex = 6
.LegendEntries(2).LegendKey.MarkerForegroundColorIndex = 6
.LegendEntries(2).LegendKey.MarkerSize = 9
.LegendEntries(3).LegendKey.MarkerBackgroundColorIndex = 5
.LegendEntries(3).LegendKey.MarkerForegroundColorIndex = 5
.LegendEntries(3).LegendKey.MarkerSize = 9
End With
End With

Case 14 '80/20 List
Chartobj.ChartArea.ClearFormats
With Chartobj
.ChartType = myGraphType
End With


End Select

Set Chartobj = Nothing

End Sub

 

[Flippertje]

Why not simply use the 'After update'event of the combobox to fill the recordsource of the graph?

grtz Flippertje

 

[striker83716]

I have tried the AfterUpdate of the list box to change the recordsource of the graph as well as the code above, which is also triggered by the afterupdate of the listbox. In both cases If I select a value that has a series collection of 1, then select a value that has a series collection greater then 1, I get a "Run-time error '1004': Unable to get the SeriesCollection of the Chart Class