frmcj1.frm

从别处拿来感觉还可以 内容比较多且完备值得大家看一看

VERSION 5.00
Object = "{5E9E78A0-531B-11CF-91F6-C2863C385E30}#1.0#0"; "MSFLXGRD.OCX"
Begin VB.Form frmcj1 
   BorderStyle     =   1  'Fixed Single
   Caption         =   "成绩浏览"
   ClientHeight    =   5415
   ClientLeft      =   45
   ClientTop       =   330
   ClientWidth     =   10665
   LinkTopic       =   "Form2"
   MaxButton       =   0   'False
   MDIChild        =   -1  'True
   MinButton       =   0   'False
   ScaleHeight     =   5415
   ScaleWidth      =   10665
   Begin VB.Frame Frame4 
      Caption         =   "类型："
      Height          =   615
      Left            =   7080
      TabIndex        =   10
      Top             =   120
      Width           =   1575
      Begin VB.ComboBox Combo1 
         BackColor       =   &H80000018&
         Height          =   300
         Index           =   3
         Left            =   120
         Style           =   2  'Dropdown List
         TabIndex        =   11
         Top             =   200
         Width           =   1335
      End
   End
   Begin VB.CommandButton Command1 
      Caption         =   "修 改"
      Height          =   255
      Left            =   2520
      TabIndex        =   9
      Top             =   5160
      Width           =   855
   End
   Begin VB.CommandButton Command2 
      Caption         =   "删 除"
      Height          =   255
      Left            =   5520
      TabIndex        =   8
      Top             =   5160
      Width           =   975
   End
   Begin VB.Frame Frame1 
      Caption         =   "成绩浏览"
      Height          =   4335
      Left            =   120
      TabIndex        =   6
      Top             =   840
      Width           =   10335
      Begin MSFlexGridLib.MSFlexGrid MSF1 
         Height          =   3975
         Left            =   120
         TabIndex        =   7
         Top             =   240
         Width           =   10125
         _ExtentX        =   17859
         _ExtentY        =   7011
         _Version        =   393216
         Cols            =   1
         BackColor       =   -2147483624
         BackColorFixed  =   12632256
         ForeColorFixed  =   0
         BackColorSel    =   16777215
         ForeColorSel    =   255
         BackColorBkg    =   -2147483624
         AllowBigSelection=   -1  'True
         SelectionMode   =   1
         AllowUserResizing=   3
         BeginProperty Font {0BE35203-8F91-11CE-9DE3-00AA004BB851} 
            Name            =   "宋体"
            Size            =   9
            Charset         =   134
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
      End
   End
   Begin VB.Frame Frame2 
      Caption         =   "年级："
      Height          =   615
      Left            =   120
      TabIndex        =   4
      Top             =   120
      Width           =   2055
      Begin VB.ComboBox Combo1 
         BackColor       =   &H80000018&
         Height          =   300
         Index           =   0
         Left            =   120
         Style           =   2  'Dropdown List
         TabIndex        =   5
         Top             =   195
         Width           =   1815
      End
   End
   Begin VB.Frame Frame3 
      Caption         =   "班级："
      Height          =   615
      Left            =   2160
      TabIndex        =   2
      Top             =   120
      Width           =   1695
      Begin VB.ComboBox Combo1 
         BackColor       =   &H80000018&
         Height          =   300
         Index           =   1
         Left            =   120
         Style           =   2  'Dropdown List
         TabIndex        =   3
         Top             =   200
         Width           =   1455
      End
   End
   Begin VB.Frame Frame7 
      Caption         =   "学期："
      Height          =   615
      Left            =   3840
      TabIndex        =   0
      Top             =   120
      Width           =   3255
      Begin VB.ComboBox Combo1 
         BackColor       =   &H80000018&
         Height          =   300
         Index           =   2
         Left            =   120
         Style           =   2  'Dropdown List
         TabIndex        =   1
         Top             =   200
         Width           =   3015
      End
   End
End
Attribute VB_Name = "frmcj1"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Private Sub Combo1_Click(Index As Integer)
    Dim mrc As ADODB.Recordset
    If Index = 0 Then
        txtsql = "select   DISTINCT 班级 from class where 年级='" & _
            Trim(Combo1(0).Text) & "'"
        Set mrc = ExecuteSQL(txtsql)
        If mrc.EOF = True Then
            Combo1(1).Text = ""
            Combo1(2).Text = ""
            Exit Sub
        End If
        Combo1(1).Clear
        mrc.MoveFirst
        Do Until mrc.EOF
            Combo1(1).AddItem mrc.Fields(0)
            mrc.MoveNext
        Loop
        Combo1(1).ListIndex = 0
    End If
    Set mrc = Nothing
    MSF1.Clear
    cjfilldata
End Sub
Private Sub Combo1_DropDown(Index As Integer)
    If Index = 0 Then
        Dim mrc As ADODB.Recordset
        txtsql = "select  DISTINCT 年级  from class "
        Set mrc = ExecuteSQL(txtsql)
        mrc.MoveFirst
        Combo1(0).Clear
        Do While Not mrc.EOF
            Combo1(0).AddItem mrc.Fields(0)
            mrc.MoveNext
        Loop
        Combo1(0).ListIndex = 0
    End If
    Set mrc = Nothing
End Sub

Private Sub Form_Activate()
    cjfilldata
