mainform.frm

基于VB和ACCESS的成绩管理系统

         Else
         cmdSQL = cmdSQL + " AND CHour=" & Text_CHour.Text & ""
      End If
   End If
   cmdSQL = cmdSQL + " ORDER BY Cno"
  
   On Error GoTo Error_process
   Adodc_CouInfo.RecordSource = cmdSQL
   Adodc_CouInfo.Refresh
   Set DataGrid1.DataSource = Adodc_CouInfo.Recordset
   Exit Sub
   
Error_process:
   MsgBox "查询记录出错：" & Err.Description & Err.Number, vbCritical + vbOKOnly, "错误"
End Sub
''''''''''''''''''''''''''''''成绩查询修改模块''''''''''''''''''''''''''''''''''''''
'修改成绩
Private Sub Button_PModify_Click()
    Dim cmdSQL As String
    Dim Sno, Cno, p As String
    Sno = Trim(Text_PSNo1.Text)
    Cno = Trim(Text_PCNo1.Text)
    p = Trim(Text_P.Text)
    If Sno = Empty Then
       MsgBox "请输入学号！", , "注意"
       Text_ASNo.SetFocus
       Exit Sub
    End If
    If Cno = Empty Then
       MsgBox "请输入课程号！", , "注意"
       Text_ACNo.SetFocus
       Exit Sub
    End If
    cmdSQL = "UPDATE Cou SET Point=" & p & " WHERE Cno=" & Cno & " AND Sno=" & Sno & ""
   On Error GoTo Error_process
    Open_DB ("Cou")
    Conn.Execute cmdSQL
    Close_DB
    MsgBox "修改成绩成功！"
    Exit Sub
Error_process:
    MsgBox "修改成绩出错：" & Err.Description & Err.Number, vbCritical + vbOKOnly, "错误"
    
End Sub

'成绩查询
Private Sub Button_PSearth_Click()
    Dim cmdSQL, cmdSQL1 As String
    Dim Sno, Sname, Cno, CName As String
    Sno = Text_PSNo.Text
    Sname = Text_PSNAME.Text
    Cno = Text_PCNo.Text
    CName = Text_PCNAME.Text
    If Trim(Sno) = Empty And Trim(Sname) = Empty And Trim(Cno) = Empty And Trim(CName) = Empty Then
       MsgBox "请输入查询条件！"
       Exit Sub
    End If
       
    cmdSQL = "Select Cou.Sno As 学号,Cou.SName As 姓名,Cou.CNo As 课程号,CouInfo.CName As 课程名称, CouInfo.CCredit As 学分,Cou.Point As 成绩 "
    If OSNo Then
       cmdSQL = cmdSQL + "FROM Cou INNER JOIN CouInfo " & _
                         " ON Cou.Cno=CouInfo.Cno " & _
                         "WHERE Cou.Sno=" & Sno & " ORDER BY Cou.Cno"
    End If
    If OSName Then
       cmdSQL = cmdSQL + "FROM Cou INNER JOIN CouInfo " & _
                      "ON Cou.CNo=CouInfo.Cno " & _
                      "WHERE Cou.SName='" & Sname & "' ORDER BY Cou.Cno"
    End If
    If OCNo Then
       cmdSQL = cmdSQL + "FROM Cou INNER JOIN CouInfo " & _
                      "ON Cou.CNo=CouInfo.Cno " & _
                      "WHERE Cou.Cno=" & Cno & " ORDER BY Cou.Sno"
    End If
    If OCName Then
       cmdSQL = cmdSQL + "FROM Cou INNER JOIN CouInfo " & _
                      "ON Cou.CNo=CouInfo.Cno " & _
                      "WHERE Cou.CName='" & CName & "' ORDER BY Cou.Sno"
    End If
   On Error GoTo Error_process
   Adodc_Cou.RecordSource = cmdSQL
   Adodc_Cou.Refresh
   Set DataGrid1.DataSource = Adodc_Cou.Recordset
   Exit Sub
Error_process:
   MsgBox "查询记录出错：" & Err.Description & Err.Number, vbCritical + vbOKOnly, "错误"
