frmclasscourse.frm

学生信息管理系统

            comClassCourse(2).AddItem objRecordset.Fields(0)
            objRecordset.MoveNext
        Loop
        comClassCourse(2).ListIndex = 0
    '选择专业
    ElseIf Index = 1 Then
        '查询学制
        strSQL = "select   DISTINCT 年制 from Classes where 年级='" & Trim(comClassCourse(0).Text) & "' and 专业='" & Trim(comClassCourse(1).Text) & "'"
        Set objRecordset = ExecuteSQL(strSQL)
        If objRecordset.EOF = True Then
            comClassCourse(0).Text = ""
            comClassCourse(2).Text = ""
            Exit Sub
        End If
        '显示学制
        comClassCourse(2).Clear
        objRecordset.MoveFirst
        Do Until objRecordset.EOF
            comClassCourse(2).AddItem objRecordset.Fields(0)
            objRecordset.MoveNext
        Loop
        comClassCourse(2).ListIndex = 0
    End If
End Sub

'下拉选择年级
Private Sub comClassCourse_DropDown(Index As Integer)
    If Index = 0 Then
        Dim objRecordset As ADODB.Recordset
        
        '选择年级
        strSQL = "select  DISTINCT 年级  from Classes "
        Set objRecordset = ExecuteSQL(strSQL)
        objRecordset.MoveFirst
        comClassCourse(0).Clear
        Do While Not objRecordset.EOF
            comClassCourse(0).AddItem objRecordset.Fields(0)
            objRecordset.MoveNext
        Loop
        comClassCourse(0).ListIndex = 0
    End If
End Sub

'确定选择
Private Sub cmdOK_Click()
    Dim i As Integer
    Dim objRecordset As ADODB.Recordset
    
    strUserManage = UserManage(4)
    If strUserManage = "ReadOnly" Then
        MsgBox "对不起，你是只读用户不能添加记录，请与管理员联系！", vbInformation + vbOKOnly, " 警告"
        Exit Sub
    End If
    
    If lstChosenCourse.ListCount = 0 Then
        MsgBox "你还没有选择课程！", vbInformation + vbOKOnly, "警告"
        Exit Sub
    End If
    
    '查询相关数据库
    strSQL = "select * from ClassCourses where 年级='" & Trim(comClassCourse(0).Text) & "' and 专业='" & Trim(comClassCourse(1).Text) & "'and 年制='" & Trim(comClassCourse(2).Text) & "' and 学期='" & Trim(comClassCourse(3).Text) & "'"
    Set objRecordset = ExecuteSQL(strSQL)
    If objRecordset.EOF = False Then
        If MsgBox("已存在该班级本学期课程设置和成绩记录，" & Chr(10) & Chr(13) & "继续会导致以上的成绩丢失,继续吗？！", vbOKCancel + vbExclamation, "警告") = vbCancel Then
            Exit Sub
        '打开数据库
        Else
            strSQL = "delete * from ClassCourses where 年级='" & Trim(comClassCourse(0).Text) & "' and 专业='" & Trim(comClassCourse(1).Text) & "'and 年制='" & Trim(comClassCourse(2).Text) & "' and 学期='" & Trim(comClassCourse(3).Text) & "'"
            Set objRecordset = ExecuteSQL(strSQL)
            strSQL = "delete * from Scores where 学号 in(select DISTINCT Documents.学号 from Documents inner join Classes on Documents.班级=Classes.班级 where 年级='" & Trim(comClassCourse(0).Text) & "' and 专业='" & Trim(comClassCourse(1).Text) & "'and 年制='" & Trim(comClassCourse(2).Text) & "')  and 学期='" & Trim(comClassCourse(3).Text) & "'"
            Set objRecordset = ExecuteSQL(strSQL)
        End If
    End If
    
    '将选择的课程保存到数据库中
    strSQL = "select * from ClassCourses"
    Set objRecordset = ExecuteSQL(strSQL)
    For i = 0 To lstChosenCourse.ListCount - 1
        objRecordset.AddNew
        objRecordset.Fields(0) = Trim(comClassCourse(0).Text)
        objRecordset.Fields(1) = Trim(comClassCourse(1).Text)
        objRecordset.Fields(2) = Trim(comClassCourse(2).Text)
        objRecordset.Fields(3) = Trim(comClassCourse(3).Text)
        objRecordset.Fields(4) = Trim(lstChosenCourse.List(i))
        objRecordset.Update
    Next
    MsgBox "课程设置成功！", vbInformation + vbOKOnly, "提示"
End Sub

Private Sub cmdCancel_Click()
    Unload Me
End Sub

