第一次机房收费——下机流程

Private Sub comOffLine_Click() '点击下机按钮
'定义消费时间
    Dim consumetime As String
    Dim txtSQL As String
    Dim MsgText As String
    Dim Day, Minute As String '计算日期差与时间差
    Dim mrcstu As ADODB.Recordset  '连接student表
    Dim mrcData As ADODB.Recordset '连接BasicData表
    Dim mrcOnline As ADODB.Recordset '连接Online表
    Dim mrcline As ADODB.Recordset '连接line表
'判断卡号是否为空
    If Trim(txtCardNo.Text) = "" Then
        MsgBox "卡号为空！", 64, "温馨提示"
        txtCardNo.SetFocus
        Exit Sub
    End If
'判断该号是否上机
    txtSQL = "select * from online_info where cardno='" & Trim(txtCardNo.Text) & "'"
    Set mrcOnline = ExecuteSQL(txtSQL, MsgText)
'连接student表
    txtSQL = "select * from student_info where cardno='" & Trim(txtCardNo.Text) & "'"
    Set mrcstu = ExecuteSQL(txtSQL, MsgText)
    If mrcOnline.EOF = True Then
        MsgBox "该号没有上机", 64, "温馨提示"
        txtCardNo.Text = ""
        txtCardNo.SetFocus
    Else
        '计算消费时间
        Day = DateDiff("9", mrcOnline.Fields(6), Date) '计算上机下机的日期查
        Minute = DateDiff("n", mrcOnline.Fields(7), Time) '计算上机下机的同日时间差
        txtCTime.Text = 3600 * Val(Day) + Minute          '计算总时间差，分钟为单位
        '消费时间
        consumetime = txtCTime.Text
        txtSQL = "select * from BasicData_info"
        Set mrcData = ExecuteSQL(txtSQL, MsgText)
        '判断是否小于准备时间
        If consumetime < mrcData.Fields(4) Then
            txtCMoney.Text = "0"
            txtCTime.Text = "0"
            txtOffdate.Text = Date
            txtOfftime.Text = Time
            txtBalance.Text = Trim(mrcstu.Fields(7))
        Else
            If Trim(mrcOnline.Fields(1)) = "固定用户" Then
                'round是将数据四舍五入，保留两位小数
                txtCMoney.Text = Round((mrcData.Fields(0)) / 60 * consumetime)
                txtBalance.Text = Val(mrcstu.Fields(7)) - Val(txtCMoney.Text)
            Else
                txtCMoney.Text = Round((mrcData.Fields(1) / 60 * consumetime))
                '计算临时用户金额
                txtBalance.Text = Val(mrcstu.Fields(7)) - Val(txtCMoney.Text)
                mrcstu.Fields(7) = Trim(txtBalance.Text)
                mrcstu.Fields(11) = "未结账"
            End If
            txtSQL = "select * from Line_info where cardno='" & Trim(txtCardNo.Text) & "'"
            Set mrcline = ExecuteSQL(txtSQL, MsgText)
            mrcline.AddNew '添加新纪录
            mrcline.Fields(1) = mrcOnline.Fields(0) '卡号
            mrcline.Fields(2) = mrcOnline.Fields(2) '学号
            mrcline.Fields(3) = mrcOnline.Fields(3) '姓名
            mrcline.Fields(4) = mrcOnline.Fields(1) '系别
            mrcline.Fields(5) = mrcOnline.Fields(5) '性别
            mrcline.Fields(6) = mrcOnline.Fields(6) '上机日期
            mrcline.Fields(7) = mrcOnline.Fields(7) '上机时间
            mrcline.Fields(8) = Date '下机日期
            mrcline.Fields(9) = Time '下机日期
            mrcline.Fields(10) = consumetime '消费时间
            mrcline.Fields(11) = Trim(txtCMoney.Text) '消费金额
            mrcline.Fields(12) = Trim(txtBalance.Text) '余额
            mrcline.Fields(13) = "正常下机"
            mrcline.Fields(14) = Trim(VBA.Environ("computername")) '计算机环境
            mrcline.Update '数据更新
            mrcstu.Fields(7) = Trim(txtBalance.Text) '学生表里，，余额
            mrcstu.Fields(11) = "未结账"
            mrcstu.Update
            '显示数据
            '将student表内的数据展示出来
            txtType.Text = Trim(mrcstu.Fields(14)) '类型
            txtSID.Text = Trim(mrcstu.Fields(1)) '学号
            txtName.Text = Trim(mrcstu.Fields(2)) '姓名
            txtDept.Text = Trim(mrcstu.Fields(4)) '系别
            txtSex.Text = Trim(mrcstu.Fields(3)) '性别
            txtOnDate.Text = Trim(mrcline.Fields(6))   '上机日期
            txtOnTime.Text = Trim(mrcline.Fields(7)) '上机时间
            txtOffdate.Text = Date '下机日期
            txtOfftime.Text = Time '下机时间
            MsgBox "下机成功", 64, "温馨提示"
            mrcOnline.Delete
        End If
    End If
End Sub