frmstudent.frm

上机考试系统

Private Sub Form_Load()
    Dim objTemp As New Recordset
    '建立数据库联接
    Set objCn = New Connection                 '实例化联接对象
    With objCn                                 '建立数据库联接
        .Provider = "SQLOLEDB"
        .ConnectionString = "User ID=sa;PWD=123;Data Source=(local);" & _
                            "Initial Catalog=自测考试"
        .Open
    End With
    '获取学生信息记录
    Set objStudent = New Recordset                 '实例化objStudent对象
    With objStudent
        Set .ActiveConnection = objCn
        .CursorLocation = adUseClient           '指定使用客户端游标
        .CursorType = adOpenStatic              '指定使用静态游标
        .LockType = adLockOptimistic
        .Open "SELECT * FROM 学生信息"          '获取学生信息登录信息
    End With
    '获取班级信息
    With objTemp
        Set .ActiveConnection = objCn
        .CursorLocation = adUseClient           '指定使用客户端游标
        .CursorType = adOpenStatic              '指定使用静态游标
        .Open "SELECT * FROM 班级信息"       '获取班级信息数据
        '建立班级名称列表
        cmbClass.AddItem ""
        cmbClass.ItemData(cmbClass.NewIndex) = 0
        While Not .EOF
            cmbClass.AddItem .Fields("名称")
            cmbClass.ItemData(cmbClass.NewIndex) = Val(.Fields("编号"))
            .MoveNext
        Wend
    End With
    '获取系部信息
    With objTemp
        .Close
        .Open "SELECT * FROM 系部信息"       '获取系部信息数据
        '建立系部名称列表
        cmbDept.AddItem ""
        cmbDept.ItemData(cmbDept.NewIndex) = 0
        While Not .EOF
            cmbDept.AddItem .Fields("名称")
            cmbDept.ItemData(cmbDept.NewIndex) = Val(.Fields("编号"))
            .MoveNext
        Wend
    End With
    '建立性别列表
    cmbSex.AddItem ""
    cmbSex.AddItem "男"
    cmbSex.AddItem "女"
    cmdMove(0).Value = True                     '触发按钮单击事件，显示第一个记录
End Sub

Private Sub cmdMove_Click(Index As Integer)
    With objStudent
        Select Case Index           '切换当前记录
            Case 0                  '使第一个记录成为当前记录
                If .RecordCount > 0 And Not .BOF Then .MoveFirst
            Case 1                  '使上一个记录成为当前记录
                If .RecordCount > 0 And Not .BOF Then
                    .MovePrevious
                    If .BOF Then .MoveFirst
                End If
            Case 2                  '使下一个记录成为当前记录
                If .RecordCount > 0 And Not .EOF Then
                    .MoveNext
                    If .EOF Then .MoveLast
                End If
            Case 3                  '使最后一个记录成为当前记录
                If .RecordCount > 0 And Not .EOF Then .MoveLast
        End Select
        Show_Data
    End With
    If isAdding Then isAdding = False
End Sub

Private Sub cmdAdd_Click()
    txtNews = "添加新记录"
    txtName = ""
    isAdding = True
    txtName.SetFocus
End Sub

Private Sub cmdDelete_Click()
    '根据是否处于添加记录状态执行不同的操作
    If isAdding Then
        '退出添加记录状态，显示当前记录
        isAdding = False
        If objStudent.RecordCount <= 0 Then
            txtNews = "记录：无"    '显示无记录提示
        Else
            Show_Data '显示当前记录数据
        End If
    Else
        If objStudent.RecordCount > 0 Then
            If MsgBox("是否删除当前记录？", vbYesNo + vbQuestion, _
                       "学生信息管理") = vbYes Then
                objStudent.Delete                   '执行删除当前记录操作
                cmdMove(2).Value = True             '显示下一记录数据
            Else
                Show_Data                       '显示当前记录数据
            End If
        End If
    End If