'选择课程
Private Sub cmdChoose_Click()
    Dim i As Integer
    
    i = 0
    '将可选课程移动到已选列表，并在可选列表中移除该选项
    Do While i < lstAllCourse.ListCount
        If lstAllCourse.Selected(i) = True Then
            lstChosenCourse.AddItem lstAllCourse.List(i)
            lstAllCourse.RemoveItem i
        Else
            i = i + 1
        End If
    Loop
    
    '设置移动按钮可用性
    If lstAllCourse.ListCount = 0 Then
        cmdChoose.Enabled = False
    End If
    If lstChosenCourse.ListCount <> 0 Then
        cmdRemove.Enabled = True
    End If
End Sub

'移除已选课程
Private Sub cmdRemove_Click()
    Dim i As Integer
    
    i = 0
    '将已选课程移动到可选列表，并在已选列表中移除该选项
    Do While i < lstChosenCourse.ListCount
        If lstChosenCourse.Selected(i) = True Then
            lstAllCourse.AddItem lstChosenCourse.List(i)
            lstChosenCourse.RemoveItem i
        Else
            i = i + 1
        End If
    Loop
    
    '设置移动按钮可用性
    If lstAllCourse.ListCount <> 0 Then
        cmdChoose.Enabled = True
    End If
    If lstChosenCourse.ListCount = 0 Then
        cmdRemove.Enabled = False
    End If
End Sub

'窗体加载时初始化控件
Private Sub Form_Load()
    Dim objRecordset As ADODB.Recordset
    
    '初始化年级信息
    strSQL = "select  DISTINCT 年级  from Classes "
    Set objRecordset = ExecuteSQL(strSQL)
    objRecordset.MoveFirst
    comClassCourse(0).Clear
    Do While Not objRecordset.EOF
        comClassCourse(0).AddItem objRecordset.Fields(0)
        objRecordset.MoveNext
    Loop
    comClassCourse(0).ListIndex = 0
    
    '初始化专业信息
    strSQL = "select   DISTINCT 专业 from Classes where 年级='" & Trim(comClassCourse(0).Text) & "'"
    Set objRecordset = ExecuteSQL(strSQL)
    comClassCourse(1).Clear
    objRecordset.MoveFirst
    Do While Not objRecordset.EOF
        comClassCourse(1).AddItem objRecordset.Fields(0)
        objRecordset.MoveNext
    Loop
    comClassCourse(1).ListIndex = 0
    
    '初始化学制信息
    strSQL = "select   DISTINCT 年制 from Classes where 年级='" & Trim(comClassCourse(0).Text) & "' and 专业='" & Trim(comClassCourse(1).Text) & "'"
    comClassCourse(2).Clear
    Set objRecordset = ExecuteSQL(strSQL)
    objRecordset.MoveFirst
    Do While Not objRecordset.EOF
        comClassCourse(2).AddItem objRecordset.Fields(0)
        objRecordset.MoveNext
    Loop
    comClassCourse(2).ListIndex = 0
    objRecordset.Close
    
    '初始化学期信息
    comClassCourse(3).AddItem Val(Format(Date, "yyyy")) - 1 & "---" & Val(Format(Date, "yyyy")) & "年级第一学期"
    comClassCourse(3).AddItem Val(Format(Date, "yyyy")) - 1 & "---" & Val(Format(Date, "yyyy")) & "年级第二学期"
    comClassCourse(3).AddItem Format(Date, "yyyy") & "---" & Val(Format(Date, "yyyy")) + 1 & "年级第一学期"
    comClassCourse(3).AddItem Format(Date, "yyyy") & "---" & Val(Format(Date, "yyyy")) + 1 & "年级第二学期"
    comClassCourse(3).AddItem Val(Format(Date, "yyyy")) + 1 & "---" & Val(Format(Date, "yyyy")) + 2 & "年级第一学期"
    comClassCourse(3).AddItem Val(Format(Date, "yyyy")) + 1 & "---" & Val(Format(Date, "yyyy")) + 2 & "年级第二学期"
    If Val(Format(Date, "mm")) > 8 Then
        comClassCourse(3).ListIndex = 2
    Else
        comClassCourse(3).ListIndex = 1
    End If
    
    '将课程名显示在列中
    lstAllCourse.Clear
    lstChosenCourse.Clear
    strSQL = "select 课程名称 from Courses "
    Set objRecordset = ExecuteSQL(strSQL)
    If objRecordset.EOF = True Then
        Exit Sub
    End If
    objRecordset.MoveFirst
    Do Until objRecordset.EOF
        lstAllCourse.AddItem objRecordset.Fields(0)
        objRecordset.MoveNext
    Loop
    Set objRecordset = Nothing
    
    '设置课程选择按钮的可用性
    If lstAllCourse.ListCount = 0 Then
        cmdChoose.Enabled = False
    Else
        cmdChoose.Enabled = True
    End If
    
    If lstChosenCourse.ListCount = 0 Then
        cmdRemove.Enabled = False
    Else
        cmdRemove.Enabled = True
    End If
End Sub