Series Collection Chart Problem

[Scott Oh]

I am attempting to setup a chart that allows the user some ability to customize their view. The problem is that I am not to familiar with the SeriesCollection command and I suspect that I am not using it correctly. To set the stage: upon startup, the chart (located at Forms!frmMain2.Form.frmStateAbbr.Form.frmChart1) is defaulted to show five columns. So far this works fine.
The user is then able to customize by clicking (or unclicking) checkboxes (located at at i.e. Forms!frmMain2.Form.frmStateAbbr.Form.chkBankruptcy). So if the user checks a box, in this example chkBankruptcy, it fires up the code (see below) in the AfterUpdate EP and the column chart now has an additional line chart.
Using this chkBankruptcy as an example, when I click on this checkbox the
code hangs on the line in RunChart: With [objChart].Object.SeriesCollection("Bankruptcy"). I can still F8 through this and allow the code to continue it's course.
Using chkForeclosure as a further example, when I click on this checkbox the code hangs on the line in RunChart: With [objChart].Object.SeriesCollection("Foreclosures"). I can still F8 through this allow the code to continue it's course.
Sorry for the long ramble, but I really really want to make this work and
hope that my explanation sheds some light on what my code problem may be.
Thank you for your assistance.

'---------------------- chkBankruptcy_AfterUpdate()----------
Private Sub chkBankruptcy_AfterUpdate()
Dim strSQL As String
'Verify that sum of data exists. If no data exists, aka SumOfRate=0 Then
' a msgbox tells user this and makes check box =0. If data exists then this
chkbox=1
' which will play a role in the module RunChart which is called.
If DSum("Rate", "tblChart1_MT", "Type='Bankruptcy'") = 0 Then
MsgBox "No data for Bankruptcy. Graph will not be altered"
Cancel = True
Me.chkBankruptcy = 0
Else
Call RunChart
End If
End Sub

'---------------------- RunChart----------
Function RunChart()
Dim strInclude As String
Dim strSQL As String
Dim objChart As Object
Set objChart = Forms!frmMain2.Form.frmStateAbbr.Form.frmChart1.Form.OLEUnbound0
'Redefining strSQL repopulates chart's datasheet with full collection of data
strSQL = "TRANSFORM Sum(qryChart1_MT.Rate) AS SumOfRate " & vbCrLf & _
"SELECT qryChart1_MT.DateYr " & vbCrLf & _
"FROM qryChart1_MT " & vbCrLf & _
"WHERE qryChart1_MT.Type " & vbCrLf & _
"GROUP BY qryChart1_MT.DateYr " & vbCrLf & _
"PIVOT qryChart1_MT.Type "
Debug.Print strSQL
Forms!frmMain2.Form.frmStateAbbr.Form.frmChart1.Form.OLEUnbound0.RowSource = strSQL
'The following If statements redefines strInclude that will narrow down the
'amount of fields associated with this chart's datasheet.
'This is a chkbox for this code but currently not enabled in form. It is there just in case
'the user wants to activate in future. It is defaulted on 1 and will always be
'part of strInclude.
If Forms!frmMain2.Form.frmStateAbbr.Form.chkDelDays3059 Then
strInclude = "'A1_DelinqDays3059',"
With [objChart].Object.SeriesCollection("A1_DelinqDays3059")
.type = xlColumn
.Interior.Color = vbRed
End With
End If

'This is a chkbox for this code but currently not enabled in form. It is there just in case
'the user wants to activate in future. It is defaulted on 1 and will always be
'part of strInclude.
If Forms!frmMain2.Form.frmStateAbbr.Form.chkDelDays6089 Then
strInclude = strInclude & "'A2_DelinqDays6089',"
With [objChart].Object.SeriesCollection("A2_DelinqDays6089")
.type = xlColumn
.Interior.Color = vbGreen
End With
End If
'This is a chkbox for this code but currently not enabled in form. It is there just in case
'the user wants to activate in future. It is defaulted on 1 and will always be
'part of strInclude.
If Forms!frmMain2.Form.frmStateAbbr.Form.chkDelDays90119 Then
strInclude = strInclude & "'A3_DelinqDays90119',"
With [objChart].Object.SeriesCollection("A3_DelinqDays90119")
.type = xlColumn
.Interior.Color = vbMagenta
End With
End If
'This is a chkbox for this code but currently not enabled in form. It is there just in case
'the user wants to activate in future. It is defaulted on 1 and will always be
'part of strInclude.
If Forms!frmMain2.Form.frmStateAbbr.Form.chkDelDays90Plus Then
strInclude = strInclude & "'A4_DelinqDays90Plus',"
With [objChart].Object.SeriesCollection("A4_DelinqDays90Plus")
.type = xlColumn
.Interior.Color = vbYellow
End With
End If
'This is a chkbox for this code but currently not enabled in form. It is there just in case
'the user wants to activate in future. It is defaulted on 1 and will always be
'part of strInclude.
If Forms!frmMain2.Form.frmStateAbbr.Form.chkDelDays120Plus Then
strInclude = strInclude & "'A5_DelinqDays120Plus',"
With [objChart].Object.SeriesCollection("A5_DelinqDays120Plus")
.type = xlColumn
.Interior.Color = vbCyan
End With
End If
'These next 14 If statements will run only if the user checks the chkbox
If Forms!frmMain2.Form.frmStateAbbr.Form.chk30YrFixed Then
strInclude = strInclude & "'30YrFixed',"
With [objChart].Object.SeriesCollection("30YrFixed")
.type = xlLine
.MarkerStyle = xlMarkerStylePlus
End With
End If
If Forms!frmMain2.Form.frmStateAbbr.Form.chkBankruptcy Then
strInclude = strInclude & "'Bankruptcy',"
With [objChart].Object.SeriesCollection("Bankruptcy") <Error Here>
.type = xlLine
.MarkerStyle = xlMarkerStylePlus
End With
End If
If Forms!frmMain2.Form.frmStateAbbr.Form.chkForeclosures Then
strInclude = strInclude & "'Foreclosures',"
With [objChart].Object.SeriesCollection("Foreclosures") <Error Here>
.type = xlLine
.MarkerStyle = xlMarkerStylePlus
End With
End If
' < Similar IF Statements >
If strInclude <> "" Then
' cuts off the last comma
strInclude = Left(strInclude, Len(strInclude) - 1)
' adds syntax we need
strInclude = "In(" & strInclude & ");"
End If
strSQL = strSQL & strInclude
Debug.Print strSQL
Forms!frmMain2.Form.frmStateAbbr.Form.frmChart1.Form.OLEUnbound0.RowSource = strSQL
Forms!frmMain2.Form.frmStateAbbr.Form.frmChart1.Form.OLEUnbound0.Refresh
End Function

 

[Uncle Gizmo]

Where did you get “SeriesCollection” from? I cannot find any reference to it in the Microsoft Access help file, I suspect it comes from VB.Net.

 

[Uncle Gizmo]

Ah right, I see it’s part of the chart collection, in that case you may need to set a reference to it in references.

 

[Uncle Gizmo]

I found it in the Excel object model

 

[Scott Oh]

Gizmo, Can you elaborate on what you mean by setting a reference? Perhaps give me an example so I can integrate into my code. Thank you.

 

[ChrisO]

There is a search facility on this site and it returned the following for SeriesCollection

http://www.access-programmers.co.uk/forums/search.php?searchid=6754209

Chris.

 

[Scott Oh]

Chris,
Yes the list for threads on SeriesCollection is certainly well represented here. In fact, I have managed to put together the lion's share of this code based on the suggestions of some very talented people here on this site. The problem is that I cannot seem to find any examples that fit my situation on this site (or Microsoft.com, or UtterAccess.com, etc...). I will continue to look through the link that you provided. However, if you or someone has an idea as to why my code hangs up I would be most appreciative. Regards.

 

[ChrisO]

The errors imply that ‘Bankruptcy’ and ‘Foreclosures’ are not series names in the series collection.

The program flow appears to be: -

If Forms!frmMain2.Form.frmStateAbbr.Form.chkBankruptc y Then
strInclude = strInclude & "'Bankruptcy',"
With [objChart].Object.SeriesCollection("Bankruptcy") <Error Here>
.type = xlLine
.MarkerStyle = xlMarkerStylePlus
End With
End If

Then: -
strSQL = strSQL & strInclude
Forms!frmMain2.Form.frmStateAbbr.Form.frmChart1.Form.OLEUnbound0.RowSource = strSQL
Forms!frmMain2.Form.frmStateAbbr.Form.frmChart1.Form.OLEUnbound0.Refresh

Which would imply you are trying to reference a series name which has not as yet been created.

But thay’s just a guess and I will need a small demo in Access 2003 to look into it any further.

Regards,
Chris.

 

[Scott Oh]

Chris,
Here is a demo version of the database in question. I appreciate you taking time to review. I look forward to hearing your suggestions.
TY

 

[ChrisO]

Those series names are not in the series collection.

Although the code populates the chart with all series names the code references the series name before the chart has finished the population.

So we need to delay the code until the chart’s Updated event has run, see attached.

You really should use option explicit and compile your code.

Chris.

 

[Scott Oh]

Chris,
Thank you for taking the time to review my chart issue and fixing the problem. Delaying the code was an option I would not have figured out. I am seeing that, for example, when I click the "bankruptcy" checkbox this shows a bar instead of a line (per the code). Would this be a function of the delay process?

 

[ChrisO]

I don’t know exactly what the problem is but it is related to the number of series in the series collection and the sorting order of the series collection.

To help you find the problem I have reduced the chart to the bare minimum.

Once you get that fixed you can add all the other stuff back in.

Hope that helps.

Chris.

 

[Scott Oh]

Chris, You've been very generous with your time in helping me in this problem. I reviewed your reply db and feel that I am making this chart way more difficult than it needs to be. Since this approach is not working the way I had hoped I'm wondering if a different (and easier) approach would be warranted. How about if upon the chart loading, all of the series data is loaded up and, like my previous example, the first five series are show and the remaining are simply hidden? Then to be shown when the check box is clicked. This way, all of the updated SQL would be eliminated. Your thoughts?

So change:
If Forms!frmMain2.Form.frmStateAbbr.Form.chkDelDays3059 Then
strInclude = strInclude & "'A1_DelinqDays3059',"
With [objChart].Object.SeriesCollection("A1_DelinqDays3059")
.type = xlColumn
.Interior.Color = vbGreen
End With
End If

to

With [ObjChart].Object.SeriesCollection ("A1_DelinqDays3059")
.type = xlcolumn
If Forms!frmMain2.Form.frmStateAbbr.Form.chkDelDays3059 Then
.Position.Hidden=False
Else
.Position.Hidden=True
End If
End With

I hope that this "mid stream" change is still within the protocol of this thread.

Scott

 

[ChrisO]

Well, I don’t know where the ‘.Position.Hidden’ comes from but it doesn’t appear to work in Access.
However, we can Delete a series so that’s the way I went.

It concerns me a bit that you seem to be copy/pasting code from somewhere and using it in a standard module.
I mentioned Option Explicit, compiling your code and you seem to persist with code like Forms!frmMain2.Form.frmStateAbbr.Form.chkDelDays3059 and [ObjChart].Object.SeriesCollection ("A1_DelinqDays3059")

The reason I gave you version 2 was to show you another way rather than just following on the same path. At this stage I don’t see much point in having the code anywhere else other than in the class module behind the Form which contains the Chart. It only adds an unnecessary complication to something we are trying to debug.

In any case, version 3 is attached.

HTH.

Chris.

 

[Scott Oh]

Chris, You are the man! The chart flows amazingly well. How you approached this is certainly above my VBA grasp and I look forward to using this chart project to increase my Access skills. To reply to your comments, most of what I was attempting to do was within my skill set.... obviously I have alot to learn. Thank you again for your time, patience and willingness to help. Scott.