End Sub
Private Sub Form_Load()
    Dim mrc As ADODB.Recordset
    txtsql = "select  DISTINCT 年级  from class "
    Set mrc = ExecuteSQL(txtsql)
    mrc.MoveFirst
    Combo1(0).Clear
    Do While Not mrc.EOF
        Combo1(0).AddItem mrc.Fields(0)
        mrc.MoveNext
    Loop
    Combo1(0).ListIndex = 0
    txtsql = "select   DISTINCT 班级 from class where 年级='" & _
        Trim(Combo1(0).Text) & "'"
    Set mrc = ExecuteSQL(txtsql)
    Combo1(1).Clear
    mrc.MoveFirst
    Do While Not mrc.EOF
        Combo1(1).AddItem mrc.Fields(0)
        mrc.MoveNext
    Loop
    Combo1(1).ListIndex = 0
    Combo1(2).AddItem Val(Format(Date, "yyyy")) - 1 & "---" & _
        Val(Format(Date, "yyyy")) & "年级第一学期"
    Combo1(2).AddItem Val(Format(Date, "yyyy")) - 1 & "---" & _
        Val(Format(Date, "yyyy")) & "年级第二学期"
    Combo1(2).AddItem Format(Date, "yyyy") & "---" & _
        Val(Format(Date, "yyyy")) + 1 & "年级第一学期"
    Combo1(2).AddItem Format(Date, "yyyy") & "---" & _
        Val(Format(Date, "yyyy")) + 1 & "年级第二学期"
    Combo1(2).AddItem Val(Format(Date, "yyyy")) + 1 & "---" & _
        Val(Format(Date, "yyyy")) + 2 & "年级第一学期"
    Combo1(2).AddItem Val(Format(Date, "yyyy")) + 1 & "---" & _
        Val(Format(Date, "yyyy")) + 2 & "年级第二学期"
    If Val(Format(Date, "mm")) > 8 Then
        Combo1(2).ListIndex = 2
    Else
        Combo1(2).ListIndex = 1
    End If
    txtsql = "select   * from kaoshileixing"
    Set mrc = ExecuteSQL(txtsql)
    Combo1(3).Clear
    mrc.MoveFirst
    Do While Not mrc.EOF
        Combo1(3).AddItem mrc.Fields(0)
        mrc.MoveNext
    Loop
    Combo1(3).ListIndex = 0
    Set mrc = Nothing
    cjfilldata
End Sub
Public Sub cjfilldata()
    Dim mrc1 As ADODB.Recordset
    Dim mrc2 As ADODB.Recordset
    Dim g As Integer
    Dim sum As Single
    txtsql = "select distinct 学号 from cj where 学号 in(select 学号 from xj " & _
        " where 班级='" & Trim(Combo1(1).Text) & "')  and 学期='" & _
        Trim(Combo1(2).Text) & "' and 类型='" & Trim(Combo1(3).Text) & _
        "'order by cj.学号"
    Set mrc1 = ExecuteSQL(txtsql)
    If mrc1.EOF = True Then
        Exit Sub
    Else
    End If
    sum = 0
    For i = 1 To mrc1.RecordCount
        txtsql = "select cj.学号,xj.姓名,cj.学期,cj.类型,cj.课程名称,cj.分数 " & _
            " from cj inner join xj on cj.学号=xj.学号 where cj.学期='" & _
            Trim(Combo1(2).Text) & "' and cj.学号='" & Trim(mrc1.Fields(0)) & _
            "'and cj.类型='" & Trim(Combo1(3).Text) & "'"
        Set mrc2 = ExecuteSQL(txtsql)
        If i = 1 Then
            MSF1.Cols = mrc2.RecordCount + 7
            MSF1.TextMatrix(0, 1) = "学号"
            MSF1.TextMatrix(0, 2) = "姓名"
            MSF1.TextMatrix(0, 3) = "学期"
            MSF1.TextMatrix(0, 4) = "类型"
            g = 5
            For j = 1 To mrc2.RecordCount
                MSF1.TextMatrix(0, g) = mrc2.Fields("课程名称")
                g = g + 1
                mrc2.MoveNext
            Next j
            MSF1.TextMatrix(0, g) = "总分"
            MSF1.TextMatrix(0, g + 1) = "平均分"
            MSF1.ColWidth(0) = 150
            MSF1.ColWidth(3) = 2300
            For r = 1 To mrc2.RecordCount + 6
                MSF1.ColAlignment(r) = 0
            Next r
            mrc2.MoveFirst
            MSF1.Rows = 30
            MSF1.Row = 1
        End If
        MSF1.Rows = MSF1.Rows + 1
        MSF1.TextMatrix(MSF1.Row, 1) = mrc2.Fields(0)
        MSF1.TextMatrix(MSF1.Row, 2) = mrc2.Fields(1)
        MSF1.TextMatrix(MSF1.Row, 3) = mrc2.Fields(2)
        MSF1.TextMatrix(MSF1.Row, 4) = mrc2.Fields(3)
        g = 5
        sum = 0
        For j = 1 To mrc2.RecordCount
            MSF1.Col = g
            If Val(mrc2.Fields("分数")) < 60 Then
                MSF1.CellForeColor = vbRed
            End If
            MSF1.TextMatrix(MSF1.Row, g) = mrc2.Fields("分数")
            sum = sum + mrc2.Fields("分数")
            g = g + 1
            mrc2.MoveNext
        Next j
        MSF1.TextMatrix(MSF1.Row, g) = sum
        Dim avg1 As Single
        avg1 = Int(sum / (mrc2.RecordCount) * 10 + 0.5) / 10
        MSF1.Col = g + 1
        If avg1 < 60 Then
            MSF1.CellForeColor = vbRed
        End If
        MSF1.TextMatrix(MSF1.Row, g + 1) = avg1
        MSF1.Row = MSF1.Row + 1
        mrc1.MoveNext
    Next i
End Sub