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()
    Unload frmFindLesn
    Unload frmFindStu
    Unload frmLesnIfm
    Unload frmStuPlace
    Unload frmStuIfm
    If seltfrom1 = 2 Then
      imgcbLesnName.Enabled = False
      txtStuScore.Enabled = False
        Else: txtStuScore.Enabled = True
             imgcbLesnName.Enabled = True
    End If
    Call HeadList
    mCnnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\StuScore.mdb" & ";Persist Security Info=False"
   
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("課程名稱")) ' ddddd
                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 seltfrom1 = 1 Then
              If Not lsvMdfiScore.SelectedItem Is Nothing Then Call SaveData
            End If
        Case "全部顯示"
            If seltfrom1 = 1 Then
              Call DataToList("SELECT * FROM tblStudent ORDER BY 學生學號")
              txtStuScore.Text = ""
            End If
        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 '定義ado
    
    Dim mRstB As New ADODB.Recordset '定義ado
    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