学生学籍成绩管理系统源程序.docx

1、学生学籍成绩管理系统源程序模块1:Option ExplicitPublic con As ADODB.ConnectionPublic rs As ADODB.RecordsetPublic username As StringPublic usergrade As StringPublic userpwd As StringPublic cmd As ADODB.Command模块2Option ExplicitSub hidewindow() With ActiveWindow .DisplayGridlines = False .DisplayHeadings = False .Dis

2、playOutline = False .DisplayZeros = False .DisplayHorizontalScrollBar = False .DisplayVerticalScrollBar = False .DisplayWorkbookTabs = False End WithEnd SubSub showwindow() With ActiveWindow .DisplayGridlines = True .DisplayHeadings = True .DisplayOutline = True .DisplayZeros = True .DisplayHorizont

3、alScrollBar = True .DisplayVerticalScrollBar = True End WithEnd Sub模块3:Sub Macro1() Range(D2:D60000).Select With Selection.Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:=团员,党员,预备党员,群众 .IgnoreBlank = True .InCellDropdown = True .InputTitl

4、e = .ErrorTitle = .InputMessage = .ErrorMessage = .IMEMode = xlIMEModeNoControl .ShowInput = True .ShowError = True End With End SubSub Macro2() Range(J2:J60000).Select With Selection.Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:=本科,专科

5、.IgnoreBlank = True .InCellDropdown = True .InputTitle = .ErrorTitle = .InputMessage = .ErrorMessage = .IMEMode = xlIMEModeNoControl .ShowInput = True .ShowError = True End With End SubSub Macro3() Range(K2:K60000).Select With Selection.Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlVal

6、idAlertStop, Operator:= _ xlBetween, Formula1:=在校,休学,毕业 .IgnoreBlank = True .InCellDropdown = True .InputTitle = .ErrorTitle = .InputMessage = .ErrorMessage = .IMEMode = xlIMEModeNoControl .ShowInput = True .ShowError = True End With End SubUserForm1代码:Option ExplicitPrivate Sub CommandButton1_Click

7、()On Error GoTo doerrorDim sql As StringSet con = New ADODB.ConnectionSet rs = New ADODB.Recordsetcon.Open Provider=Microsoft.Jet.OLEDB.4.0;Data source= & ThisWorkbook.Path & xjcj.mdbsql = select * from 用户数据表 where 用户名= & UserForm1.ComboBox1.Text & rs.Open sql, con, adOpenKeyset, adLockOptimisticIf