End Sub
'''''''''''''''''''''''''学生信息管理模块'''''''''''''''''''''''
'添加学生记录
Private Sub Button_SAdd_Click()
    Dim cmdSQL As String

    If Trim(Text_Sno.Text) = Empty Then
       MsgBox "请输入学号！", vbExclamation, "注意"
       Text_Sno.SetFocus
       Exit Sub
    End If
    If Trim(Text_SName.Text) = Empty Then
       MsgBox "请输入姓名！", vbExclamation, "注意"
       Text_SName.SetFocus
       Exit Sub
    End If
    
    If Trim(Combo_SSex.Text) = Empty Then
       MsgBox "请选择性别！", vbExclamation, "注意"
       Combo_SSex.SetFocus
       Exit Sub
    End If

    If IsDate(Trim(Text_SBirthday.Text)) = False Then
       MsgBox "该字段为日期格式数据，请核对！", vbExclamation, "注意"
       Text_SBirthday.SetFocus
       Exit Sub
    End If
    
    If Trim(Text_SBirthday.Text) = Empty Then
       MsgBox "请输入出生日期！", vbExclamation, "注意"
       Text_SBirthday.SetFocus
       Exit Sub
    End If
    
    If Trim(Text_SDno.Text) = Empty Then
       MsgBox "请输入所在学院！", vbExclamation, "注意"
       Text_SDno.SetFocus
       Exit Sub
    End If
    
    A = MsgBox("确认添加？", 256 + 4 + 32, "请确认")
    If A = vbNo Then Exit Sub
    On Error GoTo Error_process
    Open_DB ("StuInfo")
    cmdSQL = "INSERT INTO StuInfo ( Sno,SName,SSex,SBirthday,SDno ) VALUES(" & Text_Sno.Text & ",'" & Text_SName.Text & "','" & Combo_SSex.Text & "','" & Text_SBirthday.Text & "'," & Text_SDno.Text & ")"
    Conn.Execute cmdSQL
    Close_DB
    Text_Sno.Text = ""
    Text_SName.Text = ""
    Text_SBirthday.Text = ""
    MsgBox "添加记录成功", vbOKOnly
    Exit Sub
    
Error_process:
    MsgBox "添加记录出错：" & Err.Description & Err.Number, vbCritical + vbOKOnly, "错误"
End Sub
'删除学生记录
Private Sub Button_SDelete_Click()
   Dim cmdSQL As String
   
   If Trim(Text_Sno.Text) = Empty Then
      MsgBox "请输入要删除的学生的学号", vbOKOnly, "注意"
      Text_Sno.SetFocus
      Exit Sub
   Else
    A = MsgBox("确认删除？", 256 + 4 + 32, "请确认")
    If A = vbNo Then Exit Sub
   End If
   On Error GoTo Error_process
   cmdSQL = "DELETE FROM StuInfo WHERE Sno=" & Trim(Text_Sno.Text) & ""
   Open_DB ("StuInfo")
   Conn.Execute cmdSQL
   Close_DB
   MsgBox "删除记录成功", vbOKOnly
   Exit Sub
Error_process:
   MsgBox "删除记录出错：" & Err.Description & Err.Number, vbCritical + vbOKOnly, "错误"
    
End Sub
'修改学生记录
Private Sub Button_SModify_Click()
   Dim cmdSQL As String
   cmdSQL = "UPDATE StuInfo SET "
   
   If Trim(Text_Sno.Text) = Empty Then
      MsgBox "请输入要修改的学生的学号！", vbOKOnly
      Text_Sno.SetFocus
      Exit Sub
   End If
   
   If Trim(Text_SName.Text) <> Empty Then cmdSQL = cmdSQL + "SName='" & Text_SName.Text & "' "
  
   If Trim(Combo_SSex.Text) <> Empty Then
      If cmdSQL = "UPDATE StuInfo SET " Then
         cmdSQL = cmdSQL + "SSex='" & Combo_SSex.Text & "' "
      Else
         cmdSQL = cmdSQL + ",SSex='" & Combo_SSex.Text & "' "
      End If
   End If
   
   If Trim(Text_SBirthday.Text) <> Empty Then
      If IsDate(Trim(Text_SBirthday.Text)) = False Then
           MsgBox "该字段为日期格式数据，请核对！", vbExclamation, "注意"
           Text_SBirthday.SetFocus
           Exit Sub
      End If
      If cmdSQL = "UPDATE StuInfo SET " Then
      cmdSQL = cmdSQL + "SBirthday='" & Text_SBirthday.Text & "' "
      Else
         cmdSQL = cmdSQL + ",SBirthday='" & Text_SBirthday.Text & "' "
      End If
   End If
   
   If Trim(Text_SDno.Text) <> Empty Then
      If cmdSQL = "UPDATE StuInfo SET " Then
      cmdSQL = cmdSQL + "SDno=" & Text_SDno.Text & " "
      Else
         cmdSQL = cmdSQL + ",SDno=" & Text_SDno.Text & " "
      End If
   End If
      
   cmdSQL = cmdSQL + " Where Sno=" & Text_Sno.Text & ""
   
   A = MsgBox("确认修改？", 256 + 4 + 32, "请确认")
   If A = vbNo Then Exit Sub
   
   On Error GoTo Error_process
   Open_DB ("StuInfo")
   Conn.Execute cmdSQL
   Close_DB
   MsgBox "修改记录成功", vbOKOnly
   Exit Sub
   
Error_process:
   MsgBox "修改记录出错：" & Err.Description & Err.Number, vbCritical + vbOKOnly, "错误"
   
End Sub
'查询学生记录
Private Sub Button_SSearch_Click()
   cmdSQL = "select Sno as 学号,Sname as 姓名,SSex as 性别,SBirthday as 出生日期,SDno as 所在学院 from StuInfo  "
   If Trim(Text_Sno.Text) <> Empty Then cmdSQL = cmdSQL + "WHERE Sno=" & Text_Sno.Text & " "
   If Trim(Text_SName.Text) <> Empty Then
      If cmdSQL = "select Sno as 学号,Sname as 姓名,SSex as 性别,SBirthday as 出生日期,SDno as 所在学院 from StuInfo  " Then
         cmdSQL = cmdSQL + "WHERE SName='" & Text_SName.Text & "'"
         Else
         cmdSQL = cmdSQL + "AND SName='" & Text_SName.Text & "'"
      End If
   End If
   If Trim(Combo_SSex.Text) <> Empty Then
      If cmdSQL = "select Sno as 学号,Sname as 姓名,SSex as 性别,SBirthday as 出生日期,SDno as 所在学院 from StuInfo  " Then
         cmdSQL = cmdSQL + "WHERE SSex='" & Combo_SSex.Text & "'"
         Else
         cmdSQL = cmdSQL + " AND SSex='" & Combo_SSex.Text & "' "
      End If
    End If
   If Trim(Text_SBirthday.Text) <> Empty Then
      If IsDate(Trim(Text_SBirthday.Text)) = False Then
           MsgBox "该字段为日期格式数据，请核对！", vbExclamation, "注意"
           Text_SBirthday.SetFocus
           Exit Sub
      End If
      If cmdSQL = "select Sno as 学号,Sname as 姓名,SSex as 性别,SBirthday as 出生日期,SDno as 所在学院 from StuInfo  " Then
         cmdSQL = cmdSQL + "WHERE SBirthday='" & Text_SBirthday.Text & "'"
         Else
         cmdSQL = cmdSQL + "AND SBirthday='" & Text_SBirthday.Text & "'"
      End If
   End If
   If Trim(Text_SDno.Text) <> Empty Then
      If cmdSQL = "select Sno as 学号,Sname as 姓名,SSex as 性别,SBirthday as 出生日期,SDno as 所在学院 from StuInfo  " Then
         cmdSQL = cmdSQL + "WHERE SDno=" & Text_SDno.Text & ""
         Else
         cmdSQL = cmdSQL + " AND SDno=" & Text_SDno.Text & ""
      End If
   End If
   cmdSQL = cmdSQL + "  ORDER BY Sno"
   On Error GoTo Error_process
   Open_DB ("StuInfo")
   Conn.Execute cmdSQL
   Close_DB
   Adodc_StuInfo.RecordSource = cmdSQL
   Adodc_StuInfo.Refresh
   Set DataGrid1.DataSource = Adodc_StuInfo.Recordset
   Exit Sub
   
Error_process:
   MsgBox "查询记录出错：" & Err.Description & Err.Number, vbCritical + vbOKOnly, "错误"
End Sub
''''''''''''''''''''''以下为处理按钮是否可用以及单选框的语句'''''''''''''''''''''''''
Private Sub Form_Load()
   '窗体加载的时候将“成绩查询修改”模块的“查询”按钮、“选课系统”的“添加”和“删除”按钮设置成为不可用
   Button_PSearth.Enabled = False
   Button_AAdd.Enabled = False
   Button_ADelete.Enabled = False
