逻辑,做事先理好逻辑, 先干什么,再干什么。在写代码就思路清晰的多。
下机要清算好,消费金额, 不能把账算错。
画个流程图
代码部分
Private Sub cmdOffline_Click()
Dim txtSQL As String
Dim Msgtext As String
Dim mrc As ADODB.Recordset
Dim mrc1 As ADODB.Recordset
Dim mrc2 As ADODB.Recordset
Dim mrc3 As ADODB.Recordset
Dim mrc4 As ADODB.Recordset
Dim StartDate As Date
Dim enddate As Date
Dim StartTime As Date
Dim Endtime As Date
Dim CostDate As Long
Dim CostTime As Long
Dim a As Integer
'mrc连接online表
txtSQL = "select * from online_info where cardno='" & Trim(txtCardNo.Text) & "'"
Set mrc = ExecuteSQL(txtSQL, Msgtext)
'mrc1连接line表
txtSQL = "select * from line_info where cardno='" & Trim(txtCardNo.Text) & "'"
Set mrc1 = ExecuteSQL(txtSQL, Msgtext)
'mrc2连接student表
txtSQL = "select * from student_info where cardno='" & Trim(txtCardNo.Text) & "'"
Set mrc2 = ExecuteSQL(txtSQL, Msgtext)
'mrc3连接basicdata表
txtSQL = "select * from basicdata_info"
Set mrc3 = ExecuteSQL(txtSQL, Msgtext)
'mrc4连接cancelcard表
txtSQL = "select * from cancelcard_info where cardno='" & Trim(txtCardNo.Text) & "'"
Set mrc4 = ExecuteSQL(txtSQL, Msgtext)
If Trim(txtCardNo.Text) = "" Then
MsgBox "请输入卡号!", 48, "警告"
txtCardNo.SetFocus
Exit Sub
End If
If IsNumeric(txtCardNo.Text) = False Then
MsgBox "卡号请输入数字!", 48, "警告"
txtCardNo.SetFocus
txtCardNo.Text = ""
Exit Sub
End If
If Trim(mrc2.Fields(0)) <> Trim(txtCardNo.Text) Then
MsgBox "该卡号未注册!", 48, "警告"
Exit Sub
End If
If mrc2.Fields(0) = Trim(txtCardNo.Text) Then
MsgBox "学生没有上机,无法下机!", 48, "警告"
Exit Sub
End If
'计算消费时间
txtOffDate.Text = Date
txtOffTime.Text = Time
' CostDate = DateDiff("n", txtOnDate.Text, txtOffDate.Text)
' CostTime = DateDiff("n", txtOnTime.Text, txtOffTime.Text)
CostTime = DateDiff("n", StartTime, Endtime)
txtCTime = (Val(CostDate) + Val(CostTime))
'计算消费金额
If Trim(txtCTime.Text) <= Val(mrc3.Fields(4)) Then
txtCMoney.Text = 0
Else
If Trim(txtType.Text) = "固定用户" Then
txtCMoney.Text = Format(Val(txtCTime.Text) / mrc3.Fields(2) * mrc3.Fields(0), "0.00")
Else
txtCMoney.Text = Format(Val(txtCTime.Text) / mrc3.Fields(2) * mrc3.Fields(1), "0.00")
End If
End If
'计算余额
txtBaLance.Text = Val(mrc2.Fields(7)) - Val(Trim(txtCMoney.Text))
' 删除online_info表上信息
txtSQL = "Delete from online_info where cardno='" & Trim(txtCardNo.Text) & "'"
mrc.Delete
mrc.Update
mrc.Close
'更新line_info表
With mrc1
' .Fields(6) = Date
' .Fields(7) = Time
.Fields(8) = Date
.Fields(9) = Time
.Fields(10) = Trim(txtCTime.Text)
.Fields(11) = Trim(txtCMoney.Text)
.Fields(12) = Trim(txtBaLance.Text)
.Fields(13) = "正常下机"
.Fields(14) = VBA.Environ("computername")
.Update
.Close
End With
'更新学生表
With mrc2
.Fields(7) = txtBaLance.Text
.Update
.Close
End With
MsgBox "下机成功"
txtCardNo = ""
txtSID = ""
txtDept = ""
txtType = ""
txtName = ""
txtSex = ""
txtOnDate = ""
txtOffDate = ""
txtBaLance = ""
txtOnTime = ""
txtOffTime = ""
txtCTime = ""
txtCMoney = ""
End Sub