frmmdfiscore.frm

基于vb的程序管理系统

      EndProperty
      BorderStyle     =   1
   End
   Begin MSComctlLib.ListView lsvMdfiScore 
      Height          =   3165
      Left            =   240
      TabIndex        =   3
      Top             =   2040
      Width           =   9000
      _ExtentX        =   15875
      _ExtentY        =   5583
      LabelEdit       =   1
      LabelWrap       =   0   'False
      HideSelection   =   0   'False
      GridLines       =   -1  'True
      _Version        =   393217
      ForeColor       =   -2147483640
      BackColor       =   -2147483643
      BorderStyle     =   1
      Appearance      =   1
      BeginProperty Font {0BE35203-8F91-11CE-9DE3-00AA004BB851} 
         Name            =   "宋体"
         Size            =   9
         Charset         =   134
         Weight          =   700
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      NumItems        =   0
   End
End
Attribute VB_Name = "frmMdfiScore"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Option Explicit
    
Private Sub Form_Load()
    Call HeadList
    mCnnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\StuScore.mdb" & ";Persist Security Info=False"
    'MDIfrmMain.Show
End Sub
'加载科目，加载列表头
Private Sub HeadList()
On Error GoTo mErr
    Dim mRst As New ADODB.Recordset
    Dim mCItem As ComboItem
    lsvMdfiScore.ListItems.Clear
        With lsvMdfiScore.ColumnHeaders
            .Add , , "学生学号", 1200
            .Add , , "学生姓名", 980
            mRst.Open "SELECT * FROM tblLesson ORDER BY 课程号", mCnnString, adOpenKeyset, adLockPessimistic, adCmdText
            Do Until mRst.EOF
                .Add , , mRst("课程名称"), 800
                Set mCItem = imgcbLesnName.ComboItems.Add(, , mRst("课程名称"))
                mCItem.Tag = mRst("课程ID")
                mRst.MoveNext
            Loop
            .Add , , "总分", 800
            .Add , , "平均分", 800
        End With
    lsvMdfiScore.View = lvwReport
    Exit Sub
mErr:
    MsgBox Err.Number & "," & Err.Description, vbCritical + vbOKOnly, mTitle
    End
End Sub

Private Sub imgcbLesnName_Click()
    If Not lsvMdfiScore.SelectedItem Is Nothing Then
        txtStuScore.Text = lsvMdfiScore.SelectedItem.SubItems(1 + imgcbLesnName.SelectedItem.Index)
        txtStuScore.SetFocus
        txtStuScore.SelLength = Len(txtStuScore.Text)
    End If
End Sub

Private Sub lsvMdfiScore_ItemClick(ByVal Item As MSComctlLib.ListItem)
    If Not lsvMdfiScore.SelectedItem Is Nothing Then
        If imgcbLesnName.Text <> "" Then txtStuScore.Text = lsvMdfiScore.SelectedItem.SubItems(1 + imgcbLesnName.SelectedItem.Index)
        Call DataToLabel
    End If
End Sub

Private Sub tbrMdfiScore_ButtonClick(ByVal Button As MSComctlLib.Button)
    Select Case Button.Key
        Case "查找"
            SeltFrom = 2
            frmFindStu.Show
            If Not lsvMdfiScore.SelectedItem Is Nothing Then Call DataToLabel
        Case "保存"
            If Not lsvMdfiScore.SelectedItem Is Nothing Then Call SaveData
        Case "全部显示"
            Call DataToList("SELECT * FROM tblStudent ORDER BY 学生学号")
            txtStuScore.Text = ""
        Case "清空"
            Call ClearAll
            Call ClearLabel
        Case "退出"
            Unload Me
    End Select
End Sub
'当已在科目下拉列表框中选择了科目且在成绩列表中选择了某一条记录，其中的输入分数改变时，成绩列表中相应选中的项的
'成绩会随之改变，且“总分”、“平均分”也会随之改变
Private Sub txtStuScore_Change()
    Dim mLItem As ListItem
    Dim i As Long
    Dim Sum As Long
    Set mLItem = lsvMdfiScore.SelectedItem
        If Not lsvMdfiScore.SelectedItem Is Nothing And IsNumeric(txtStuScore.Text) Then
            If imgcbLesnName.Text <> "" Then
                With lsvMdfiScore.SelectedItem
                    .SubItems(imgcbLesnName.SelectedItem.Index + 1) = Trim(txtStuScore.Text)
                    Sum = 0
                    For i = 2 To imgcbLesnName.ComboItems.Count + 1
                        Sum = Sum + mLItem.SubItems(i)
                    Next i
                    .SubItems(imgcbLesnName.ComboItems.Count + 2) = Sum
                    .SubItems(imgcbLesnName.ComboItems.Count + 3) = Format(Sum / imgcbLesnName.ComboItems.Count, "#0.0")
                End With
            End If
        End If