8、UserForm1.ComboBox1.Text = Then MsgBox 用户名不能为空！ UserForm1.ComboBox1.SetFocus Exit SubEnd IfIf UserForm1.TextBox2.Text = Then MsgBox 密码不能为空！ UserForm1.TextBox2.SetFocus Exit SubEnd IfIf Trim(rs.Fields(密码) = Trim(UserForm1.TextBox2.Text) Then username = Trim(rs.Fields(用户名) usergrade = Trim(rs.Fields(权

9、限) userpwd = Trim(rs.Fields(密码) Workbooks.Application.Visible = True Sheets(目录).Activate Me.Hide Unload MeElse MsgBox 密码错误 TextBox2.Text = TextBox2.SetFocusExit SubEnd Ifrs.Closecon.CloseSet rs = NothingSet con = NothingExit Subdoerror:MsgBox 系统出现错误End SubPrivate Sub CommandButton2_Click()ActiveWork

10、book.SaveApplication.QuitEnd SubPrivate Sub UserForm_Initialize()Dim i As IntegerSet con = New ADODB.ConnectionSet rs = New ADODB.Recordsetcon.Open Provider=Microsoft.Jet.OLEDB.4.0;Data source= & ThisWorkbook.Path & xjcj.mdbrs.Open select * from 用户数据表 , con, adOpenKeyset, adLockOptimisticFor i = 1 T

11、o rs.RecordCountUserForm1.ComboBox1.AddItem rs.Fields(用户名)rs.MoveNextNext irs.Closecon.CloseSet rs = NothingSet con = NothingEnd SubUserForm2代码:Option ExplicitPrivate Sub CommandButton1_Click()On Error Resume NextApplication.ScreenUpdating = FalseDim sql As StringSet con = New ADODB.ConnectionSet rs

12、 = New ADODB.RecordsetSet cmd = New ADODB.Commandcon.Open Provider=Microsoft.Jet.OLEDB.4.0;Data source= & ThisWorkbook.Path & xjcj.mdbSet cmd.ActiveConnection = conIf UserForm2.TextBox1.Text = Or UserForm2.TextBox2.Text = Or UserForm2.TextBox3.Text = Then MsgBox 更改信息不齐全！, 48 Exit Sub End IfIf userpw

13、d = Trim(UserForm2.TextBox1.Text) Then If Trim(UserForm2.TextBox2) = Trim(UserForm2.TextBox3) Then cmd.CommandText = update 用户数据表 set 密码= & UserForm2.TextBox2.Text & where 用户名= & username & cmd.Execute MsgBox 密码更换成功！请牢记密码！ Unload Me Else MsgBox 确认密码输入有误，请重新输入！ UserForm2.TextBox3.SetFocus End IfElse

14、MsgBox 原密码输入错误，请重新输入！ UserForm2.TextBox1.SetFocus End If Application.ScreenUpdating = True rs.Closecon.CloseSet rs = NothingSet con = NothingEnd Sub Private Sub CommandButton2_Click()Unload MeEnd SubUserForm3代码:Option ExplicitPrivate Sub CommandButton1_Click()On Error Resume NextDim y As IntegerSet

15、con = New ADODB.ConnectionSet rs = New ADODB.RecordsetSet cmd = New ADODB.Commandcon.Open Provider=Microsoft.Jet.OLEDB.4.0;Data source= & ThisWorkbook.Path & xjcj.mdbrs.Open select * from 用户数据表 where 用户名= & UserForm3.TextBox1.Text & , con, adOpenKeyset, adLockOptimisticIf UserForm3.TextBox1.Text = O

16、r UserForm3.TextBox2.Text = Or UserForm3.TextBox1.Text = ThenMsgBox 信息不全，请完整填写信息Exit SubEnd IfIf Not rs.EOF ThenMsgBox 用户名已存在，请重新输入Exit SubElseSet cmd.ActiveConnection = concmd.CommandText = INSERT INTO 用户数据表 (用户名,密码,权限) VALUES ( & Trim(UserForm3.TextBox1.Text) & , & Trim(UserForm3.TextBox2.Text) &

17、, & Trim(UserForm3.ComboBox1.Text) & )cmd.Executey = MsgBox(数据保存成功，是否继续增加, vbYesNo, 提示信息！) If y = vbYes Then With UserForm3 .TextBox1.Text = .TextBox2.Text = .ComboBox1.Text = .TextBox1.SetFocus Exit Sub End With ElseIf y = vbNo Then Unload Me Exit Sub End IfEnd Ifrs.Closecon.CloseSet rs = NothingSe

18、t con = NothingEnd SubUserForm4代码:Option ExplicitPrivate Sub CommandButton1_Click()On Error Resume NextDim y As StringDim cmd As ADODB.CommandSet con = New ADODB.ConnectionSet rs = New ADODB.RecordsetSet cmd = New ADODB.Commandcon.Open Provider=Microsoft.Jet.OLEDB.4.0;Data source= & ThisWorkbook.Pat

19、h & xjcj.mdbSet cmd.ActiveConnection = conIf Trim(username) = Trim(UserForm4.ComboBox1.Text) ThenMsgBox 当前用户不能删除Exit SubEnd Ify = MsgBox(您是否真的删除该用户?, vbYesNo + 64, 提示)If y = vbYes Thencmd.CommandText = delete * from 用户数据表 where 用户名= & UserForm4.ComboBox1.Text & cmd.ExecuteMsgBox 删除成功Me.HideUnload Me

20、ElseUserForm4.ComboBox1.Text = Exit SubEnd Ifrs.Closecon.CloseSet rs = NothingSet con = NothingEnd SubPrivate Sub CommandButton2_Click()Unload MeEnd SubPrivate Sub UserForm_Initialize()On Error Resume NextDim i As IntegerSet con = New ADODB.ConnectionSet rs = New ADODB.Recordsetcon.Open Provider=Mic

21、rosoft.Jet.OLEDB.4.0;Data source= & ThisWorkbook.Path & xjcj.mdbrs.Open select * from 用户数据表 , con, adOpenKeyset, adLockOptimisticFor i = 1 To rs.RecordCountComboBox1.AddItem rs.Fields(用户名)rs.MoveNextNext irs.Closecon.CloseSet rs = NothingSet con = NothingEnd SubPrivate Sub CommandButton2_Click()Unlo

22、ad MeEnd SubPrivate Sub UserForm_Initialize()On Error Resume NextComboBox1.AddItem (一般用户)ComboBox1.AddItem (高级用户)ComboBox1.AddItem (管理员)End SubUserForm5代码:Option ExplicitPrivate Sub CommandButton1_Click()On Error Resume NextDim sql As StringDim con As ADODB.ConnectionDim rs As ADODB.RecordsetSet con

23、 = New ADODB.ConnectionSet rs = New ADODB.RecordsetIf UserForm5.TextBox1.Text = Then MsgBox 查询条件不能为空！ UserForm5.TextBox1.SetFocus Exit SubEnd Ifcon.Open Provider=Microsoft.Jet.OLEDB.4.0;Data source= & ThisWorkbook.Path & xjcj.mdbsql = select * from 学生数据表 where 学号= & UserForm5.TextBox1.Text & rs.Open

24、 sql, con, adOpenKeyset, adLockOptimisticIf Not rs.EOF ThenWith UserForm5.Label24.Caption = rs.Fields(学号).Label19.Caption = rs.Fields(姓名).Label17.Caption = rs.Fields(性别).Label16.Caption = rs.Fields(政治面貌).Label20.Caption = rs.Fields(入学时间).Label21.Caption = rs.Fields(出生年月).Label27.Caption = rs.Fields(

25、班级代码).Label18.Caption = rs.Fields(专业代码).Label26.Caption = rs.Fields(民族).Label25.Caption = rs.Fields(层次).Label23.Caption = rs.Fields(在校状态)End WithElseMsgBox 没有找到查询结果Exit SubEnd IfUserForm5.CommandButton7.Enabled = TrueUserForm5.CommandButton8.Enabled = TrueUserForm5.CommandButton9.Enabled = TrueEnd S

26、ubPrivate Sub CommandButton6_Click()Unload MeEnd SubPrivate Sub CommandButton7_Click()On Error Resume NextDim y As IntegerDim cmd As ADODB.CommandSet con = New ADODB.ConnectionSet rs = New ADODB.Recordsetcon.Open Provider=Microsoft.Jet.OLEDB.4.0;Data source= & ThisWorkbook.Path & xjcj.mdbSet cmd = N

27、ew ADODB.CommandSet cmd.ActiveConnection = conIf Trim(UserForm5.Label23.Caption) = Trim(在校) Theny = MsgBox(您是否要进行休学处理, vbYesNo, 提示信息！)If y = vbYes ThenUserForm5.Label23.Caption = 休学 cmd.CommandText = update 学生数据表 set 在校状态= & UserForm5.Label23.Caption & where 学号= & UserForm5.Label24.Caption & cmd.Exe

28、cuteMsgBox 更新成功Else Exit SubEnd IfElseMsgBox 不能进行休学处理Exit SubEnd IfUserForm5.CommandButton8.Enabled = FalseUserForm5.CommandButton9.Enabled = FalseEnd SubPrivate Sub CommandButton8_Click()On Error Resume NextDim y As IntegerDim cmd As ADODB.CommandSet con = New ADODB.ConnectionSet rs = New ADODB.Rec

29、ordsetcon.Open Provider=Microsoft.Jet.OLEDB.4.0;Data source= & ThisWorkbook.Path & xjcj.mdbSet cmd = New ADODB.CommandSet cmd.ActiveConnection = conIf Trim(UserForm5.Label23.Caption) = Trim(休学) Theny = MsgBox(您是否要进行复学处理, vbYesNo, 提示信息！)If y = vbYes ThenUserForm5.Label23.Caption = 在校 cmd.CommandText = update 学生数据表 set 在校状态= & UserForm5.Label23.Caption & where 学号= & UserForm5.Label24.Caption & cmd.ExecuteMsgBox 更新成功Else Exit SubEnd IfElseMsgBox 不能进行复学处理Exit SubEnd IfUserForm5.CommandButton7.Enabled = FalseU