End Sub

Private Sub Text_ACNo_Change()
   '当“选课系统”的“课程号”文本框的文字发生变化时，
   '如果“学号”不为空，则将“添加”和“删除”按钮设置为可用
   '如果“学号”为空，则“添加”和“删除”按钮保持不可用
   If Trim(Text_ACNo.Text) <> Empty And Trim(Text_ASNo.Text) <> Empty Then
   Button_AAdd.Enabled = True
   Button_ADelete.Enabled = True
   End If
   
End Sub

Private Sub Text_ASNo_Change()
   If Trim(Text_ACNo.Text) <> Empty And Trim(Text_ASNo.Text) <> Empty Then
   Button_AAdd.Enabled = True
   Button_ADelete.Enabled = True
   End If
End Sub
Private Sub Text_ACNo_LostFocus()
   If Trim(Text_ACNo.Text) = Empty Or Trim(Text_ASNo.Text) = Empty Then
   Button_AAdd.Enabled = False
   Button_ADelete.Enabled = False
   End If
End Sub
Private Sub Text_ASNo_LostFocus()
   If Trim(Text_ACNo.Text) = Empty Or Trim(Text_ASNo.Text) = Empty Then
   Button_AAdd.Enabled = False
   Button_ADelete.Enabled = False
   End If