End Sub
'保存成绩时，如果在tblScore中找到不到相应的记录，这说明它是新增，在tblScore表中为其加一条记录
Private Sub SaveData()
On Error GoTo mErr
    Dim mRstA As New ADODB.Recordset
    Dim mRstB As New ADODB.Recordset
    Dim mLItem As ListItem
    Dim mCItem As ComboItem
    Dim k As Long
    Dim i As Long
    For k = 1 To imgcbLesnName.ComboItems.Count
        Set mCItem = imgcbLesnName.ComboItems.Item(k)
        mRstA.Open "SELECT * FROM tblScore WHERE 课程ID = " & CStr(mCItem.Tag), mCnnString, adOpenKeyset, adLockPessimistic, adCmdText
        If mRstA.RecordCount = 0 Then
            mRstA.Close
            mRstA.Open "SELECT DISTINCT 学生ID FROM tblScore", mCnnString, adOpenKeyset, adLockPessimistic, adCmdText
            Do Until mRstA.EOF
                mRstB.Open "INSERT INTO tblScore(学生ID,课程ID,成绩) VALUES(" & CLng(mRstA("学生ID")) & "," & CStr(mCItem.Tag) & ",0)", mCnnString, adOpenKeyset, adLockPessimistic, adCmdText
                mRstB.Close
                mRstA.MoveNext
            Loop
        End If
        mRstA.Close
    Next k
    For i = 1 To lsvMdfiScore.ListItems.Count
        Set mLItem = lsvMdfiScore.ListItems(i)
        For k = 1 To imgcbLesnName.ComboItems.Count
            Set mCItem = imgcbLesnName.ComboItems.Item(k)
            With mRstA
                .Open "SELECT * FROM tblScore WHERE 学生ID = " & CStr(mLItem.Tag) & " AND 课程ID = " & CStr(mCItem.Tag), mCnnString, adOpenKeyset, adLockPessimistic, adCmdText
                If .RecordCount = 0 Then
                    .AddNew
                    .Fields("学生ID") = CStr(mLItem.Tag)
                    .Fields("课程ID") = CStr(mCItem.Tag)
                    .Fields("成绩") = mLItem.SubItems(k + 1)
                    .Update
                    .Close
                Else
                    .Fields("成绩") = mLItem.SubItems(k + 1)
                    .Update
                    .Close
                End If
            End With
        Next k
    Next i
    Set mRstA = Nothing
    Exit Sub
mErr:
    MsgBox Err.Number & "," & Err.Description, vbCritical + vbOKOnly, mTitle
    End
End Sub
'从tblStudent表中读取“学生学号”、“学生姓名”到列表中相应记录，从tblScore中读取各门功课成绩，计算总分和平均分
Public Sub DataToList(mStr As String)
On Error GoTo mErr
    Dim mRstA As New ADODB.Recordset
    Dim mRstB As New ADODB.Recordset
    Dim mLItem As ListItem
    Dim i As Long
    lsvMdfiScore.ListItems.Clear
    mRstA.Open mStr, mCnnString, adOpenKeyset, adLockPessimistic, adCmdText
    Do Until mRstA.EOF
        Set mLItem = lsvMdfiScore.ListItems.Add(, , mRstA("学生学号"))
        With mLItem
            .SubItems(1) = mRstA("学生姓名")
            .Tag = mRstA("学生ID")
            For i = 1 To imgcbLesnName.ComboItems.Count
                mRstB.Open "SELECT 成绩 FROM tblScore WHERE 学生ID = " & CLng(mRstA("学生ID")) & " AND 课程ID = " & CLng(imgcbLesnName.ComboItems(i).Tag), mCnnString, adOpenKeyset, adLockPessimistic, adCmdText
                If mRstB.RecordCount <> 0 Then
                    .SubItems(i + 1) = mRstB("成绩")
                Else
                    .SubItems(i + 1) = 0
                End If
                mRstB.Close
            Next i
            mRstB.Open "SELECT SUM(成绩) AS sumzf FROM tblScore WHERE 学生ID = " & CLng(.Tag), mCnnString, adOpenKeyset, adLockPessimistic, adCmdText
            If mRstB("sumzf").Value >= 0 Then
                .SubItems(i + 1) = mRstB("sumzf").Value
                .SubItems(i + 2) = Format(mRstB("sumzf").Value / imgcbLesnName.ComboItems.Count, "##0.0")
            Else
                .SubItems(i + 1) = 0
                .SubItems(i + 2) = 0
            End If
        End With
        mRstB.Close
        Set mRstB = Nothing
        mRstA.MoveNext
    Loop
    mRstA.Close
    Set mRstA = Nothing
    Call DataToLabel
    Exit Sub
mErr:
    MsgBox Err.Number & "," & Err.Description, vbCritical + vbOKOnly, mTitle
    End
End Sub
'把列表中相应记录的学号和学生姓名显示到两个label中
Private Sub DataToLabel()
    lblDispNum.Caption = lsvMdfiScore.SelectedItem.Text
    lblDispName.Caption = lsvMdfiScore.SelectedItem.SubItems(1)
End Sub

Private Sub ClearLabel()
    lblDispNum.Caption = ""
    lblDispName.Caption = ""
End Sub

Private Sub ClearAll()
    imgcbLesnName.Text = ""
    txtStuScore.Text = ""
    lsvMdfiScore.ListItems.Clear
End Sub

Private Sub Form_Resize()
    If frmMdfiScore.WindowState <> 1 Then
        lsvMdfiScore.Move lsvMdfiScore.Left, lsvMdfiScore.Top, Me.ScaleWidth - lsvMdfiScore.Left - 100, Me.ScaleHeight - lsvMdfiScore.Top - 100
    End If
End Sub

Private Sub txtStuScore_KeyDown(KeyCode As Integer, Shift As Integer)
    If Not lsvMdfiScore.SelectedItem Is Nothing And imgcbLesnName.Text <> "" Then
        If KeyCode = 13 And lsvMdfiScore.SelectedItem.Index <> lsvMdfiScore.ListItems.Count Then
            lsvMdfiScore.ListItems.Item(lsvMdfiScore.SelectedItem.Index + 1).Selected = True
            lsvMdfiScore.SelectedItem.EnsureVisible
            txtStuScore.Text = lsvMdfiScore.SelectedItem.SubItems(1 + imgcbLesnName.SelectedItem.Index)
            txtStuScore.SelLength = Len(txtStuScore.Text)
        End If
    End If
End Sub