最后一次验收也是因为这个报表没有弄好一直拖着,不是师傅严格,而是我自己太不认真了,从心里面不想弄这两个报表。最开始写报表代码的时候是看的师哥师姐们的博客,但错误一致没有调试好,自己也不知道是哪里出了问题。后来终于忍不住好好研究一下。看来别人的东西终究是别人的,成不了自己的,只有自己真正的懂了明白了融会贯通了才能成为自己的东西。
实现后日结账单的报表:
Private Sub Form_Load()
Dim txtSQL As String
Dim msgText As String
Dim mrc As ADODB.Recordset
Dim mrc1 AsADODB.Recordset
Dim RemainCash As Single
Dim RechargeCash As Single
Dim ConsumeCash As Single
Dim CancelCash As Single
Dim AllCash As Single
Dim i As Integer
'先检查日结账单中是否有当日的记录,若有,则显示在账单,没有则进行添加
txtSQL = "select * fromcheckday_Info where date='" & Format(Date, "yyyy-mm-dd")& "'"
Set mrc1 = ExecuteSQL(txtSQL,msgText)
If mrc1.EOF = False Then
RemainCash =mrc1!RemainCash
RechargeCash =mrc1!RechargeCash
ConsumeCash =mrc1!ConsumeCash
CancelCash =mrc1!CancelCash
AllCash = mrc1!AllCash
Else
'没有则自己结算今日账单
txtSQL = "select * fromcheckday_Info"
Set mrc = ExecuteSQL(txtSQL,msgText)
Do While mrc.EOF =False
mrc.Delete
mrc.MoveNext
Loop
'1.从checkday表中获取 最新的余额,要求表中有记录,获得上次结账后的上期余额
txtSQL = "select * from checkday_info"
Set mrc = ExecuteSQL(txtSQL, msgText)
RemainCash = 0
DoWhile Not mrc.EOF
RemainCash = RemainCash+ mrc!AllCash
mrc.MoveNext
Loop
'2.从recharge表中获取今天的充值总和
txtSQL = "select * fromrecharge_info where date='" & Format(Date, "yyyy/mm/dd")& "'"
Set mrc = ExecuteSQL(txtSQL,msgText)
RechargeCash = 0
Do While Notmrc.EOF
RechargeCash =RechargeCash + mrc!addmoney
mrc.MoveNext
Loop
'3.从line表中获取今天的学生消费总额
txtSQL = "select * fromline_info where offdate='" & Format(Date, "yyyy-mm-dd")& "'"
Set mrc = ExecuteSQL(txtSQL,msgText)
ConsumeCash = 0
Do While Not mrc.EOF
ConsumeCash =ConsumeCash + mrc!consume
mrc.MoveNext
Loop
'4.从cancelcard表中获取今天的退款总额
txtSQL = "select * fromcancelcard_info where date='" & Format(Date, "yyyy-mm-dd")& "'"
Set mrc = ExecuteSQL(txtSQL,msgText)
CancelCash = 0
Do While Notmrc.EOF
CancelCash = CancelCash+ mrc!CancelCash
mrc.MoveNext
Loop
'5.计算出今天的余额,将以上得到的数据添加到checkday表中
AllCash = RemainCash +RechargeCash + ConsumeCash - CancelCash
End If
每次查询表中记录之情,应先把上次结账的记录删掉.
txtSQL = "select * from checkday_info where date='" & Format(Date, "yyyy-mm-dd") & "'"
Set mrc = ExecuteSQL(txtSQL, msgText)
'应将checkday表中的记录删除
Do While Not mrc.EOF
mrc.Delete
mrc.MoveNext
Loop
On Error Resume Next '3021
mrc.AddNew
mrc.Fields(0) = RemainCash
mrc.Fields(1) = RechargeCash
mrc.Fields(2) = ConsumeCash
mrc.Fields(3) = CancelCash
mrc.Fields(4) = AllCash
mrc.Fields(5) = Date
mrc.Update
mrc.Close
每次日结账之后都要把这一天的情况记录到周结账表中,以便查询周结账时调用
'6.向checkweek表中添加今天的日汇总情况
txtSQL = "select * from checkweek_info "
Set mrc = ExecuteSQL(txtSQL, msgText)
On Error Resume Next
mrc.AddNew
mrc.Fields(0) = RemainCash
mrc.Fields(1) = RechargeCash
mrc.Fields(2) = ConsumeCash
mrc.Fields(3) = CancelCash
mrc.Fields(4) = AllCash
mrc.Fields(5) = Date
mrc.Update
mrc.Close
这是报表和VB连接的重要语句
Set Report = New grproLibCtl.GridppReport '实例化模板
Report.LoadFromFile (App.Path & "\日报表.grf") '加载模板
Report.DetailGrid.Recordset.ConnectionString = ConnectString() '连接数据源
txtSQL = "select * from checkday_info" ' where date='" & Format(Date, "yyyy-mm-dd") & "'"
Report.DetailGrid.Recordset.QuerySQL = txtSQL '通过查询创建记录集
GRDisplayViewer1.Report = Report
GRDisplayViewer1.Start '开始打印
End Sub
接下来的周报表和日报表和很相似的,只是查询的日期不是一天,而是一个日期间隔,改一下查询语句就可以了。
一直错误的认为这个系统早就敲完了,但是验收时才发现错误真是不少。
其实一个系统代码的实现不是一个完整的系统,代码的调试和选择最好的方法才是一个好系统真正的开始。这两个报表让我懂得了没有自己做不到的事,只有自己不想做的事。任何自己畏惧的东西都是来自心里的抵触。其实没有什么可怕的,自己去做了就好!