Chart Object - SeriesCollection property

[Alan]

Can anyone help with me please - been scratching my head for over a week now
- I am trying to colour stacked bar charts dependant on a set of different
variables using the series collection property struggling to work out why I
continually get
Runtime Error '1004'
Unable to get the SeriesCollection property of the chart class

although when I select Debug and F5 the code runs and the chart is displayed
as expected.

I am using a number of case statements to select the colours an extract of
which is below
With rstAllTimes
With Forms!SearchForm.Graph27
Select Case rstAllTimes.Fields("Status")

Case Is = "Working"
.SeriesCollection(i).Border.Color = vbBlack
.SeriesCollection(i).Interior.Color = 26367
'MsgBox "CL04 Colour orange"

Case Is = "Break"
.SeriesCollection(i).Border.Color = vbBlack
.SeriesCollection(i).Interior.Color = vbBlack

 

[Ralph]

dim objChart as object

With rstAllTimes
set objChart=Forms!SearchForm.Graph27
With objChart
Select Case rstAllTimes.Fields("Status")

Case Is = "Working"
.SeriesCollection(i).Border.Color = vbBlack
.SeriesCollection(i).Interior.Color = 26367
'MsgBox "CL04 Colour orange

 

[Ralph]

Also just noticed that it does not appear you are assigning a value to i
..SeriesCollection(i).Border.Color = vbBlack

 

[Alan]

Ralph

many thanks for your post
However this does not resolve the issue (I apologise I had initilised i
before the start of the code segment I posted.)

I am still trying to get this to work however it may be better that I
explain a bit further.

I have a Form that contains a graph that is based on employee work times
I enter a Pay Number and the graph is then populated following a button
press from tables that sit behind this.

The problem appears to be happening when I update from one employee to
another where there is more in the subsequent series. ie the first employee I
select maybe has 6 events. If I enter a new employee who has more than six
events then I get the error
Unable to get the SeriesCollection property of the chart class

this does not happen when I go to an employee with less in the series ??

Still very Puzzled and any help would be much appreciated

Regards
Many Thanks

Alan

 

[Ralph]

How are you assigning a value to i ? Are you using

i=Forms!SearchForm.Graph27.SeriesCollection.Count

If you have more than one series on the chart, you need to get the count
then loop through it.

 

[Alan]

Ralph

I am assigning i at the start of the loop to i=1
and at the end i=i+1 at the end

The function is attached to a button that should reset the i value

I have tried to set it using the count property and using a For loop however
i now get the error 'No Current Record'

It would appear that the seriescount is remembering the previous graph
parameters and not clearing this out or resetting the count ???

 

[Ralph]

I'd try assigning the count to i then reducing it by 1 each time it runs
through the loop. See below.

With Forms!SearchForm.Graph27
i=Forms!SearchForm.Graph27.SeriesCollection.Count
Select Case rstAllTimes.Fields("Status")

Case Is = "Working"
.SeriesCollection(i).Border.Color = vbBlack
.SeriesCollection(i).Interior.Color = 26367
'MsgBox "CL04 Colour orange"

Case Is = "Break"
.SeriesCollection(i).Border.Color = vbBlack
.SeriesCollection(i).Interior.Color = vbBlack
End Select etc
i=i-1

 

[Alan]

Ralph

Once again thanks for your time ... this still is not working for me still
getting the errors described previously
It appears the series collection count is not being updated despite the
rowsource and variable paynumber being changed ??

 

[Ralph]

I would test it with debug.print

With Forms!SearchForm.Graph27
debug.print Forms!SearchForm.Graph27.SeriesCollection.Count
Select Case rstAllTimes.Fields("Status")

If it is not changing maybe you need to refresh the chart when the recordset
changes.

Forms!SearchForm.Graph27.Refresh

 

[Alan]

Ralph

Once again many thanks for all your assistance
I hqve tried the debug.print and various permutations of your ideas. It
appears that the rowsource is not being updated quick enough for the
sub-routine and thereforce causing the error.

The routione etc works fine when I step through with F8, however when run in
its entirety it fails. I have managed to inroduce a 0.2 second wait which
allows the rowsource to be fully updated and provides the desired output.