runtime error 3021

[gracehskuo]

hi,please help.when i try to plot chart from the mschart have this runtime error appear.Runtime error '3021'.

this is the current code:

Con.Open "driver={SQL Server};Server=ERP_server;Uid=sa;pwd=sa;database=SPC"

Set Rss = Nothing
Rss.Open " Select sum(sampleno/5)as subgroup,avg(data)as avgdata ,avg(sevenpoint)as seven, time,date from spcdata where date between '" & Format$(startdate, "yyyy-mm-dd") & "' and '" & Format$(enddate, "yyyy-mm-dd") & "' and partno ='" & partno & "'and dimension = '" & dimension & "' and machineno ='" & machineno & "'group by date,time", Con, adOpenKeyset ' Making Recordset
rs1.Open "SELECT PartNo, Dimension,Nominal, UCL, LCL From PartList WHERE PartNo = '" & partno & "' AND Dimension = '" & dimension & "' ", Con, adOpenKeyset ' Making Recordset
 
     If Rss.RecordCount = 0 Then ' If no Record in Database, then Show an Error Msg and Exit the Sub
    Else
        ReDim ArrayChart(1 To Rss.RecordCount, 1 To 5) ' Array
        For X = 1 To Rss.RecordCount
        ArrayChart(X, 1) = Rss!avgdata
        ArrayChart(X, 2) = rs1!ucl
        ArrayChart(X, 3) = rs1!lcl
        ArrayChart(X, 4) = (rs1!lcl + rs1!ucl) / 2
        ArrayChart(X, 5) = Rss!seven
                 Rss.MoveNext
        Next X
  
    ' 'Set manually the setting of the Scale of Axis
MSChart1.Plot.Axis(VtChAxisIdY).ValueScale.Auto = False
MSChart1.Plot.Axis(VtChAxisIdY).ValueScale.Maximum = (rs1!ucl) + (0.2 * ((rs1!ucl) - (rs1!lcl)))
MSChart1.Plot.Axis(VtChAxisIdY).ValueScale.Minimum = (rs1!lcl) - (0.2 * ((rs1!ucl) - (rs1!lcl)))
      
        
'# Assigns array to the MSChart control #
    MSChart1.ChartData = ArrayChart
    MSChart1.SeriesType = VtChSeriesType2dLine
    MSChart1.Refresh
    End If
    
      If Rss!seven < rs1!lcl Then error message higlight at this line
               MSChart1.Plot.SeriesCollection(5).SeriesMarker.Show = False
        Else
                MSChart1.Plot.SeriesCollection(5).SeriesMarker.Show = True
        End If
    
    
    With MSChart1.Plot.SeriesCollection(1)
        .SeriesMarker.Auto = False      
        .DataPoints.Item(-1).Marker.Visible = True
        .DataPoints.Item(-1).Marker.Size = 65
        .DataPoints.Item(-1).Marker.Style = VtMarkerStyleSquare
        End With
        
       With MSChart1.Plot.SeriesCollection(5)
        .SeriesMarker.Auto = False       
        .DataPoints.Item(-1).Marker.Visible = True
        .DataPoints.Item(-1).Marker.Size = 65
        .DataPoints.Item(-1).Marker.Style = VtMarkerStyleSquare
        End With
            
 Set Rss = Nothing
 Set rs1 = Nothing
        
End Sub

actually i'm try to hide the marker if the rss!seven is less than the rs1!lcl scale .i don't know is work for mschart from vb or not.please help thanks.

[RobDog888]

You should not be using the Recordcount property as depending upon the sursor location and lock type it wont be supported.

Use the BOF and EOF comparisons to determine if there are records present in your recordset.

If (oRs.BOF = True) AND (oRs.EOF = True) Then
    'No records
Else
    'records
End If

[gracehskuo]

hi,thanks reply.i still facing the same error message.please guide .

this is the current code:

If Not Rss.EOF And Rss.BOF Then
    MsgBox "no record"
    Else
     If Rss!seven < rs1!lcl Then
               MSChart1.Plot.SeriesCollection(5).SeriesMarker.Show = False
        Else
                MSChart1.Plot.SeriesCollection(5).SeriesMarker.Show = True
        End If
   End If

thanks for help.

[RobDog888]

This is wrong. if you dont use parenthesis with the Not then you are only using Not on the first eval criteria.

If ((Rss.EOF = False) And (Rss.BOF = False)) Then

[gracehskuo]

RobDog888:
hi,i try the method as you guide,but the error remain the same.

this is the current code:

     If ((Rss.EOF = False) And (Rss.BOF = False)) Then
    MsgBox "no record"
    Else
     If Rss!seven < rs1!lcl Then error highlight at this line
               MSChart1.Plot.SeriesCollection(5).SeriesMarker.Show = False
        Else
                MSChart1.Plot.SeriesCollection(5).SeriesMarker.Show = True
        End If
   End If

please help.thanks.

[Hack]

How many records are returned from your query?

[RobDog888]

Recordcount may not bee of any value as it may not be supported.

If Rss!seven < rs1!lcl Then

Can generate many different errors like Type comparisons if one field is of a string and another is of date etc.

[gracehskuo]

Hack:
hi,Rss!seven is a plotting data point,will return many record from the query.rs1!Lcl is only return one data point for minimum scale Y from mschart.

thanks for help.

[RobDog888]

You might want to fully qualify the objects instead of relying upon the defaults for evaluation.

If Rss.Fields("seven").Value < rs1.Fields("lcl").Value Then