结账这部分算是机房收费系统的一个难点，之所以它难，是因为这个窗体不光涉及到代码，而且还涉及到算数的问题，还和报表有关，所以它是个难点，但是只要把思路弄清楚了，再难的东西也不怕啦！
思维导图：
思路有没有清楚一点呢？
代码片段
先让操作员的名字和label框实现同步：
txtsql = "select * from user_info where userid= '" & Trim(cboOpUserID.Text) & "'"
Set mrc_u = ExecuteSQL(txtsql, msgtext)
If mrc_u.EOF Then
mrc_u.Close
Exit Sub
Else
lblusername.Caption = mrc_u.fields(3)
mrc_u.Close
End If
call viewdata '调用过程
以一个为例：
Private Sub viewdata()
Dim txtsql As String
Dim msgtext As String
Dim mrc As ADODB.Recordset
'从student_info中查找未结账和用户名相一致的学生信息显示出来
txtsql = "select * from student_info where userid= '" & Trim(cboOpUserID.Text) & "'and ischeck='" & "未结账" & "'"
Set mrc = ExecuteSQL(txtsql, msgtext)
With MSHFlexGrid1
' .Rows = 1
Do While Not mrc.EOF
.CellAlignment = 4
.Rows = .Rows + 1
.CellAlignment = 4
.TextMatrix(.Rows - 1, 0) = Trim(mrc.fields(0))
.TextMatrix(.Rows - 1, 1) = Trim(mrc.fields(1))
.TextMatrix(.Rows - 1, 2) = Trim(mrc.fields(12))
.TextMatrix(.Rows - 1, 3) = Trim(mrc.fields(13))
mrc.MoveNext
Loop
End With
Call AdjustColWidth(Account, MSHFlexGrid1)
mrc.Close
重头戏来了，让我们看一下结账部分：
首先我们要先弄清楚以下几个关系：
1、售卡张数=student_info表中的总数
2、退卡张数=cancelcard_info表中的总数
3、总售卡张数=售卡张数-退卡张数
4、临时用户金额=当天未结账的临时用户金额
5、应收金额=充值金额-退卡金额（充值金额=充值金额+注册金额）
下面我们来看一下具体的代码应该怎么写：
'充值部分
Dim txtsql1 As String
Dim msgtext1 As String
Dim mrc1 As ADODB.Recordset '连接recharge_info表
'退卡部分
Dim txtsql2 As String
Dim msgtext2 As String
Dim mrc2 As ADODB.Recordset '连接cancel_info表
'临时用户部分
Dim txtsql3 As String
Dim msgtext3 As String
Dim mrc3 As ADODB.Recordset '连接student_info表
Dim mrc4 As ADODB.Recordset '连接为结账的学生表
Dim mrc_line As ADODB.Recordset '连接line表
Dim mrc_c As ADODB.Recordset '连接checkday表
Dim txtsql As String
Dim msgtext As String
Dim mrcstudent As ADODB.Recordset
Dim mrccancel As ADODB.Recordset
txtsql = "select * from student_info "
Set mrcstudent = ExecuteSQL(txtsql, msgtext)
txtsql = "select * from cancelcard_info "
Set mrccancel = ExecuteSQL(txtsql, msgtext)
'把操作员的所有统计信息汇总到汇总列表
txtSellCardSum.Text = mrcstudent.RecordCount '售卡张数
txtBackCardSum.Text = mrccancel.RecordCount ' 退卡张数
'售卡总张数
txtSellCardActual.Text = mrcstudent.RecordCount - mrccancel.RecordCount
'退卡金额
txtsql2 = "select sum(cancelcash) from cancelcard_info where userid= '" & Trim(cboOpUserID) & " 'and status='" & "未结账" & "'"
Set mrc2 = ExecuteSQL(txtsql2, msgtext2)
If IsNull(mrc2.fields(0)) Then
txtBackCardMoney.Text = "0"
Else
txtBackCardMoney.Text = mrc2.fields(0)
End If
'充值金额
txtsql1 = "select sum(addmoney) from recharge_info where userid= '" & Trim(cboOpUserID) & " 'and status='" & "未结账" & "'"
Set mrc1 = ExecuteSQL(txtsql1, msgtext1)
If IsNull(mrc1.fields(0)) Then
txtRecharge.Text = "0"
Else
txtRecharge.Text = mrc1.fields(0)
End If
'临时金额
txtsql3 = "select sum(cash) from student_info where userid= '" & Trim(cboOpUserID) & "'and type='" & "临时用户" & "'and ischeck='" & "未结账" & "'"
Set mrc3 = ExecuteSQL(txtsql3, msgtext3)
If IsNull(mrc3.fields(0)) Then
txtTmpRecharge.Text = "0"
Else
txtTmpRecharge.Text = mrc3.fields(0)
End If
'应收金额
'应收金额=充值金额-退卡金额，也就是挣得的钱，充值金额包括充值金额+注册金额
txtCollectMoney.Text = Val(txtRecharge.Text) + Val(txtTmpRecharge.Text) - Val(txtBackCardMoney.Text)
'充值部分
Dim txtsql1 As String
Dim msgtext1 As String
Dim mrc1 As ADODB.Recordset '连接recharge_info表
'退卡部分
Dim txtsql2 As String
Dim msgtext2 As String
Dim mrc2 As ADODB.Recordset '连接cancel_info表
'临时用户部分
Dim txtsql3 As String
Dim msgtext3 As String
Dim mrc3 As ADODB.Recordset '连接student_info表
Dim mrc4 As ADODB.Recordset '连接为结账的学生表
Dim mrc_line As ADODB.Recordset '连接line表
Dim mrc_c As ADODB.Recordset '连接checkday表
Dim txtsql As String
Dim msgtext As String
Dim mrcstudent As ADODB.Recordset
Dim mrccancel As ADODB.Recordset
txtsql = "select * from student_info "
Set mrcstudent = ExecuteSQL(txtsql, msgtext)
txtsql = "select * from cancelcard_info "
Set mrccancel = ExecuteSQL(txtsql, msgtext)
'把操作员的所有统计信息汇总到汇总列表
txtSellCardSum.Text = mrcstudent.RecordCount '售卡张数
txtBackCardSum.Text = mrccancel.RecordCount ' 退卡张数
'售卡总张数
txtSellCardActual.Text = mrcstudent.RecordCount - mrccancel.RecordCount
'退卡金额
txtsql2 = "select sum(cancelcash) from cancelcard_info where userid= '" & Trim(cboOpUserID) & " 'and status='" & "未结账" & "'"
Set mrc2 = ExecuteSQL(txtsql2, msgtext2)
If IsNull(mrc2.fields(0)) Then
txtBackCardMoney.Text = "0"
Else
txtBackCardMoney.Text = mrc2.fields(0)
End If
'充值金额
txtsql1 = "select sum(addmoney) from recharge_info where userid= '" & Trim(cboOpUserID) & " 'and status='" & "未结账" & "'"
Set mrc1 = ExecuteSQL(txtsql1, msgtext1)
If IsNull(mrc1.fields(0)) Then
txtRecharge.Text = "0"
Else
txtRecharge.Text = mrc1.fields(0)
End If
'临时金额
txtsql3 = "select sum(cash) from student_info where userid= '" & Trim(cboOpUserID) & "'and type='" & "临时用户" & "'and ischeck='" & "未结账" & "'"
Set mrc3 = ExecuteSQL(txtsql3, msgtext3)
If IsNull(mrc3.fields(0)) Then
txtTmpRecharge.Text = "0"
Else
txtTmpRecharge.Text = mrc3.fields(0)
End If
'应收金额
'应收金额=充值金额-退卡金额，也就是挣得的钱，充值金额包括充值金额+注册金额
txtCollectMoney.Text = Val(txtRecharge.Text) + Val(txtTmpRecharge.Text) - Val(txtBackCardMoney.Text)
将结账的钱数同步到日结账里
Dim mrcline As ADODB.Recordset
Dim mrccheckday As ADODB.Recordset
Dim mrcrecharge As ADODB.Recordset
Dim lastrecharge As Integer
Dim LastExitcash As Integer
Dim RemainCash As Integer
Dim RechargeCash As Integer
Dim ConsumeCash As Integer
Dim cancelcash As Integer
'计算上期充值卡余额（上期就是昨天的钱=充值卡的钱-退卡的钱）
'计算昨天充值卡里面的余额
txtsql = "select sum(addmoney) from recharge_info where date= '" & Date - 1 & "'"
Set mrcrecharge = ExecuteSQL(txtsql, msgtext)
'判断充值卡里面的钱是否为空
If IsNull(mrcrecharge.fields(0)) = True Then
lastrecharge = "0"
Else
'引用sum函数，默认的addmoney列为field(0)
lastrecharge = mrcrecharge.fields(0)
'计算昨天退卡表里面的钱
txtsql = "select sum(cancelcash) from cancelcard_info where date= '" & Date - 1 & "'"
Set mrccancel = ExecuteSQL(txtsql, msgtext)
If IsNull(mrccancel.fields(0)) = True Then
LastExitcash = "0"
Else
RemainCash = lastrecharge - LastExitcash
End If
End If
'计算当日充值金额
txtsql = "select sum(addmoney) from recharge_info where date='" & Date & "'"
Set mrcrecharge = ExecuteSQL(txtsql, msgtext)
If IsNull(mrcrecharge.fields(0)) Then
RechargeCash = "0"
Else
RechargeCash = mrcrecharge.fields(0)
End If
'计算当日消费金额
txtsql = "select sum(consume) from line_info where offdate= '" & Date & "'"
Set mrcline = ExecuteSQL(txtsql, msgtext)
If IsNull(mrcline.fields(0)) Then
ConsumeCash = "0"
Else
ConsumeCash = mrcline.fields(0)
End If
'计算当日退还金额
txtsql = "select sum(cancelcash) from cancelcard_info where date= '" & Date & "'"
Set mrccancel = ExecuteSQL(txtsql, msgtext)
If IsNull(mrccancel.fields(0)) Then
cancelcash = "0"
Else
cancelcash = mrccancel.fields(0)
End If
'清楚当天记录
txtsql = "delete checkday_info where date= '" & Date & "'"
Set mrccheckday = ExecuteSQL(txtsql, msgtext)
'将数据同步到checkday_info 表中
txtsql = "select * from checkday_info "
Set mrccheckday = ExecuteSQL(txtsql, msgtext)
mrccheckday.AddNew
mrccheckday!RemainCash = Val(RemainCash)
mrccheckday!RechargeCash = Val(RechargeCash)
mrccheckday!ConsumeCash = Val(ConsumeCash)
mrccheckday!cancelcash = Val(cancelcash)
mrccheckday!allcash = Val(RechargeCash) - Val(cancelcash)
mrccheckday!Date = Date
mrccheckday.Update
mrccheckday.Close
MsgBox "结账成功!", 0 + 46, "提示"
txtsql = "select * from student_info where date = '" & Date & "'and ischeck= '" & "未结账" & "'"
Set mrcstudent = ExecuteSQL(txtsql, msgtext)
Do While Not mrcstudent.EOF
mrcstudent!ischeck = "结账"
mrcstudent.Update
mrcstudent.MoveNext
Loop
mrcstudent.Close
txtsql = "select * from recharge_info where date= '" & Date & " 'and status= '" & "未结账" & "'"
Set mrcrecharge = ExecuteSQL(txtsql, msgtext)
Do While Not mrcrecharge.EOF
mrcrecharge!status = "结账"
mrcrecharge.Update
mrcrecharge.MoveNext
Loop
mrcrecharge.Close
txtsql = "select * from cancelcard_info where date = '" & Date & "'and status='" & "未结账" & "'"
Set mrccancel = ExecuteSQL(txtsql, msgtext)
Do While Not mrccancel.EOF
mrccancel!status = "结账"
mrccancel.Update
mrccancel.MoveNext
Loop
mrccancel.Close
最后在窗体加载事件中写以下代码：
Private Sub Form_Load()
Dim msgtext As String
Dim txtsql As String
Dim mrc_u As ADODB.Recordset
txtsql = "select * from user_info where level='" & "操作员" & "'or level= '" & "管理员" & "'"
Set mrc_u = ExecuteSQL(txtsql, msgtext)
If mrc_u.RecordCount = 0 Then
MsgBox "无操作员记录", 0 + 48, "提示"
Exit Sub
Else
Do While mrc_u.EOF = False
cboOpUserID.AddItem mrc_u.fields(0)
mrc_u.MoveNext
Loop
End If
'为三张表添加表头
With MSHFlexGrid1
.Rows = 1
.CellAlignment = 4
.TextMatrix(0, 0) = "学号"
.TextMatrix(0, 1) = "卡号"
.TextMatrix(0, 2) = "日期"
.TextMatrix(0, 3) = "时间"
End With
With MSHFlexGrid2
.Rows = 1
.CellAlignment = 4
.TextMatrix(0, 0) = "学号"
.TextMatrix(0, 1) = "卡号"
.TextMatrix(0, 2) = "充值金额"
.TextMatrix(0, 3) = "日期"
.TextMatrix(0, 4) = "时间"
End With
With MSHFlexGrid3
.Rows = 1
.CellAlignment = 4
.TextMatrix(0, 0) = "学号"
.TextMatrix(0, 1) = "卡号"
.TextMatrix(0, 2) = "日期"
.TextMatrix(0, 3) = "时间"
.TextMatrix(0, 4) = "退卡金额"
End With
With MSHFlexGrid4
.Rows = 1
.CellAlignment = 4
.TextMatrix(0, 0) = "学号"
.TextMatrix(0, 1) = "卡号"
.TextMatrix(0, 2) = "日期"
.TextMatrix(0, 3) = "时间"
End With
End Sub
![机房收费系统（一）登录[图]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)