前言:
学生上机状态查询窗体可以显示全部正在上机的学生,在上机管理菜单里可以选择所有学生下线和选中学生下线。选中学生下线是在MSFlexGrid里选择学生,被选中的学号记录会变色并在后边显示√。使用循环语句进行下机处理。
流程图:
代码:
单击MSFlexGrid中的数据改变颜色
Private Sub MSFlexGrid1_MouseDown(Button As Integer, Shift As Integer, x As Single, y As Single)
Dim col As Integer
'选中不连续的行
If MSFlexGrid1.TextMatrix(MSFlexGrid1.Row, 5) = "√" Then
MSFlexGrid1.TextMatrix(MSFlexGrid1.Row, 5) = ""
'恢复颜色
For col = 0 To MSFlexGrid1.cols - 1
MSFlexGrid1.col = col
MSFlexGrid1.CellBackColor = vbWhite
Next col
Else
MSFlexGrid1.TextMatrix(MSFlexGrid1.Row, 5) = "√"
'改变颜色
For col = 0 To MSFlexGrid1.cols - 1
MSFlexGrid1.col = col
MSFlexGrid1.CellBackColor = &HFFFF00
Next col
End If
'判断是否选中数据,如果选中数据那么就会让你的修改按钮为激活状态
If MSFlexGrid1.TextMatrix(MSFlexGrid1.Row, 5) = "√" Then
xzxs.Enabled = True
Else
xzxs.Enabled = False
End If
End Sub
选择下机
Private Sub cmdselect_Click()
Dim sz(999) As String '这是一个数组,用来存储带“√”的学号
Dim xh(999) As String '用来存储带“√”的mshflexgrid的行号
Dim txtcash As String
Dim consumetime As String
Dim consume As String
Dim z As Integer '用来存储带“√”的学号用到的变量
Dim i As Integer '改变颜色时候调用的变量
Dim s As Integer '存带√的mshflexgrid的行号用到的变量
Dim j As Integer
Dim t
Dim bob As Boolean '用来标记是否点击显示全部按钮的状态,最开始默认的是false,点击全部按钮后,值为true
Dim txtsql As String
Dim txtsql1 As String
Dim msgtext As String
Dim mrc_online As ADODB.Recordset '连接on_line表
Dim mrc1 As ADODB.Recordset '代表online_info中有时间限制
Dim mrc_line As ADODB.Recordset '代表连接 line 表
Dim mrc_bas As ADODB.Recordset '代表连接Basicdate表
Dim mrc_stu As ADODB.Recordset '代表学生表
With MSFlexGrid1
'记录选中下机的卡号,在最后一行加了一个勾,将这些记录的所有卡号信息全部存到数组sz中
i = 0
For j = 1 To .rows - 1
If .TextMatrix(j, 5) = "√" Then
sz(i) = .TextMatrix(j, 0) '存的是卡号
xh(i) = Val(j)
i = i + 1
End If
Next j
For z = 0 To i - 1 '数组是从0开始的
'更新了表online_info与line info中的信息
txtsql = "select * from BasicData_Info"
Set mrc_bas = ExecuteSQL(txtsql, msgtext)
txtsql = "select * from student_Info where cardno='" & sz(z) & "'" & "and status='使用" & "'"
Set mrc_stu = ExecuteSQL(txtsql, msgtext)
txtsql = "select * from online_info where cardno= '" & sz(z) & " '"
Set mrc_online = ExecuteSQL(txtsql, msgtext)
'计算消费时间
consumetime = DateDiff("n", Trim(mrc_online!Date), Now)
'计算消费金额
If Val(consumetime) <= Val(mrc_bas!PrepareTime) Then
consume = 0
Else
'计算消费时间
If Val(consumetime) Mod Val(mrc_bas!unittime) = 0 Then
t = Int(consumetime / mrc_bas!unittime)
Else
t = Int(consumetime / mrc_bas!unittime) + 1
End If
If mrc_stu.EOF Then
MsgBox "该同学没有注册或者是已经退卡!", 0 + 46, "提示"
Exit Sub
Else
'判断是固定用户还是临时用户
If Trim(mrc_stu!Type) = Trim("固定用户") Then
consume = t * mrc_bas.Fields(0)
Else
consume = t * mrc_bas.Fields(1)
End If
End If
End If
'计算余额(上机时候余额显示减去消费余额)
txtcash = Val(mrc_stu!cash) - consume
'更新数据到line_info表
txtsql = "select * from line_info where cardno='" & sz(z) & "' and ondate='" & mrc_online!ondate & "' and ontime='" & mrc_online!OnTime & "'"
Set mrc1 = ExecuteSQL(txtsql, msgtext)
mrc1.Fields(1) = sz(z)
mrc1.Fields(2) = Trim(mrc_stu.Fields(1))
mrc1.Fields(3) = Trim(mrc_stu.Fields(2))
mrc1.Fields(4) = Trim(mrc_stu.Fields(4))
mrc1.Fields(5) = Trim(mrc_stu.Fields(3))
mrc1.Fields(6) = Trim(mrc_online.Fields(6))
mrc1.Fields(7) = Trim(mrc_online.Fields(7))
mrc1.Fields(10) = consumetime
mrc1.Fields(11) = consume
mrc1.Fields(12) = txtcash
mrc1.Fields(13) = "正常下机"
mrc1.Fields(14) = GetThisComputerName
mrc1.Fields(8) = Date
mrc1.Fields(9) = Time
mrc1.Update
mrc1.Close
mrc_stu.Close
mrc_online.Close
'更新表student
txtsql = "select * from student_info where cardno='" & sz(z) & "'"
Set mrc1 = ExecuteSQL(txtsql, msgtext)
mrc1.Fields(7) = txtcash
mrc1.Update
'更新表Online_info
txtsql1 = "delete online_info where cardno='" & sz(z) & "'"
Set mrc_online = ExecuteSQL(txtsql1, msgtext)
Next z
'更新msflexgrid1的界面
For s = 0 To i - 1
If .rows > 2 Then
.RemoveItem xh(s)
Else
.Clear
.Enabled = False
End If
Next s
End With
End Sub
全部下机和选择下机差不多,使用循环语句将所有学生下线,结算好账单,更新相联系的表。