前言:
上下机似乎一直都被认为很难,那么我们今天来讨论一下上机的问题。
总结:
1.先来一张流程图:
上机需要判断多个条件,最终的目的就是看卡号是否满足上机的条件,仅此而已,这样想,上机还是很简单的。
2.涉及表
流程很简单,但是中间涉及到几张表,分别是:student_Info;Online_Info;Line_Info;BasicData_Info;
ReCharge_Info五张表。一般情况下只涉及前三张表,后两个是先判断钱是否够,如果不够应及时充值。
'判断卡号是否为空,如果为空,提醒输入;
'若不为空,判断是否注册过,如果注册了就继续往下执行;
'没有注册包括:数据集中没有这个卡号;数据集中有这个卡号,但是该卡已经停用!
If txtcardno.Text = "" Then
MsgBox "卡号不能为空,请输入!", 48, "提示"
txtcardno.SetFocus
Exit Sub
Else
'判断卡号是否为数字
If Not IsNumeric(Trim(txtcardno.Text)) Then
MsgBox "请输入数字!", vbOKOnly + vbExclamation, "提示"
txtcardno.SetFocus
txtcardno.Text = ""
Exit Sub
Else
txtSQL = "select * from student_info where cardno= '" & txtcardno.Text & "'"
Set mrcstu = ExecuteSQL(txtSQL, Msgtext)
'数据集中没有记录的情况
If mrcstu.EOF = True Then
MsgBox "该卡没有注册,请重新输入!", 48, "提示"
txtcardno.Text = ""
txtcardno.SetFocus
Exit Sub
Else
'判断是否上机了,一个卡不能重复上机;
'如果已经上机,显示上机,若没有上机,进行正常上机
'正常上机后将文本框中的数据添加到online_info表中
'判断是否上机
txtSQL = "select * from online_info where cardno= '" & txtcardno.Text & "'"
Set mrconline = ExecuteSQL(txtSQL, Msgtext)
If mrconline.EOF = False Then
MsgBox "该卡正在上机,不能重复上机!", 48, "提示"
txtcardno.Text = ""
txtcardno.SetFocus
Exit Sub
Else
'正常使用的卡
txtSQL = "select * from student_info where cardno= '" & Trim(txtcardno.Text) & " 'and status= '使用'"
Set mrcstu = ExecuteSQL(txtSQL, Msgtext)
If mrcstu.EOF = False Then
'将数据库总中的数据显示在文本框中
labStudentno.Caption = Trim(mrcstu.Fields(1))
labDepartment.Caption = Trim(mrcstu.Fields(4))
labCardtype.Caption = Trim(mrcstu.Fields(14))
labStudentname.Caption = Trim(mrcstu.Fields(2))
labSex.Caption = Trim(mrcstu.Fields(3))
labOnlinedate.Caption = Date
labOnlinetime.Caption = Time
labCash.Caption = Trim(mrcstu.Fields(7))
'判断余额是否小于最小金额,若小于,需要充值后再继续上机,强制下机,此时最好弹出充值界面
'此时判断用户类型
txtSQL = "select * from basicdata_info"
Set mrcbas = ExecuteSQL(txtSQL, Msgtext)
If mrcstu.Fields(7) < mrcbas.Fields(5) Then
MsgBox "余额小于最小限制金额,请充值后再上机!", 48, "提示"
'弹出充值窗口
frmOpeRecharge.Show
SetParent frmOpeRecharge.hWnd, Picture1.hWnd
Exit Sub
End If
Else
'数据集中有记录,但是该卡已经停用
txtSQL = "select * from student_info where cardno= '" & txtcardno.Text & "'and status= '不使用'"
Set mrcstu = ExecuteSQL(txtSQL, Msgtext)
If mrcstu.EOF = False Then
MsgBox "该卡已经注销,请重新输入!", 48, "提示"
txtcardno.Text = ""
txtcardno.SetFocus
Exit Sub
End If
End If
End If
End If
End If
End If
'上机时将上机卡的数据同步至online_info表中
Set mrconline = New ADODB.Recordset
txtSQL = "select * from online_info"
Set mrconline = ExecuteSQL(txtSQL, Msgtext)
mrconline.AddNew
mrconline.Fields(0) = Trim(txtcardno.Text)
mrconline.Fields(1) = Trim(labCardtype.Caption)
mrconline.Fields(2) = Trim(labStudentno.Caption)
mrconline.Fields(3) = Trim(labStudentname.Caption)
mrconline.Fields(4) = Trim(labDepartment.Caption)
mrconline.Fields(5) = Trim(labSex.Caption)
mrconline.Fields(6) = Date
mrconline.Fields(7) = Time
mrconline.Fields(8) = VBA.Environ("computername") '将计算机名同步到数据库的相应表格中
mrconline.Fields(9) = Now
labCount.Caption = mrconline.RecordCount + 1 '显示上机人数
mrconline.Update
mrconline.Close
'上机时将上机卡的数据同步到line_info表中
Set mrcline = New ADODB.Recordset
txtSQL = "select * from line_info"
Set mrcline = ExecuteSQL(txtSQL, Msgtext)
mrcline.AddNew
mrcline.Fields(1) = Trim(txtcardno.Text)
mrcline.Fields(2) = Trim(labStudentno.Caption)
mrcline.Fields(3) = Trim(labStudentname.Caption)
mrcline.Fields(4) = Trim(labDepartment.Caption)
mrcline.Fields(5) = Trim(labSex.Caption)
mrcline.Fields(6) = Trim(labOnlinedate.Caption)
mrcline.Fields(7) = Trim(labOnlinetime.Caption)
mrcline.Fields(12) = Trim(labCash.Caption)
mrcline.Fields(13) = "正常上机"
mrcline.Fields(14) = Trim(VBA.Environ("computername"))
mrcline.Update
' mrcline.Close
MsgBox "上机完成!", 48, "提示"
3.我的建议:
这是我的窗体,除了卡号之外,其他的都是从数据库直接查询显示出来的,不需要手动输入或者修改,所以我就用label来直接显示,这样也就避免了用户无意中修改的情况。(之后的查询余额窗体也可以这样哦!)