运动会成绩管理系统-主要代码.doc

窗体代码Private Sub Text1_KeyPress(KeyAscii As Integer)If KeyAscii = 13 Then 监听键盘回车 Command1.Value = TrueEnd IfEnd Sub（Text1是账号，Text2是密码）登陆Private Sub Command1_Click()If Text1.Text And Text2.Text Then strsql = select * from 用户信息 where 用户名= & Text1.Text & and 密码= & Text2.Text & Set rs = cnn.Execute(strsql) If Not rs.EOF Then 输入的登录信息正确，则进入系统 主界面.Show Unload 登陆界面 Else MsgBox 您输入的登录信息有误，请重新输入！, vbOKOnly + vbExclamation, 提示 Text1.Text = Text2.Text = Text1.SetFocus End IfElse MsgBox 您输入的帐号或密码有误！, vbOKOnly + vbExclamation, 警告 Text1.Text = Text2.Text = Text1.SetFocusEnd IfEnd Sub退出Private Sub Command2_Click()i = MsgBox(确实要退出系统吗？, vbYesNo + vbExclamation, 系统警告)If i = vbYes Then EndElse MsgBox 欢迎继续使用！, vbOKOnly, 系统提示End IfEnd Sub主界面Private Sub mnuedititem_Click(Index As Integer)项目定义.Show 1End SubPrivate Sub mnusearchitem_Click(Index As Integer)查询输出.SSTab1.Tab = 1查询输出.Show 1End Sub（mnu#是项目定义的属性名称，其他的菜单省略了，都是*.Show1。查询输出菜单栏中包含了7项，但都属于同一窗体，所以加“查询输出.SSTab1.Tab = 1”后面的数字是对应的菜单） 定义比赛项目项目名称：Text2；加入项目：Text1；项目、全能：SSTab1添加：Command1 ；项目删除：Command2；：Command3，全能删除Command4Dim cnn As ADODB.ConnectionDim rs As ADODB.Recordset, rs1 As ADODB.RecordsetPrivate Sub Form_Load()Dim mypath As StringDim i As IntegerSet cnn = New ADODB.ConnectionSet rs = New ADODB.RecordsetSet rs1 = New ADODB.Recordsetmypath = App.Path & /data 获取当前路径If Right(mypath, 1) / Then mypath = mypath + /cnn.Open Data Source= & mypath & db1.mdb & ;Provider=Microsoft.Jet.OLEDB.4.0 连接并打开数据库strsql1 = select count(*) from 运动项目名单 where 项目类型=全能项目strsql2 = select 项目名称 from 运动项目名单 where 项目类型=全能项目Set rs1 = cnn.Execute(strsql1)Set rs2 = cnn.Execute(strsql2)For i = 0 To rs1.Fields(0) - 1 Combo2.List(i) = rs2.Fields(0) rs2.MoveNextNextEnd Sub（Dim定义、连接数据库）Private Sub Combo1_Change()End SubPrivate Sub Combo2_Click()strsql3 = select count(*) from & Combo2.Text & strsql4 = select 项目名称 from & Combo2.Text & Set rs1 = cnn.Execute(strsql3)Set rs2 = cnn.Execute(strsql4)For i = 0 To rs1.Fields(0) - 1 List1.List(i) = rs2.Fields(0) rs2.MoveNextNextEnd SubPrivate Sub Combo3_Change()End SubPrivate Sub Command2_Click()If Text2.Text = Then MsgBox 请先输入项目或编号, vbOKOnly + vbExclamation, 提示 Else strsql = select * from 运动项目名单 where 项目名称= & Text2.Text & Set rs = cnn.Execute(strsql) If rs.EOF = True Then MsgBox 该库中没有此项目！, vbOKOnly, 提示 Text1.Text = Text2.Text = Else strsql2 = delete * from 运动项目名单 where 项目名称= & Text2.Text & cnn.Execute (strsql2) MsgBox 该项目已删除！, vbOKOnly, 提示 Text1.Text = Text2.Text = End IfEnd IfEnd SubPrivate Sub Command3_Click()If Text1.Text = Then MsgBox 请先输入项目, vbOKOnly + vbExclamation, 提示 Else strsql11 = insert into & Combo2.Text & values( & Text1.Text & ) cnn.Execute (strsql11) Text1.Text = strsql3 = select count(*) from & Combo2.Text & strsql4 = select 项目名称 from & Combo2.Text & Set rs1 = cnn.Execute(strsql3) Set rs2 = cnn.Execute(strsql4) For i = 0 To rs1.Fields(0) - 1 List1.List(i) = rs2.Fields(0) rs2.MoveNext NextEnd IfEnd SubPrivate Sub Command4_Click()strsql12 = delete * from & Combo2.Text & where 项目名称= & List1.Text & cnn.Execute (strsql12)List1.RemoveItem List1.ListIndexText1.Text = strsql3 = select count(*) from & Combo2.Text & strsql4 = select 项目名称 from & Combo2.Text & Set rs1 = cnn.Execute(strsql3)Set rs2 = cnn.Execute(strsql4)For i = 0 To rs1.Fields(0) - 1 List1.List(i) = rs2.Fields(0) rs2.MoveNextNextEnd SubPrivate Sub Command1_Click()If Text2.Text = Then MsgBox 请先输入项目或编号, vbOKOnly + vbExclamation, 提示 Else strsql = select * from 运动项目名单 where 项目名称= & Text2.Text & Set rs = cnn.Execute(strsql) If rs.EOF = True Then 该项目暂时在库中不存在，可以入库 If Combo1.Text = 全能项目 Then strsql1 = insert into 运动项目名单(项目名称,项目类型) values( + Text2.Text + , + Combo1.Text + ) strsql2 = create table & Text2.Text & (项目名称 char(20) strsql3 = create table & Text2.Text & 成绩(姓名 char(20),项目名称 char(20),成绩 char(10) cnn.Execute (strsql1) cnn.Execute (strsql2) cnn.Execute (strsql3) MsgBox 该项目已经添加成功！, vbOKOnly, 提示 Text2.Text = Else strsql11 = insert into 运动项目名单 values( + Text2.Text + , + Combo1.Text + , & Combo3.Text & ) cnn.Execute (strsql11) MsgBox 该项目已经添加成功！, vbOKOnly, 提示 Text2.Text = End If Else MsgBox 您输入的该项目已经存在于库中！, vbOKOnly, 提示 Text2.Text = End IfEnd IfEnd SubPrivate Sub SSTab1_DblClick()End SubPrivate Sub Text1_Change()End SubPrivate Sub Text2_Change()End Sub设置工作岗位编号：Text1；名称：Text2；添加：Command1；删除：Command2Dim cnn As ADODB.ConnectionDim rs As ADODB.RecordsetPrivate Sub Form_Load()Dim mypath As StringSet cnn = New ADODB.ConnectionSet rs = New ADODB.Recordsetmypath = App.Path & /data 获取当前路径If Right(mypath, 1) / Then mypath = mypath + /cnn.Open Data Source= & mypath & db1.mdb & ;Provider=Microsoft.Jet.OLEDB.4.0 连接并打开数据库End Sub（定义、连接数据库）Private Sub Command1_Click()If Text1.Text = Or Text2.Text = Then MsgBox 请先输入岗位或编号, vbOKOnly + vbExclamation, 提示 Else strsql = select * from 岗位表 where 岗位编号= & Text1.Text & and 岗位名称= & Text2.Text & Set rs = cnn.Execute(strsql) If rs.EOF = True Then 该岗位暂时在库中不存在，可以入库 strsql1 = insert into 岗位表 values( + Text1.Text + , + Text2.Text + ) cnn.Execute (strsql1) MsgBox 该岗位已添加成功！, vbOKOnly, 提示 Text1.Text = Text2.Text = Else MsgBox 您输入的该岗位或编号已经存在于库中！, vbOKOnly, 提示 Text1.Text = Text2.Text = End IfEnd IfEnd SubPrivate Sub Command2_Click()If Text1.Text = And Text2.Text = Then MsgBox 请先输入岗位或编号, vbOKOnly + vbExclamation, 提示 Else strsql = select * from 岗位表 where 岗位编号= & Text1.Text & or 岗位名称= & Text2.Text & Set rs = cnn.Execute(strsql) If rs.EOF = True Then MsgBox 该库中没有此岗位！, vbOKOnly, 提示 Text1.Text = Text2.Text = Else strsql2 = delete * from 岗位表 where 岗位编号= & Text1.Text & or 岗位名称= & Text2.Text & cnn.Execute (strsql2) MsgBox 该岗位已删除！, vbOKOnly, 提示 Text1.Text = Text2.Text = End IfEnd IfEnd SubPrivate Sub Text1_Change()End SubPrivate Sub Text2_Change()End Sub录入工作人员姓名：Text1；修改：Command1；删除：Command2Dim cnn As ADODB.ConnectionDim rs As ADODB.RecordsetPrivate Sub Form_Load()Dim mypath As StringSet cnn = New ADODB.ConnectionSet rs = New ADODB.Recordsetmypath = App.Path & /data 获取当前路径If Right(mypath, 1) / Then mypath = mypath + /cnn.Open Data Source= & mypath & db1.mdb & ;Provider=Microsoft.Jet.OLEDB.4.0 连接并打开数据库strsql0 = select count(*) from 岗位表strsql10 = select 岗位名称 from 岗位表Set rs = cnn.Execute(strsql0)Sum = Val(rs.Fields(0) + 1Set rs = cnn.Execute(strsql10)Combo1.List(0) = For i = 1 To Sum - 1 为课程名称选项的值进行初始化处理 Combo1.List(i) = rs.Fields(0) rs.MoveNext NextEnd Sub（定义、连接数据库）Private Sub Command2_Click()If Text1.Text = Or Combo1.Text = Then MsgBox 请先输入姓名和岗位！, vbOKOnly + vbExclamation, 提示 Else strsql7 = select * from 工作人员表 where 姓名= & Text1.Text & and 岗位= & Combo1.Text & Set rs = cnn.Execute(strsql7) If rs.EOF = False Then 该岗位存在，可以进行删除 strsql6 = delete * from 工作人员表 where 姓名= & Text1.Text & and 岗位= & Combo1.Text & cnn.Execute (strsql6) MsgBox 删除成功！, vbOKOnly, 提示 Text1.Text = Else MsgBox 此岗位不存在！, vbOKOnly, 警告 Text1.Text = End IfEnd IfEnd SubPrivate Sub Command1_Click()If Text1.Text = Or Combo1.Text = Then MsgBox 请先输入姓名和岗位, vbOKOnly + vbExclamation, 提示 Else strsql = select * from 工作人员表 where 姓名= & Text1.Text & Set rs = cnn.Execute(strsql) If rs.EOF = True Then 该岗位没被您选择，可以入库 strsql3 = insert into 工作人员表 values( + Text1.Text + , + Combo1.Text + ) cnn.Execute (strsql3) MsgBox 修改成功！, vbOKOnly, 提示 Text1.Text = Else strsq2 = select * from 工作人员表 where 姓名= & Text1.Text & and + Combo1.Text + Set rs = cnn.Execute(strsq2) If rs.EOF = True Then MsgBox 您已经选过了此岗位！, vbYesNoCancel + vbExclamation, 提示 Else strsql4 = insert into 工作人员表 values( + Text1.Text + , + Combo1.Text + ) cnn.Execute (strsql4) MsgBox 修改成功！, vbOKOnly, 提示 Text1.Text = End If End IfEnd IfEnd SubPrivate Sub Text1_Change()End Sub姓名：Text1 ；学号：Text2 ；代表队：Text3；确定：Command1；删除：Command2；取消：Command3；：MSHFlexGrid1Dim cnn As ADODB.ConnectionDim rs As ADODB.Recordset, rs1 As ADODB.Recordset, rs2 As ADODB.Recordset, rs3 As ADODB.Recordset, rs4 As ADODB.Recordset, rs5 As ADODB.RecordsetPrivate Sub Form_Load()Dim mypath As StringSet cnn = New ADODB.ConnectionSet rs = New ADODB.Recordsetmypath = App.Path & /data 获取当前路径If Right(mypath, 1) / Then mypath = mypath + /cnn.Open Data Source= & mypath & db1.mdb & ;Provider=Microsoft.Jet.OLEDB.4.0 连接并打开数据库strsql1 = select count(*) from 运动项目名单strsql2 = select 项目名称 from 运动项目名单Set rs1 = cnn.Execute(strsql1)Set rs2 = cnn.Execute(strsql2)For i = 0 To rs1.Fields(0) - 1 Combo1.List(i) = rs2.Fields(0) rs2.MoveNextNextEnd SubPrivate Sub Command1_Click()If Text1.Text = Or Text2.Text = Then MsgBox 请先输入姓名和学号, vbOKOnly + vbExclamation, 提示 Else strsql = select * from 运动员名单 where 姓名= & Text1.Text & or 学号= & Text2.Text & Set rs = cnn.Execute(strsql) If rs.EOF = True Then 该运动员不存在，可以入库 strsql1 = insert into 运动员名单 values( + Text2.Text + , & Text1.Text & , + Combo2.Text + , + Text4.Text + ) strsql3 = insert into 运动员参赛情况 values( + Text1.Text + , + Combo1.Text + ) cnn.Execute (strsql1) cnn.Execute (strsql3) strsql12 = select 项目类型 from 运动项目名单 where 项目名称= & Combo1.Text & Set rs4 = cnn.Execute(strsql12) If rs4.Fields(0) = 径赛项目 Then strsql31 = insert into 径赛初赛成绩(姓名,项目名称) values( & Text1.Text & , & Combo1.Text & ) cnn.Execute (strsql31) End If MsgBox 报名成功！, vbOKOnly, 提示 Else strsql2 = select * from 运动员名单 where 姓名= & Text1.Text & and 学号= & Text2.Text & Set rs = cnn.Execute(strsql2) If rs.EOF = False Then strsql2 = select * from 运动员参赛情况 where 姓名= & Text1.Text & and 项目名称= & Combo1.Text & Set rs = cnn.Execute(strsql2) If rs.EOF = True Then strsql4 = insert into 运动员参赛情况 values( & Text1.Text & , & Combo1.Text & ) cnn.Execute (strsql4) strsql12 = select 项目类型 from 运动项目名单 where 项目名称= & Combo1.Text & Set rs4 = cnn.Execute(strsql12) If rs4.Fields(0) = 径赛项目 Then strsql31 = insert into 径赛初赛成绩(姓名,项目名称) values( & Text1.Text & , & Combo1.Text & ) cnn.Execute (strsql31) End If MsgBox 报名成功！, vbOKOnly, 提示 Else MsgBox 您已经了报名！, vbOKOnly, 提示 End If Else MsgBox 已经存在此人了！, vbOKOnly, 提示 End If End IfEnd Ifstrsql5 = select distinct 代表队 from 运动员名单Set rs = cnn.Execute(strsql5)i = 0Do While Not rs.EOF strsql6 = select count(*) from 运动员名单 where 代表队= & rs.Fields(0) & and 性别=男 Set rs1 = cnn.Execute(strsql6) strsql7 = select count(*) from 运动员名单 where 代表队= & rs.Fields(0) & and 性别=女 Set rs2 = cnn.Execute(strsql7) strsql8 = select count(*) from 运动员名单 where 代表队= & rs.Fields(0) & Set rs3 = cnn.Execute(strsql8) strsqli = select * from 代表队统计 where 代表队= & rs.Fields(0) & Set rs4 = cnn.Execute(strsqli) If rs4.EOF Then strsql9 = insert into 代表队统计 values( & rs.Fields(0) & , & rs1.Fields(0) & , & rs2.Fields(0) & , & rs3.Fields(0) & ) cnn.Execute (strsql9) End If If Not rs4.EOF Then strsqlj = update 代表队统计 set 男子= & rs1.Fields(0) & ,女子= & rs2.Fields(0) & ,合计= & rs3.Fields(0) & where 代表队= & rs.Fields(0) & cnn.Execute (strsqlj) End If rs.MoveNext i = i + 1Loopstrsql111 = select * from 运动员参赛情况 where 姓名= & Text1.Text & Set rs5 = cnn.Execute(strsql111)With MSHFlexGrid1 详细查询的信息显示 .Rows = 2 .CellAlignment = 4 .TextMatrix(0, 0) = 姓名 .TextMatrix(0, 1) = 项目名称 Do While Not rs5.EOF .Rows = .Rows + 1 .CellAlignment = 4 .TextMatrix(.Rows - 2, 0) = rs5.Fields(0) .TextMatrix(.Rows - 2, 1) = Trim(rs5.Fields(1) rs5.MoveNext LoopEnd WithEnd SubPrivate Sub Command2_Click()If Text1.Text = Or Text2.Text = Then MsgBox 请先输入姓名和学号, vbOKOnly + vbExclamation, 提示 Else strsql5 = select * from 运动员参赛情况 where 姓名= & Text1.Text & and 项目名称= & Combo1.Text & Set rs = cnn.Execute(strsql5) If rs.EOF = False Then 该运动员存在，可以进行删除 strsql8 = delete * from 运动员参赛情况 where 姓名= & Text1.Text & and 项目名称= + Combo1.Text + cnn.Execute (strsql8) strsql12 = select 项目类型 from 运动项目名单 where 项目名称= & Combo1.Text & Set rs4 = cnn.Execute(strsql12) If rs4.Fields(0) = 径赛项目 Then strsql31 = delete * from 径赛初赛成绩 where 姓名= & Text1.Text & and 项目名称= + Combo1.Text + cnn.Execute (strsql31) End If strsql9 = select count(*) from 运动员参赛情况 where 姓名= + Text1.Text + Set rs = cnn.Execute(strsql9) M = rs.Fields(0) If M 0 Then MsgBox 删除成功！, vbOKOnly, 提示 Else strsql6 = delete * from 运动员名单 where 姓名= + Text1.Text + cnn.Execute (strsql6) MsgBox 删除成功！, vbOKOnly, 提示 End If Else MsgBox 你没有选此项目！, vbOKOnly, 提示 End IfEnd Ifstrsql5 = select distinct 代表队 from 运动员名单Set rs = cnn.Execute(strsql5)i = 0Do While Not rs.EOF strsql6 = select count(*) from 运动员名单 where 代表队= & rs.Fields(0) & and 性别=男 Set rs1 = cnn.Execute(strsql6) strsql7 = select count(*) from 运动员名单 where 代表队= & rs.Fields(0) & and 性别=女 Set rs2 = cnn.Execute(strsql7) strsql8 = select count(*) from 运动员名单 where 代表队= & rs.Fields(0) & Set rs3 = cnn.Execute(strsql8) strsqli = select * from 代表队统计 where 代表队= & rs.Fields(0) & Set rs4 = cnn.Execute(strsqli) If rs4.EOF Then strsql9 = insert into 代表队统计 values( & rs.Fields(0) & , & rs1.Fields(0) & , & rs2.Fields(0) & , & rs3.Fields(0) & ) cnn.Execute (strsql9) End If If Not rs4.EOF Then strsqlj = update 代表队统计 set 男子= & rs1.Fields(0) & ,女子= & rs2.Fields(0) & ,合计= & rs3.Fields(0) & where 代表队= & rs.Fields(0) & cnn.Execute (strsqlj) End If rs.MoveNext i = i + 1Loopstrsql111 = select * from 运动员参赛情况 where 姓名= & Text1.Text & Set rs5 = cnn.Execute(strsql111)With MSHFlexGrid1 详细查询的信息显示 .Rows = 2 .CellAlignment = 4 .TextMatrix(0, 0) = 姓名 .TextMatrix(0, 1) = 项目名称 Do While Not rs5.EOF .Rows = .Rows + 1 .CellAlignment = 4 .TextMatrix(.Rows - 2, 0) = rs5.Fields(0) .TextMatrix(.Rows - 2, 1) = Trim(rs5.Fields(1) rs5.MoveNext LoopEnd WithEnd SubPrivate Sub Command3_Click()Unload MeEnd SubPrivate Sub MSHFlexGrid1_Click()End Sub纪录成绩：Text1；确定：Command1；：MSHFlexGrid1Dim cnn As ADODB.ConnectionDim rs As ADODB.Recordset, rs1 As ADODB.RecordsetPrivate Sub Form_Load()Dim mypath As StringSet cnn = New ADODB.ConnectionSet rs = New ADODB.Recordsetmypath = App.Path & /data 获取当前路径If Right(mypath, 1) / Then mypath = mypath + /cnn.Open Data Source= & mypath & db1.mdb & ;Provider=Microsoft.Jet.OLEDB.4.0 连接并打开数据库strsql10 = select distinct 项目名称 from 运动项目名单Set rs = cnn.Execute(strsql10)k = 0Do While Not rs.EOF Combo1.List(k) = rs.Fields(0) rs.MoveNext k = k + 1LoopEnd Sub（定义、连接数据库）Private Sub Command1_Click()If Combo1.Text = And Text1.Text = Then MsgBox 请输入纪录成绩！, vbOKOnly, 提示！Else strsql = select * from 纪录成绩 where 项目名称= & Combo1.Text & Set rs = cnn.Execute(strsql) If rs.EOF Then strsql1 = insert into 纪录成绩 values( & Combo1.Text & , & Text1.Text & ) cnn.Execute (strsql1) Else strsql2 = update 纪录成绩 set 纪录成绩= & Text1.Text & where 项目名称= & Combo1.Text & cnn.Execute (strsql2) End IfEnd Ifstrsql3 = select * from 纪录成绩Set rs1 = cnn.Execute(strsql3)With MSHFlexGrid1 详细查询的信息显示 .Rows = 2 .Cel