End Sub

Private Sub Text_PCName_GotFocus()
   OCName.Value = True
   If Trim(Text_PSNAME.Text) <> Empty Then
      Button_PSearth.Enabled = True
   Else
      Button_PSearth.Enabled = False
   End If
End Sub
Private Sub Text_PCName_Change()
   If Trim(Text_PCNAME.Text) <> Empty Then Button_PSearth.Enabled = True
End Sub
Private Sub Text_PCName_LostFocus()
   If Trim(Text_PCNAME.Text) = Empty Then OCName.Value = False
End Sub
Private Sub Text_PCNo_Change()
   If Trim(Text_PCNo.Text) <> Empty Then Button_PSearth.Enabled = True
End Sub
Private Sub Text_PCNo_GotFocus()
   OCNo.Value = True
   If Trim(Text_PCNo.Text) <> Empty Then
      Button_PSearth.Enabled = True
   Else
      Button_PSearth.Enabled = False
   End If
End Sub

Private Sub Text_PCNo_LostFocus()
   If Trim(Text_PCNo.Text) = Empty Then OCNo.Value = False
End Sub
Private Sub Text_PSName_Change()
   If Trim(Text_PSNAME.Text) <> Empty Then Button_PSearth.Enabled = True
End Sub
Private Sub Text_PSNAME_GotFocus()
   OSName.Value = True
   If Trim(Text_PSNAME.Text) <> Empty Then
      Button_PSearth.Enabled = True
   Else
      Button_PSearth.Enabled = False
   End If
End Sub

Private Sub Text_PSNAME_LostFocus()
  If Trim(Text_PSNAME.Text) = Empty Then OSName.Value = False
End Sub

Private Sub Text_PSNo_Change()
   If Trim(Text_PSNo.Text) <> Empty Then Button_PSearth.Enabled = True
End Sub

Private Sub Text_PSNo_GotFocus()
   OSNo.Value = True
   If Trim(Text_PSNo.Text) <> Empty Then
      Button_PSearth.Enabled = True
   Else
      Button_PSearth.Enabled = False
   End If
End Sub

Private Sub Text_PSNo_LostFocus()
   If Trim(Text_PSNo.Text) = Empty Then OSNo.Value = False
End Sub