applyform.frm

运动会管理系统 运动会管理系统

Public Sub init()
    sexCombo.AddItem "男"
    sexCombo.AddItem "女"
    
    strSql = "select class_info_name from class_info"
    Set rs = ExecuteSQL(Trim(strSql))
    If rs.EOF = False Then
        rs.MoveFirst
        Do While Not rs.EOF
            classCombo.AddItem rs.Fields(0)
            rs.MoveNext
        Loop
        rs.Close
    End If
    
    strSql = "select sport_item_id,sport_item_name from sport_item"
    Set rs = ExecuteSQL(Trim(strSql))
    If rs.EOF = False Then
        rs.MoveFirst
        Do While Not rs.EOF
            sportCombo.AddItem rs.Fields(1)
            rs.MoveNext
        Loop
        rs.Close
    End If
End Sub

Private Sub addBnt_Click()
    Dim rs As ADODB.Recordset
    Dim rs1 As ADODB.Recordset
    Dim rs2 As ADODB.Recordset
    Dim rs3 As ADODB.Recordset
    If sexCombo.Text = "" Then
        MsgBox "性别不能为空！", vbExclamation + vbOKOnly, "警告"
        Exit Sub
    End If
    If txtAge.Text = "" Then
        MsgBox "年龄不能为空！", vbExclamation + vbOKOnly, "警告"
        Exit Sub
    End If
    If classCombo.Text = "" Then
        MsgBox "班级不能为空！", vbExclamation + vbOKOnly, "警告"
        Exit Sub
    Else
        strSql = "select class_info_id from class_info where class_info_name = '" & Trim(classCombo.Text) & "'"
        Set rs = ExecuteSQL(Trim(strSql))
        If rs.EOF = False Then
            rs.MoveFirst
            clsId = rs.Fields(0)
            rs.Close
        Else
            MsgBox "班级不对！", vbExclamation + vbOKOnly, "警告"
            Exit Sub
        End If
    End If
    If sportCombo.Text = "" Then
        MsgBox "参加项目不能为空！", vbExclamation + vbOKOnly, "警告"
        Exit Sub
    Else
        strSql = "select sport_item_id from sport_item where sport_item_name = '" & Trim(sportCombo.Text) & "'"
        Set rs3 = ExecuteSQL(Trim(strSql))
        If rs3.EOF = False Then
            rs3.MoveFirst
            sptId = rs3.Fields(0)
            rs3.Close
        Else
            MsgBox "项目名不对！", vbExclamation + vbOKOnly, "警告"
            Exit Sub
        End If
    End If
    If txtSNo.Text = "" Then
        MsgBox "学号不能为空！", vbExclamation + vbOKOnly, "警告"
        Exit Sub
    End If
    If txtName.Text = "" Then
        MsgBox "姓名不能为空！", vbExclamation + vbOKOnly, "警告"
        Exit Sub
    Else
        strSql = "select student_info_id from student_info where student_info_name = '" & txtName.Text & "' and student_info_no = " & txtSNo.Text
        
        Set rs1 = ExecuteSQL(Trim(strSql))
        
        If rs1.EOF = False Then
            rs1.MoveFirst
            stdId = rs1.Fields(0)
            rs1.Close
        Else
            strSql = "insert into student_info (student_info_no,student_info_name,student_info_sex,class_info_id,student_info_age) values (" & Trim(txtSNo.Text) & ",'" & Trim(txtName.Text) & "','" & Trim(sexCombo.Text) & "'," & Trim(clsId) & "," & Trim(txtAge.Text) & ")"
            If ExecuteUpdateSQL(strSql) = False Then
                MsgBox "添加记录失败,请检查相关格式是否正确！", vbExclamation + vbOKOnly, "警告"
                Exit Sub
            End If
        End If
    End If
    
    strSql = "select student_info_id from student_info where student_info_no = " & txtSNo.Text
    Set rs2 = ExecuteSQL(Trim(strSql))
    If rs2.EOF = True Then
        MsgBox "添加记录失败！", vbExclamation + vbOKOnly, "警告"
        rs2.Close
        Exit Sub
    Else
        rs2.MoveFirst
        stdId = rs2.Fields(0)
        rs2.Close
        strSql = "insert into student_sport (student_info_id,sport_item_id,student_sport_no,student_sport_result,student_sport_rank) values(" & stdId & "," & sptId & ",0,'0',0)"
        If ExecuteUpdateSQL(strSql) = False Then
            MsgBox "添加记录失败！", vbExclamation + vbOKOnly, "警告"
            Exit Sub
        Else
            MsgBox "添加记录成功！", vbExclamation + vbOKOnly, "警告"
        End If
    End If
    Me.classshowdata
End Sub

Private Sub delBnt_Click()
    If txtNo.Text = "" Then
        MsgBox "序号不能为空！", vbExclamation + vbOKOnly, "警告"
        Exit Sub
    End If
    strSql = "delete from student_sport where student_sport_id = " & Trim(txtNo.Text)
    If ExecuteUpdateSQL(strSql) = False Then
        MsgBox "删除记录失败！", vbExclamation + vbOKOnly, "警告"
        Exit Sub
    Else
        MsgBox "删除记录成功！", vbExclamation + vbOKOnly, "警告"
    End If
    Me.classshowdata
