一、前言
进入机房到结账这儿增加了一个新控件SSTab，工程——部件——Microsoft Tabbed Dialog Control 6.0
后面具体的SStab控件的设计我就不说了。
二、设计界面
这儿我将ComboBox控件改成了text和label控件并增加一个命令按钮相互配合,删除了退出
三、具体思路
购卡：联系student_info中此操作员购买的卡
充值：联系recharge_info表中此操作员充值 且未结账的卡
退卡：联系cancelcard_info表中此操作员退的卡 且 未结账的卡
临时用户：student_info表中此操作员 type为临时用户 未结账 未退卡的卡
汇总：
售卡张数：购卡选项卡记录数
退卡张数：退卡选项卡记录数
充值金额：recharge_info 表中金额总数
临时收费金额：student_info表中临时用户的金额总数
退卡金额：cancelcard_info 表中金额总数
总售卡数：售卡张数-退卡张数
应收金额:充值金额-退卡金额
四、结账代码
Dim txtSQL, Msgtext As String
Dim mrc_stu As ADODB.Recordset '购卡 售卡数
Dim mrc_re As ADODB.Recordset '充值
Dim mrc_cc As ADODB.Recordset '退卡
Dim mrc_stut As ADODB.Recordset '临时用户
Dim mrc As ADODB.Recordset '退卡金额
Dim mrc1 As ADODB.Recordset '退卡张数
Dim mrc2 As ADODB.Recordset '充值金额
Dim mrc3 As ADODB.Recordset '临时金额
'购卡
With myflexgrid1
txtSQL = "select * from student_info where userid='" & Trim(txtUserID.Text) & "'"
Set mrc_stu = ExecuteSQL(txtSQL, Msgtext)
.Rows = 1
.CellAlignment = 4
.TextMatrix(0, 0) = "学号"
.TextMatrix(0, 1) = "卡号"
.TextMatrix(0, 2) = "日期"
.TextMatrix(0, 3) = "时间"
.TextMatrix(0, 4) = "金额"
Do While Not mrc_stu.EOF
.Rows = .Rows + 1
.CellAlignment = 4
.TextMatrix(.Rows - 1, 0) = Trim(mrc_stu.Fields(1))
.TextMatrix(.Rows - 1, 1) = Trim(mrc_stu.Fields(0))
.TextMatrix(.Rows - 1, 2) = Trim(mrc_stu!Date)
.TextMatrix(.Rows - 1, 3) = Trim(mrc_stu!Time)
.TextMatrix(.Rows - 1, 4) = Trim(mrc_stu!cash)
mrc_stu.MoveNext
Loop
End With
'充值
With myflexgrid2
.Rows = 1
.CellAlignment = 4
.TextMatrix(0, 0) = "学号"
.TextMatrix(0, 1) = "卡号"
.TextMatrix(0, 2) = "充值金额"
.TextMatrix(0, 3) = "日期"
.TextMatrix(0, 4) = "时间"
txtSQL = "select * from recharge_info where userid='" & Trim(txtUserID.Text) & "' and status='未结账'"
Set mrc_re = ExecuteSQL(txtSQL, Msgtext)
Do While Not mrc_re.EOF
.Rows = .Rows + 1
.CellAlignment = 4
.TextMatrix(.Rows - 1, 0) = Trim(mrc_re!studentno)
.TextMatrix(.Rows - 1, 1) = Trim(mrc_re!cardno)
.TextMatrix(.Rows - 1, 2) = Trim(mrc_re!addmoney)
.TextMatrix(.Rows - 1, 3) = Trim(mrc_re!Date)
.TextMatrix(.Rows - 1, 4) = Trim(mrc_re!Time)
mrc_re.MoveNext
Loop
End With
'退卡
With myflexgrid3
.Rows = 1
.CellAlignment = 4
.TextMatrix(0, 0) = "学号"
.TextMatrix(0, 1) = "卡号"
.TextMatrix(0, 2) = "日期"
.TextMatrix(0, 3) = "时间"
.TextMatrix(0, 4) = "退卡金额"
txtSQL = "select * from cancelcard_info where userid='" & Trim(txtUserID.Text) & "' and status='未结账'"
Set mrc_cc = ExecuteSQL(txtSQL, Msgtext)
Do While Not mrc_cc.EOF
.Rows = .Rows + 1
.CellAlignment = 4
.TextMatrix(.Rows - 1, 0) = Trim(mrc_cc!studentno)
.TextMatrix(.Rows - 1, 1) = Trim(mrc_cc!cardno)
.TextMatrix(.Rows - 1, 2) = Trim(mrc_cc!Date)
.TextMatrix(.Rows - 1, 3) = Trim(mrc_cc!Time)
.TextMatrix(.Rows - 1, 4) = Trim(mrc_cc.Fields(2))
mrc_cc.MoveNext
Loop
End With
'临时用户
With myflexgrid4
.Rows = 1
.CellAlignment = 4
.TextMatrix(0, 0) = "学号"
.TextMatrix(0, 1) = "卡号"
.TextMatrix(0, 2) = "日期"
.TextMatrix(0, 3) = "时间"
txtSQL = "select * from student_info where userid='" & Trim(txtUserID.Text) & "' and type='临时用户' and ischeck='未结账'"
Set mrc_stut = ExecuteSQL(txtSQL, Msgtext)
Do While Not mrc_stut.EOF
.Rows = .Rows + 1
.CellAlignment = 4
.TextMatrix(.Rows - 1, 0) = Trim(mrc_stut!studentno)
.TextMatrix(.Rows - 1, 1) = Trim(mrc_stut.Fields(0))
.TextMatrix(.Rows - 1, 2) = Trim(mrc_stut!Date)
.TextMatrix(.Rows - 1, 3) = Trim(mrc_stut!Time)
mrc_stut.MoveNext
Loop
End With
mrc_stut.Close
mrc_re.Close
mrc_cc.Close
'售卡数
If mrc_stu.RecordCount = 0 Then
txtOutCard.Text = 0
Else
txtOutCard.Text = mrc_stu.RecordCount
End If
'退卡金额
txtSQL = "select sum(cancelcash) from cancelcard_info where userid='" & Trim(txtUserID.Text) & "' and status='未结账'"
Set mrc = ExecuteSQL(txtSQL, Msgtext)
If IsNull(Trim(mrc.Fields(0))) Then
txtOffCardMoney.Text = "0"
Else
txtOffCardMoney.Text = Val(mrc.Fields(0))
End If
'退卡张数
txtSQL = "select * from cancelcard_info where userid='" & Trim(txtUserID.Text) & "' and status='未结账'"
Set mrc1 = ExecuteSQL(txtSQL, Msgtext)
If mrc1.RecordCount = 0 Then
txtOffCard.Text = 0
Else
txtOffCard.Text = mrc1.RecordCount
End If
'总售卡数=售卡数-退卡数
txtALLOutCard.Text = txtOutCard.Text - txtOffCard.Text
'充值金额
txtSQL = "select sum(addmoney) from recharge_info where userid='" & Trim(txtUserID.Text) & "' and status='未结账'"
Set mrc2 = ExecuteSQL(txtSQL, Msgtext)
If IsNull(Trim(mrc.Fields(0))) Then
txtRechargeMoney.Text = "0"
Else
txtRechargeMoney.Text = Val(mrc2.Fields(0))
End If
'应收金额=充值金额-退卡金额
txtGetMoney.Text = txtRechargeMoney.Text - txtOffCard.Text
'临时应收金额
txtSQL = "select sum(cash) from student_info where userid='" & Trim(txtUserID.Text) & "'and type='临时用户' and ischeck='未结账'"
Set mrc3 = ExecuteSQL(txtSQL, Msgtext)
If IsNull(Trim(mrc.Fields(0))) Then
txtTMPMoney.Text = "0"
Else
txtTMPMoney.Text = Val(mrc3.Fields(0))
End If