End Sub
Private Sub cmdSave_Click()
    Dim objCopy As New Recordset
    '在当前表中无数据和不是添加记录时，不执行保存操作
    If Not isAdding And objStudent.RecordCount < 1 Then Exit Sub
    If Trim(txtName) = "" Then
        MsgBox "学生姓名不能为空！", vbCritical, "学生信息管理"
        txtName.SetFocus
        txtName = ""
    ElseIf Not txtNum Like "[1-9]#######" Then
        MsgBox "学号必须是以非0数字开头的8位数字字符串！", vbCritical, "学生信息管理"
        txtNum.SetFocus
        txtNum.SelStart = 0
        txtNum.SelLength = 8
    ElseIf Not txtCode Like "[1-9]#######" Then
        MsgBox "考号必须是以非0数字开头的8位数字字符串！", vbCritical, "学生信息管理"
        txtCode.SetFocus
        txtCode.SelStart = 0
        txtCode.SelLength = 8
    ElseIf Trim(cmbSex) = "" Then
        MsgBox "请选择性别！", vbCritical, "学生信息管理"
        cmbSex.SetFocus
    ElseIf Trim(cmbClass) = "" Then
        MsgBox "请选择所属班级！", vbCritical, "学生信息管理"
        cmbClass.SetFocus
    ElseIf Trim(cmbDept) = "" Then
        MsgBox "请选择所属系部！", vbCritical, "学生信息管理"
        cmbDept.SetFocus
    ElseIf Not IsDate(txtTime) Then
        MsgBox "请输入有效的考试时间！", vbCritical, "学生信息管理"
        txtTime.SetFocus
        txtTime.SelStart = 0
        txtTime.SelLength = Len(txtTime)
    ElseIf Trim(txtRoom) = "" Then
        MsgBox "请输入考试教室！", vbCritical, "学生信息管理"
        txtRoom.SetFocus
        txtRoom = ""
    Else
        Set objCopy = objStudent.Clone
        With objCopy
            If .RecordCount > 0 Then
                '检查学生学号是否重复
                .MoveFirst
                .Find "学号='" & Trim(txtNum) & "'"
                If (isAdding And Not .EOF) Or _
                    (Not isAdding And Not .EOF And _
                     .AbsolutePosition <> objStudent.AbsolutePosition) Then
                    MsgBox "学号重复，请修改！", vbCritical, "学生信息管理"
                    txtNum.SetFocus
                    txtNum.SelStart = 0
                    txtNum.SelLength = Len(txtNum)
                    Exit Sub
                End If
                '检查学生考号是否重复
                .MoveFirst
                .Find "考号='" & Trim(txtCode) & "'"
                If (isAdding And Not .EOF) Or _
                    (Not isAdding And Not .EOF And _
                     .AbsolutePosition <> objStudent.AbsolutePosition) Then
                    MsgBox "考号重复，请修改！", vbCritical, "学生信息管理"
                    txtCode.SetFocus
                    txtCode.SelStart = 0
                    txtCode.SelLength = Len(txtCode)
                    Exit Sub
                End If
                If isAdding Then objStudent.AddNew      '保存或添加新记录
            Else
                If isAdding Then objStudent.AddNew      '添加新记录
            End If
            objStudent.Fields("姓名") = Trim(txtName)
            objStudent.Fields("学号") = Trim(txtNum)
            objStudent.Fields("考号") = Trim(txtCode)
            objStudent.Fields("性别") = cmbSex
            objStudent.Fields("班级") = cmbClass.ItemData(cmbClass.ListIndex)
            objStudent.Fields("系部") = cmbDept.ItemData(cmbDept.ListIndex)
            objStudent.Fields("时间") = txtTime
            objStudent.Fields("教室") = txtRoom
            objStudent.Update
            MsgBox "数据保存成功！", vbInformation, "学生信息管理"
            isAdding = False
            '显示当前记录编号和记录总数
            txtNews = "记录：" & objStudent.AbsolutePosition & "/" & objStudent.RecordCount
        End With
    End If
End Sub

Private Sub Form_Terminate()

End Sub

Private Sub Form_Unload(Cancel As Integer)
    objCn.Close                 '关闭数据联接
    Set objCn = Nothing         '释放数据库联接
    Set objStudent = Nothing      '释放记录集对象
End Sub
'限制联系电话输入
Private Sub txtPhone_KeyPress(KeyAscii As Integer)
    '如果敲回车键，使下一控件获得焦点
    If KeyAscii = vbKeyReturn Then SendKeys "{TAB}"
    If Not (Chr(KeyAscii) Like "[0-9]" Or KeyAscii = vbKeyBack) Then
        KeyAscii = 0    '输入不是数字或退格键，取消输入
    End If
End Sub
Private Sub txtCode_KeyPress(KeyAscii As Integer)
    '如果敲回车键，使下一控件获得焦点
    If KeyAscii = vbKeyReturn Then SendKeys "{TAB}"
End Sub
Private Sub txtName_KeyPress(KeyAscii As Integer)
    '如果敲回车键，使下一控件获得焦点
    If KeyAscii = vbKeyReturn Then SendKeys "{TAB}"
End Sub
Private Sub txtSum_KeyPress(KeyAscii As Integer)
    '如果敲回车键，使保存按钮获得焦点
    If KeyAscii = vbKeyReturn Then cmdSave.SetFocus
End Sub

Private Sub Show_Data()
    Dim i%
    With objStudent
        If .RecordCount < 1 Then
            txtNews = "记录：无"    '显示无记录提示
            '清除显示数据
            txtName = "": txtNum = ""
            txtCode = "": txtTime = "": txtRoom = ""
            cmbSex.ListIndex = 0: cmbClass.ListIndex = 0
            cmbDept.ListIndex = 0
        Else
            '显示当前记录数据
            txtName = .Fields("姓名"): txtNum = .Fields("学号")
            txtCode = .Fields("考号"): txtTime = .Fields("时间")
            txtRoom = .Fields("教室")
            '显示性别
            If .Fields("性别") = "男" Then
                cmbSex.ListIndex = 1
            Else
                cmbSex.ListIndex = 2
            End If
            '显示班级名称
            For i = 1 To cmbClass.ListCount - 1
                If Val(.Fields("班级")) = cmbClass.ItemData(i) Then Exit For
            Next
            cmbClass.ListIndex = i
            '显示系部名称
            For i = 1 To cmbDept.ListCount - 1
                If Val(.Fields("系部")) = cmbDept.ItemData(i) Then Exit For
            Next
            cmbDept.ListIndex = i
            '显示当前记录编号和记录总数
            txtNews = "记录：" & .AbsolutePosition & "/" & .RecordCount
        End If
    End With
End Sub
Private Sub txtNum_KeyPress(KeyAscii As Integer)
    '如果敲回车键，使下一控件获得焦点
    If KeyAscii = vbKeyReturn Then SendKeys "{TAB}"
End Sub

Private Sub txtRoom_KeyPress(KeyAscii As Integer)
    '如果敲回车键，使保存按钮获得焦点
    If KeyAscii = vbKeyReturn Then cmdSave.SetFocus
End Sub

Private Sub txtTime_KeyPress(KeyAscii As Integer)
    '如果敲回车键，使下一控件获得焦点
    If KeyAscii = vbKeyReturn Then SendKeys "{TAB}"
End Sub