End Sub

Private Sub editBnt_Click()
    Dim rs As ADODB.Recordset
    Dim rs1 As ADODB.Recordset
    Dim rs2 As ADODB.Recordset
    Dim rs3 As ADODB.Recordset
    If txtNo.Text = "" Then
        MsgBox "序号不能为空！", vbExclamation + vbOKOnly, "警告"
        Exit Sub
    End If
    If sexCombo.Text = "" Then
        MsgBox "性别不能为空！", vbExclamation + vbOKOnly, "警告"
        Exit Sub
    End If
    If txtAge.Text = "" Then
        MsgBox "年龄不能为空！", vbExclamation + vbOKOnly, "警告"
        Exit Sub
    End If
    If classCombo.Text = "" Then
        MsgBox "班级不能为空！", vbExclamation + vbOKOnly, "警告"
        Exit Sub
    Else
        strSql = "select class_info_id from class_info where class_info_name = '" & Trim(classCombo.Text) & "'"
        Set rs = ExecuteSQL(Trim(strSql))
        If rs.EOF = False Then
            rs.MoveFirst
            clsId = rs.Fields(0)
            rs.Close
        Else
            MsgBox "班级不对！", vbExclamation + vbOKOnly, "警告"
            Exit Sub
        End If
    End If
    If sportCombo.Text = "" Then
        MsgBox "参加项目不能为空！", vbExclamation + vbOKOnly, "警告"
        Exit Sub
    Else
        strSql = "select sport_item_id from sport_item where sport_item_name = '" & Trim(sportCombo.Text) & "'"
        Set rs3 = ExecuteSQL(Trim(strSql))
        If rs3.EOF = False Then
            rs3.MoveFirst
            sptId = rs3.Fields(0)
            rs3.Close
        Else
            MsgBox "项目名不对！", vbExclamation + vbOKOnly, "警告"
            Exit Sub
        End If
    End If
    If txtSNo.Text = "" Then
        MsgBox "学号不能为空！", vbExclamation + vbOKOnly, "警告"
        Exit Sub
    End If
    If txtName.Text = "" Then
        MsgBox "姓名不能为空！", vbExclamation + vbOKOnly, "警告"
        Exit Sub
    Else
        strSql = "select student_info_id from student_info where student_info_name = '" & txtName.Text & "' and student_info_no = " & txtSNo.Text
        
        Set rs1 = ExecuteSQL(Trim(strSql))
        
        If rs1.EOF = False Then
            rs1.MoveFirst
            stdId = rs1.Fields(0)
            rs1.Close
        Else
            strSql = "insert into student_info (student_info_no,student_info_name,student_info_sex,class_info_id,student_info_age) values (" & Trim(txtSNo.Text) & ",'" & Trim(txtName.Text) & "','" & Trim(sexCombo.Text) & "'," & Trim(clsId) & "," & Trim(txtAge.Text) & ")"
            If ExecuteUpdateSQL(strSql) = False Then
                MsgBox "修改记录失败,请检查相关格式是否正确！", vbExclamation + vbOKOnly, "警告"
                Exit Sub
            End If
        End If
    End If
    
    strSql = "select student_info_id from student_info where student_info_no = " & txtSNo.Text
    Set rs2 = ExecuteSQL(Trim(strSql))
    If rs2.EOF = True Then
        MsgBox "添加记录失败！", vbExclamation + vbOKOnly, "警告"
        rs2.Close
        Exit Sub
    Else
        rs2.MoveFirst
        stdId = rs2.Fields(0)
        rs2.Close
        strSql = "update student_sport set student_info_id = " & stdId & ",sport_item_id = " & sptId & " where student_sport_id = " & Trim(txtNo.Text)
        If ExecuteUpdateSQL(strSql) = False Then
            MsgBox "修改记录失败！", vbExclamation + vbOKOnly, "警告"
            txtName.Text = strSql
            Exit Sub
        Else
            MsgBox "修改记录成功！", vbExclamation + vbOKOnly, "警告"
        End If
    End If
    Me.classshowdata
End Sub

Private Sub MSF_Click()
    applyForm.txtNo = MSF.TextMatrix(MSF.RowSel, 1)
    applyForm.txtSNo = MSF.TextMatrix(MSF.RowSel, 2)
    applyForm.txtName = MSF.TextMatrix(MSF.RowSel, 3)
    applyForm.sexCombo = MSF.TextMatrix(MSF.RowSel, 4)
    applyForm.classCombo = MSF.TextMatrix(MSF.RowSel, 5)
    applyForm.sportCombo = MSF.TextMatrix(MSF.RowSel, 6)
    applyForm.txtAge = MSF.TextMatrix(MSF.RowSel, 7)
End Sub