对于机房收费的结账，我感觉是所有窗体中，最难的一个。这个窗体我真的做了好多天。它的难度系数我感觉是最高的。
首先，你要理清上机时间和收费标准的关系，在预备时间中，是不收费的。
其次，在超过预备时间，一个单位时间之内，收一个费用。在一个单位时间到两个单位时间内，收两个单位时间的费用。
然后，在结账窗口，链接数据库，显示出所有该用户未结账的信息。
最后，在点击结账时，这个用户的所有未结账信息都结账，并写入数据库。结账窗口查询类信息都为0。并把结账信息写入结账表。已被制作报表使用。
在做结账窗体时，主要是理清思路，这样做起来就容易很多了。我把我的结账窗体和代码放在下面，不做详细的解说，只是给自己留个思路，读者一个方法。
代码仅供参考：
Option ExplicitDim c0 As Variant '用于把最新数据存放到表Dim c1 As VariantDim c2 As VariantDim c3 As VariantDim c4 As VariantDim c5 As VariantPrivate Sub cboUser_Click() Dim txtsql As String Dim MsgText As String Dim mrcU As ADODB.Recordset '使得 用户名的卡号 与 姓名 一致 txtsql = "select Username from User_Info where UserID='" & cboUser.Text & "'" Set mrcU = Executesql(txtsql,MsgText) cboUsername.Text = mrcU.FIElds(0) mrcU.Close Call vIEwdate '更新汇总信息End SubPrivate Sub cboUsername_Click() Dim txtsql As String Dim MsgText As String Dim mrcU As ADODB.Recordset '用户名的卡号 与 姓名 一致 txtsql = "select UserID from User_Info where Username='" & cboUsername.Text & "'" Set mrcU = Executesql(txtsql,MsgText) cboUser.Text = mrcU.FIElds(0) mrcU.Close Call vIEwdate '更新信息End SubPrivate Sub vIEwdate() '根据已经选择好人员信息来修改sstab里面的汇总信息 Dim txtsql As String Dim MsgText As String Dim mrcSD As ADODB.Recordset Dim mrcRC As ADODB.Recordset Dim mrcCC As ADODB.Recordset Dim RechargeCash As Variant '用于存储，充值的 所有金额 Dim cancelCash As Variant '用于存储，退钱的 所有金额 '把他的所有信息，未结账的显示出来 'txtsql = "select studentNo,cardno,date,time from student_Info where Ischeck = '未结帐' and UserID='" & cboUser.Text & "'" 'Set mrcSD = Executesql(txtsql,MsgText)'由于有空格，找了好久才找到 txtsql = "select studentNo,time from student_Info where Ischeck='未结账' and UserID='" & cboUser.Text & "'" Set mrcSD = Executesql(txtsql,MsgText) MfgBUGCard.Rows = mrcSD.RecordCount + 1 With MfgBUGCard .Row = 0 While mrcSD.EOF = False .Row = .Row + 1 .TextMatrix(.Row,0) = " " & mrcSD.FIElds(0) .TextMatrix(.Row,1) = " " & mrcSD.FIElds(1) .TextMatrix(.Row,2) = " " & mrcSD.FIElds(2) .TextMatrix(.Row,3) = " " & mrcSD.FIElds(3) mrcSD.MoveNext Wend End With '把该 *** 作员的所有未结账的充值信息汇总到表格，一个注册信息对应一个充值信息 txtsql = "select studentNo,addmoney,time from ReCharge_Info where status='未结账' and UserID='" & cboUser.Text & "'" Set mrcRC = Executesql(txtsql,MsgText) MfgRecharge.Rows = mrcRC.RecordCount + 1 With MfgRecharge .Row = 0 While Not mrcRC.EOF .Row = .Row + 1 .TextMatrix(.Row,0) = mrcRC.FIElds(0) .TextMatrix(.Row,1) = mrcRC.FIElds(1) .TextMatrix(.Row,2) = mrcRC.FIElds(2) .TextMatrix(.Row,3) = mrcRC.FIElds(3) .TextMatrix(.Row,4) = mrcRC.FIElds(4) RechargeCash = RechargeCash + mrcRC.FIElds(2) mrcRC.MoveNext Wend End With '把所有信息汇总到表格 txtsql = "select studentNo,cardNo,time,cancelcash from CancelCard_Info where Ischeck='未结账' and UserID='" & cboUser.Text & "'" Set mrcCC = Executesql(txtsql,MsgText) MfgCancelCard.Rows = mrcCC.RecordCount + 1 With MfgCancelCard .Row = 0 While mrcCC.EOF = False .Row = .Row + 1 .TextMatrix(.Row,0) = " " & mrcCC.FIElds(0) .TextMatrix(.Row,1) = " " & mrcCC.FIElds(1) .TextMatrix(.Row,2) = " " & mrcCC.FIElds(2) .TextMatrix(.Row,3) = " " & mrcCC.FIElds(3) .TextMatrix(.Row,4) = " " & mrcCC.FIElds(4) cancelCash = cancelCash + mrcCC.FIElds(4) mrcCC.MoveNext Wend End With '然后，把 *** 作员 的所有统计 信息 汇总 到 汇总列表 txtSaleCard = mrcSD.RecordCount txtCancelCard = mrcCC.RecordCount txtRechargeCash = RechargeCash txtCancelCash = cancelCash txtSumCard = Val(txtSaleCard.Text) - Val(txtCancelCard.Text) txtSumCash = Val(txtRechargeCash.Text) - Val(txtCancelCash.Text) mrcSD.Close '关闭释放空间 mrcRC.Close mrcCC.CloseEnd SubPrivate Sub cmdCheckOut_Click() '把该 *** 作员 有关的 未结账信息 修改成 已结账，------并写报表 Dim txtsql As String Dim MsgText As String Dim mrcSD As ADODB.Recordset Dim mrcRC As ADODB.Recordset Dim mrcCC As ADODB.Recordset Dim mrcCO As ADODB.Recordset txtsql = "select Ischeck from student_Info where Ischeck='未结账' and UserID='" & cboUser.Text & "'" Set mrcSD = Executesql(txtsql,MsgText) txtsql = "select status from Recharge_Info where status='未结账' and UserID='" & cboUser.Text & "'" Set mrcRC = Executesql(txtsql,MsgText) txtsql = "select Ischeck from CancelCard_Info where Ischeck='未结账' and UserID='" & cboUser.Text & "'" Set mrcCC = Executesql(txtsql,MsgText) While mrcSD.EOF = False '修改数据库 并且 更新 mrcSD.FIElds(0) = "已结账" mrcSD.Update '更新数据库 mrcSD.MoveNext Wend While mrcRC.EOF = False mrcRC.FIElds(0) = "已结帐" mrcRC.Update mrcRC.MoveNext Wend While mrcCC.EOF = False mrcCC.FIElds(0) = "已结帐" mrcCC.Update mrcCC.MoveNext Wend mrcSD.Close '关闭释放空间 mrcRC.Close mrcCC.Close '记录修改成功 将刚才的 *** 作写入 日报表 Call ReportDay Call ReportSum MsgBox "结账成功",vbOKOnly + vbinformation,"提示" Dim mrcU As ADODB.Recordset '使得 用户名的卡号 与 姓名 一致 txtsql = "select UserID from User_Info where Username='" & cboUsername.Text & "'" Set mrcU = Executesql(txtsql,MsgText) cboUser.Text = mrcU.FIElds(0) mrcU.Close Call vIEwdate '更新 汇总信息 End SubPrivate Sub Form_Load() Dim txtsql As String Dim MsgText As String Dim mrcU As ADODB.Recordset sstab.Tab = 0 '设置 刚刚载入窗体的时候 的 选项卡位置 '初始化 表格 的标题 With MfgBUGCard .TextMatrix(0,0) = "学号" .TextMatrix(0,1) = "卡号" .TextMatrix(0,2) = "日期" .TextMatrix(0,3) = "时间" End With With MfgRecharge .TextMatrix(0,2) = "充值金额" .TextMatrix(0,3) = "日期" .TextMatrix(0,4) = "时间" End With With MfgCancelCard .TextMatrix(0,3) = "时间" .TextMatrix(0,4) = "退卡金额" End With '把数据库中 的 *** 作员的 卡号 和 姓名 提取出来 供选择 txtsql = "select UserID,Username from User_Info " Set mrcU = Executesql(txtsql,MsgText) While mrcU.EOF = False cboUser.AddItem Trim(mrcU.FIElds(0)) cboUsername.AddItem Trim(mrcU.FIElds(1)) mrcU.MoveNext Wend mrcU.Close End SubPrivate Sub sstab_Click(PrevIoUsTab As Integer) '点击退出按钮，卸载该窗体 Select Case sstab.Tab Case 4 Unload Me End SelectEnd Sub'写报表信息Private Sub ReportDay() Dim txtsql As String Dim MsgText As String Dim mrcCD As ADODB.Recordset '用于写 今日的 账单 Dim mrcSD As ADODB.Recordset '用于找出 上期 和 本期的充值卡余额 Dim mrcRC As ADODB.Recordset '用于找出 当日的 充值金额 Dim mrcL As ADODB.Recordset '用于找出 当日的 消费金额 Dim mrcCC As ADODB.Recordset '用于找出 当日的 退卡金额 Dim CardCash As Double '对应 本期消费金额 Dim RechargeCash As Double '对应 本日充值余额 Dim lineCash As Double '对应 本日消费金额 Dim cancelCash As Double '对应 本日退卡金额 txtsql = "select * from CheckDay_Info" '等待写入 Set mrcCD = Executesql(txtsql,MsgText) txtsql = "select sum(cash) from student_Info where status='使用'" Set mrcSD = Executesql(txtsql,MsgText) '存放 本期充值卡余额 txtsql = "select sum(addmoney) from ReCharge_Info where date='" & Date & "'" Set mrcRC = Executesql(txtsql,MsgText) '存放 本日充值金额 txtsql = "select sum(consume) from line_Info where offdate='" & Date & "'" Set mrcL = Executesql(txtsql,MsgText) '存放 本日消费金额 txtsql = "select sum(CancelCash) from CancelCard_Info where date='" & Date & "'" Set mrcCC = Executesql(txtsql,MsgText) '存放 本日退卡金额 '逐一判断 记录集合 为空的情况。如果查询结果为空 则赋值为0 If IsNull(mrcSD.FIElds(0).Value) Then CardCash = 0 Else CardCash = mrcSD.FIElds(0) End If If IsNull(mrcRC.FIElds(0).Value) Then RechargeCash = 0 Else RechargeCash = mrcRC.FIElds(0) End If If IsNull(mrcL.FIElds(0).Value) Then lineCash = 0 Else lineCash = mrcL.FIElds(0) End If If IsNull(mrcCC.FIElds(0).Value) Then cancelCash = 0 Else cancelCash = mrcCC.FIElds(0) End If mrcCD.AddNew mrcCD.FIElds(4) = Val(CardCash) + Val(lineCash) + Val(cancelCash) - Val(RechargeCash) mrcCD.FIElds(1) = Val(RechargeCash) mrcCD.FIElds(2) = Val(lineCash) mrcCD.FIElds(3) = Val(cancelCash) mrcCD.FIElds(0) = Val(CardCash) mrcCD.FIElds(5) = Date c0 = mrcCD.FIElds(0) c1 = mrcCD.FIElds(1) c2 = mrcCD.FIElds(2) c3 = mrcCD.FIElds(3) c4 = mrcCD.FIElds(4) c5 = mrcCD.FIElds(5) mrcCD.Update mrcCD.Close End SubPrivate Sub ReportSum() Dim txtsql As String Dim MsgText As String Dim mrcCD As ADODB.Recordset Dim mrcCS As ADODB.Recordset txtsql = "select * from CheckDay_Info where date='" & Date & "'" Set mrcCD = Executesql(txtsql,MsgText) txtsql = "select * from checkWeek_Info where date='" & Date & "'" Set mrcCS = Executesql(txtsql,MsgText) '如果 总报表中 没有数据，那么把日汇总表 的内容写入 mrcCS.AddNew mrcCS.FIElds(0) = mrcCD.FIElds(0) mrcCS.FIElds(1) = mrcCD.FIElds(1) mrcCS.FIElds(2) = mrcCD.FIElds(2) mrcCS.FIElds(3) = mrcCD.FIElds(3) mrcCS.FIElds(4) = mrcCD.FIElds(4) mrcCS.FIElds(5) = mrcCD.FIElds(5) mrcCS.Update '更新 mrcCS.Close '释放内存 mrcCD.Close End Sub总结
以上是内存溢出为你收集整理的VB查询数据库之结账——机房收费系统总结（五）全部内容，希望文章能够帮你解决VB查询数据库之结账——机房收费系统总结（五）所遇到的程序开发问题。
如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。
欢迎分享，转载请注明来源：内存溢出
微信扫一扫
支付宝扫一扫
评论列表（0条）