Hi,Is it possible to only show 15 points on mschart at a time? this is the current code:
Public Sub Cmdmschart_Click()
Dim partno As String
Dim avgdata As Double
Dim enddate As String
Dim startdate As String
Dim machineno As String
Dim dimension As String
Dim rs1 As New ADODB.Recordset ' Recordset
Dim rs2 As New ADODB.Recordset ' Recordsetmachineno = Combomachineno.Text
startdate = DTPicker3.Value
'enddate = DTPicker2.Value
partno = combopartno.Text
dimension = txtcdimension.Text
Set Con = Nothing
Con.Open "driver={SQL Server};Server=GRACEKUO;Uid=sa;pwd=sa;database=SPC"
'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 ,time,date from spcdata where date ='" & Format$(startdate, "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
' MsgBox "No Data to Show on Chart!!!", vbCritical, "MSChart Demo": Exit Sub
Else
ReDim ArrayChart(1 To Rss.RecordCount, 1 To 4) ' Array
'Puuting Records from Database to 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
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.Refresh
End If
With MSChart1.Plot.SeriesCollection(1)
.SeriesMarker.Auto = False ' This is the important line, without it your er will not change.
.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 Subthanks for help
Public Sub Cmdmschart_Click()
Dim partno As String
Dim avgdata As Double
Dim enddate As String
Dim startdate As String
Dim machineno As String
Dim dimension As String
Dim rs1 As New ADODB.Recordset ' Recordset
Dim rs2 As New ADODB.Recordset ' Recordsetmachineno = Combomachineno.Text
startdate = DTPicker3.Value
'enddate = DTPicker2.Value
partno = combopartno.Text
dimension = txtcdimension.Text
Set Con = Nothing
Con.Open "driver={SQL Server};Server=GRACEKUO;Uid=sa;pwd=sa;database=SPC"
'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 ,time,date from spcdata where date ='" & Format$(startdate, "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
' MsgBox "No Data to Show on Chart!!!", vbCritical, "MSChart Demo": Exit Sub
Else
ReDim ArrayChart(1 To Rss.RecordCount, 1 To 4) ' Array
'Puuting Records from Database to 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
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.Refresh
End If
With MSChart1.Plot.SeriesCollection(1)
.SeriesMarker.Auto = False ' This is the important line, without it your er will not change.
.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 Subthanks for help
do while Rss.eof=false
ArrayChart(X, 1) = Rss!avgdata
ArrayChart(X, 2) = rs1!ucl
ArrayChart(X, 3) = rs1!lcl
ArrayChart(X, 4) = (rs1!lcl + rs1!ucl) / 2
Rss.MoveNext
loop
ArrayChart(X, 1) = Rss!avgdata
ArrayChart(X, 2) = rs1!ucl
ArrayChart(X, 3) = rs1!lcl
ArrayChart(X, 4) = (rs1!lcl + rs1!ucl) / 2
Rss.MoveNext
Next X
your Rss.RecordCount =R1.RecordCount ? or < or >?