📄 frmstart.frm
字号:
'_________________________________________________________________
Set DBS = OpenDatabase(MAIN.CMD2.filename)
Set RSAA = DBS.OpenRecordset("SELECT MAX(" & Combo2.Text & "), MIN(" & Combo2.Text & "), AVG(" & Combo2.Text & "), SUM(" & Combo2.Text & ") FROM 学生 WHERE 班级=" & Combo1.Text & " AND 学籍=-1")
MAXF = Format$(RSAA(0))
MINF = Format$(RSAA(1))
AVGF = Format(Format$(RSAA(2)), "0.0")
SUMF = Format$(RSAA(3))
DBS.Close
'
' YMF = MF * Text55.Text / 100
' LMF = MF * Text6.Text / 100
' JMF = MF * Text7.Text / 100
Set DBS = OpenDatabase(MAIN.CMD2.filename)
Set RSAA = DBS.OpenRecordset("SELECT COUNT(*) FROM 学生 WHERE 班级=" & Combo1.Text & "AND " & Combo2 & ">=" & MF * Text55.Text / 100 & " AND 学籍=-1")
YMF = Format$(RSAA(0))
DBS.Close
Set DBS = OpenDatabase(MAIN.CMD2.filename)
Set RSAA = DBS.OpenRecordset("SELECT COUNT(*) FROM 学生 WHERE 班级=" & Combo1.Text & "AND " & Combo2 & ">=" & MF * Text6.Text / 100 & " AND 学籍=-1")
LMF = Format$(RSAA(0))
DBS.Close
Set DBS = OpenDatabase(MAIN.CMD2.filename)
Set RSAA = DBS.OpenRecordset("SELECT COUNT(*) FROM 学生 WHERE 班级=" & Combo1.Text & "AND " & Combo2 & ">=" & MF * Text7.Text / 100 & " AND 学籍=-1")
JMF = Format$(RSAA(0))
DBS.Close
Set DBS = OpenDatabase(MAIN.CMD2.filename)
Set RSAA = DBS.OpenRecordset("SELECT COUNT(*) FROM 学生 WHERE 班级=" & Combo1.Text & "AND " & Combo2 & "<" & MF * Text7.Text / 100 & " AND 学籍=-1")
NJMF = Format$(RSAA(0))
DBS.Close
Dim BJRS As Long
Set DBS = OpenDatabase(MAIN.CMD2.filename)
Set RSAA = DBS.OpenRecordset("SELECT COUNT(*) FROM 学生 WHERE 班级=" & Combo1.Text & " AND 学籍=-1")
BJRS = Format$(RSAA(0))
DBS.Close
' Me.Caption = Format(YMF / BJRS, "0.0%")_______________________________________________________________
Set db = OpenDatabase(MAIN.CMD2.filename)
SQLSTR = "INSERT INTO 分析表 " _
& "(班级,科目,最高分,最低分,平均分,合计总分,优秀数,良好数,及格数,不及格数,优秀率,良好率,及格率,不及格率,三项之和) " _
& "VALUES('" & Combo1.Text & "','" & Combo2.Text & "' , '" & Format(MAXF, "0.0") & " ' , '" & Format(MINF, "0.0") & " ' , '" & Format(AVGF, "0.0") & " ', '" & Format(SUMF, "0.0") & " ', '" & Format(YMF, "0.0") & " ', '" & Format(LMF, "0.0") & " ', '" & Format(JMF, "0.0") & " ', '" & Format(NJMF, "0.0") & "', '" & Format(YMF / BJRS, "0.0%") & " ', '" & Format(LMF / BJRS, "0.0%") & " ', '" & Format(JMF / BJRS, "0.0%") & " ', '" & Format(NJMF / BJRS, "0.0%") & " ','" & Format(AVGF * Text22.Text / 100 + YMF / BJRS * Text33.Text + JMF / BJRS * Text44.Text, "0.0") & "') "
db.Execute SQLSTR
db.Close
',优秀率 , 良好率, 及格率, 不及格率
' Me.Caption = (AVGF * Text22.Text / 100) + (YMF / BJRS * Text33.Text) + (JMF / BJRS * Text44.Text)
Next intC
Set DBS = OpenDatabase(MAIN.CMD2.filename)
Set RSAA = DBS.OpenRecordset("SELECT MAX(" & Combo2.Text & "), MIN(" & Combo2.Text & "), AVG(" & Combo2.Text & "), SUM(" & Combo2.Text & ") FROM 学生 WHERE 学籍=-1")
MAXFa = Format$(RSAA(0))
MINFa = Format$(RSAA(1))
AVGFa = Format(Format$(RSAA(2)), "0.0")
SUMFa = Format$(RSAA(3))
DBS.Close
Set DBS = OpenDatabase(MAIN.CMD2.filename)
Set RSAA = DBS.OpenRecordset("SELECT COUNT(*) FROM 学生 WHERE " & Combo2 & ">=" & MF * Text55.Text / 100 & " AND 学籍=-1")
YMFS = Format$(RSAA(0))
DBS.Close
Set DBS = OpenDatabase(MAIN.CMD2.filename)
Set RSAA = DBS.OpenRecordset("SELECT COUNT(*) FROM 学生 WHERE " & Combo2 & ">=" & MF * Text6.Text / 100 & " AND 学籍=-1")
LMFS = Format$(RSAA(0))
DBS.Close
Set DBS = OpenDatabase(MAIN.CMD2.filename)
Set RSAA = DBS.OpenRecordset("SELECT COUNT(*) FROM 学生 WHERE " & Combo2 & ">=" & MF * Text7.Text / 100 & " AND 学籍=-1")
JMFS = Format$(RSAA(0))
DBS.Close
Set DBS = OpenDatabase(MAIN.CMD2.filename)
Set RSAA = DBS.OpenRecordset("SELECT COUNT(*) FROM 学生 WHERE " & Combo2 & "<" & MF * Text7.Text / 100 & " AND 学籍=-1")
NJMFS = Format$(RSAA(0))
DBS.Close
Set DBS = OpenDatabase(MAIN.CMD2.filename)
Set RSAA = DBS.OpenRecordset("SELECT COUNT(*) FROM 学生 WHERE 学籍=-1")
BJRSS = Format$(RSAA(0))
DBS.Close
Set db = OpenDatabase(MAIN.CMD2.filename)
SQLSTR = "INSERT INTO 分析表 (班级,科目,最高分,最低分,平均分,合计总分,优秀数,良好数,及格数,不及格数,优秀率,良好率,及格率,不及格率,三项之和) " _
& "VALUES('年级','" & Combo2.Text & "总分', '" & Format(MAXFa, "0.0") & " ' , '" & Format(MINFa, "0.0") & " ' , '" & Format(AVGFa, "0.0") & " ', '" & Format(SUMFa, "0.0") & " ', '" & Format(YMFS, "0.0") & " ', '" & Format(LMFS, "0.0") & " ', '" & Format(JMFS, "0.0") & " ', '" & Format(NJMFS, "0.0") & "', '" & Format(YMFS / BJRSS, "0.0%") & " ', '" & Format(LMFS / BJRSS, "0.0%") & " ', '" & Format(JMFS / BJRSS, "0.0%") & " ', '" & Format(NJMFS / BJRSS, "0.0%") & " ','" & Format(AVGFa * Text22.Text / 100 + YMFS / BJRSS * Text33.Text + JMFS / BJRSS * Text44.Text, "0.0") & "') "
db.Execute SQLSTR
db.Close
Data1.Refresh
Data1.DatabaseName = MAIN.CMD2.filename
Data1.RecordSource = "select 平均分,均分差 from 分析表 WHERE 科目='" & Combo2.Text & "' ORDER BY 平均分 desc"
Data1.Refresh
Dim AIQ As Long
For AIQ = 1 To VSFlexGrid1.Rows - 1
VSFlexGrid1.TextMatrix(AIQ, 2) = "-" & Format(VSFlexGrid1.TextMatrix(1, 1) - VSFlexGrid1.TextMatrix(AIQ, 1), "0.0")
VSFlexGrid1.TextMatrix(1, 2) = ""
' VSFlexGrid1.TextMatrix(VSFlexGrid1.Rows - 1, 2) = ""
Next AIQ
Next intCounter
DoEvents
Label1.Caption = "正在优化数据库......"
DoEvents
'##############################################################################################
Data1.Refresh
'
'Data1.DatabaseName = Main.CMD2.filename
Data1.RecordSource = "select * from 分析表 ORDER BY 科目 "
Data1.Refresh
Dim aiqa As Long
For aiqa = 2 To VSFlexGrid1.Rows - 1
If VSFlexGrid1.TextMatrix(aiqa, 6) = "0" Then VSFlexGrid1.TextMatrix(aiqa, 6) = ""
Next aiqa
'32755:
' Select Case Err.Number
' Case 13
' MsgBox "数据库有误,如:某个班级分数未输入完整", 64, "无法载入"
' Unload Me
'
' End Select
End Sub
Private Sub Command3_Click()
Dim DBS As Database
Dim RST As Recordset
Dim SQLSTR As String
Dim YXJ As Long
Dim NYXJ As Long
Dim ZJF As Single
Dim YS As Long
Dim LS As Long
Dim JS As Long
Dim NJS As Long
On Error Resume Next
Set db = OpenDatabase(MAIN.CMD2.filename)
SQLSTR = "DELETE * from 班主任"
db.Execute SQLSTR
db.Close
Set db = OpenDatabase(MAIN.CMD2.filename)
Set rs = db.OpenRecordset("班级")
rs.MoveLast
intRecCount = rs.RecordCount
rs.MoveFirst
For intCounter = 1 To intRecCount
Text1.Text = rs![班级]
rs.MoveNext
Set DBS = OpenDatabase(MAIN.CMD2.filename)
Set RST = DBS.OpenRecordset("SELECT COUNT(*) FROM 学生 WHERE 班级=" & Text1.Text & " AND 学籍=-1")
YXJ = Format$(RST(0))
DBS.Close
Set DBS = OpenDatabase(MAIN.CMD2.filename)
Set RST = DBS.OpenRecordset("SELECT COUNT(*) FROM 学生 WHERE 班级=" & Text1.Text & " ")
NYXJ = Format$(RST(0))
DBS.Close
Set DBS = OpenDatabase(MAIN.CMD2.filename)
Set RST = DBS.OpenRecordset("SELECT AVG(总分) FROM 学生 WHERE 班级=" & Text1.Text & " AND 学籍=-1")
ZJF = Format$(RST(0))
DBS.Close
Set DBS = OpenDatabase(MAIN.CMD2.filename)
Set RST = DBS.OpenRecordset("SELECT COUNT(*) FROM 学生 WHERE 班级=" & Text1.Text & " AND 学籍=-1 AND 总分>= " & Text11.Text * Text55.Text / 100 & "")
YS = Format$(RST(0))
DBS.Close
Set DBS = OpenDatabase(MAIN.CMD2.filename)
Set RST = DBS.OpenRecordset("SELECT COUNT(*) FROM 学生 WHERE 班级=" & Text1.Text & " AND 学籍=-1 AND 总分>= " & Text11.Text * Text6.Text / 100 & "")
LS = Format$(RST(0))
DBS.Close
Set DBS = OpenDatabase(MAIN.CMD2.filename)
Set RST = DBS.OpenRecordset("SELECT COUNT(*) FROM 学生 WHERE 班级=" & Text1.Text & " AND 学籍=-1 AND 总分>= " & Text11.Text * Text7.Text / 100 & "")
JS = Format$(RST(0))
DBS.Close
Set DBS = OpenDatabase(MAIN.CMD2.filename)
Set RST = DBS.OpenRecordset("SELECT COUNT(*) FROM 学生 WHERE 班级=" & Text1.Text & " AND 学籍=-1 AND 总分< " & Text11.Text * Text7.Text / 100 & "")
NJS = Format$(RST(0))
DBS.Close
Set DBS = OpenDatabase(MAIN.CMD2.filename)
SQLSTR = "INSERT INTO 班主任 (班级,在籍生,参考生,总均分,优秀数,良好数,及格数,不及格数,优秀率,良好率,及格率,不及格率,三项之和) " _
& "VALUES('" & Text1.Text & "','" & YXJ & "','" & NYXJ & "','" & Format(ZJF, "0.0") & "','" & Format(YS, "0.0") & "','" & Format(LS, "0.0") & "','" & Format(JS, "0.0") & "','" & Format(NJS, "0.0") & "','" & Format(YS / YXJ, "0.0%") & "','" & Format(LS / YXJ, "0.0%") & "','" & Format(JS / YXJ, "0.0%") & "','" & Format(NJS / YXJ, "0.0%") & "','" & Format(ZJF * Text22.Text / 100 + YS / YXJ * Text33.Text + JS / YXJ * Text44.Text, "0.0") & "') "
DBS.Execute SQLSTR
DBS.Close
Next intCounter
' Data1.DatabaseName = Main.CMD2.filename
Data1.RecordSource = "select 总均分,均分差 from 班主任 ORDER BY 总均分 desc"
Data1.Refresh
Dim AIQ As Long
For AIQ = 1 To VSFlexGrid1.Rows - 1
VSFlexGrid1.TextMatrix(AIQ, 2) = "-" & Format(VSFlexGrid1.TextMatrix(1, 1) - VSFlexGrid1.TextMatrix(AIQ, 1), "0.0")
VSFlexGrid1.TextMatrix(1, 2) = ""
Next AIQ
'
Data1.DatabaseName = MAIN.CMD2.filename
Data1.RecordSource = "select * from 班主任 "
Data1.Refresh
Label1.Caption = " 处理完毕！！！"
MsgBox "载入数据已处理成功,您可以进行报表输出", 32, "提示"
Unload Me
End Sub
Private Sub Form_Load()
On Error GoTo 32755
Me.Hide
SCA.Show
DoEvents
MAIN.Enabled = False
Unload SCA
Me.Show
Set db = OpenDatabase(MAIN.CMD2.filename)
Set rs = db.OpenRecordset("SELECT * FROM COM WHERE 标记='载入数据'")
lo = rs![代码]
'以下代码将取出COM中的载入数据中的代码信息,供下表格输入
Data1.DatabaseName = MAIN.CMD2.filename
Data1.RecordSource = lo
Data1.Refresh
'+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Set db = OpenDatabase(MAIN.CMD2.filename)
Set rs = db.OpenRecordset("SELECT * FROM COM WHERE 标记='合计总分'")
HJZF = rs![代码]
Set db = DBEngine.Workspaces(0).OpenDatabase(MAIN.CMD2.filename)
db.Execute HJZF
db.Close '得到合计总分数
Data1.Refresh
'+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Call Command1_Click
Call Command2_Click
Call Command3_Click
32755:
Select Case Err.Number
Case 3343
MsgBox "无法识别的NHB数据文件,或者该文件已损坏", 64, "无法载入"
MAIN.CMD2.filename = ""
Unload Me
Case 3061
MsgBox "此数据被破坏,请使用数据恢复来修复此数据库", 32, "无法载入"
MAIN.CMD2.filename = ""
Unload Me
Case 3078
MsgBox "此数据格式不对或被破坏", 32, "无法载入"
MAIN.CMD2.filename = ""
Unload Me
Case 13
MsgBox "数据库有误,如:某个班级分数未输入完整", 64, "无法载入"
MAIN.CMD2.filename = ""
Unload Me
Case 3021
MsgBox "此数据格式不对或被破坏", 32, "无法载入"
MAIN.CMD2.filename = ""
Unload Me
End Select
End Sub
Private Sub Form_Unload(Cancel As Integer)
On Error Resume Next
MAIN.Enabled = True
Dim ws As Workspace
Dim db As Database
Dim rs As Recordset
For Each ws In Workspaces
For Each db In ws.Databases
For Each rs In db.Recordsets
rs.Close
Set rs = Nothing
Next
db.Close
Set db = Nothing
Next
ws.Close
Set ws = Nothing
Next
End Sub
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -