Creating a Running Balance Query

[arnelgp]

Just a personal preference.

yes, so i edited by post.

 

[KitaYama]

yes, so i edited by post.

With a large number of records in the table, your code faces a performance problem because it opens the recordset (RS.RecordCount - 1) times.

I would change as following to prevent opening recordset over and over:

Public RS As DAO.Recordset

Public Function fnBalance(ByVal ID As Long) As Double
    Dim result As Double
   
    If RS Is Nothing Then
        Set RS = CurrentDb.OpenRecordset("SELECT * FROM tblChecking ORDER BY TransDate, ID;", dbOpenSnapshot, dbReadOnly)
    End If
    With RS
        ......      ---> Do calculations
    End With
    fnBalance = result
End Function

And to allow the update of the table being set to the recordset, I would add this too:

Private Sub Form_Open(Cancel As Integer)
    Set RS = Nothing
End Sub

Do you have a better solution to stop opening the same recordset over and over?
thanks.

 

[arnelgp]

your code wiill do or you can make it static:

Public Function fnBalance(ByVal ID As Long) As Double
    ' ID can be:
    '   -1          release the recordset
    '   0           requery the recordset
    '   any number  the actual id number
    Static rs As DAO.Recordset  'rs is preserved when the db is open and will be closed automatically when you the db
    Dim result As Double
    If ID = -1 Then
        If Not (rs Is Nothing) Then
            rs.Close
            Set rs = Nothing
            Exit Function
        End If
    End If
    If rs Is Nothing Then
        Set rs = CurrentDb.OpenRecordset("SELECT * FROM tblChecking ORDER BY TransDate, ID;", dbOpenSnapshot, dbReadOnly)
    End If
    If ID = 0 Then
        rs.Requery
        Exit Function
    End If
    With rs
        .FindFirst "ID = " & ID
        Do Until .BOF
            result = result + Nz(!Credit, 0) - Nz(!Debit, 0)
            .MovePrevious
        Loop
    End With
    fnBalance = result
End Function

on opening of the form you can either pass -1 (release the recordset) or 0 (requery the recordset), before opening the query
so you get Fresh records from the recordset.

 

[AngelSpeaks]

May I hijack this discussion for my question? I have more than one checking account. When the first account is displayed, then it's displayed correctly. I have a main form, frmBankAccounts, bound to qryBankAccounts which displays all back accounts. The subform, subfrmCheckRegister is bound to qryCheckRegister and when I click NEXT button on frmBankAccounts, the check transactions for that bank is displayed. The second and remaining accounts Balance field starts with the final balance of the previous account.

The control NewBalance on subfrmCheckRegister is set to =Val(DSum("Nz(Debit,0)-Nz(Credit,0)","[qryCheckRegister]","ID<=" & [ID]))

Any suggestions on how I can reset this field to zero? I tried on the Form Load, Form Current and Form Open events to set Me.NewBalance to zero but I got the message "You can't assign a value to that object".

Thanks

 

[Gasman]

Use the Account in the criteria?

 

[AngelSpeaks]

Use the Account in the criteria?

The NEXT button on the main form?

 

[Gasman]

No you include the account in the dsum criteria. That should start at zero for the very first record for that account, if you also check for account.

 

[AngelSpeaks]

No you include the account in the dsum criteria. That should start at zero for the very first record for that account, if you also check for account.

Ok thanks

 

[AngelSpeaks]

OK, I'm stuck on stupid here. This is the code behind the subform field (I did try several different variations):

=Val(DSum("Nz(Debit,0)-Nz(Credit,0)","qryCheckRegister","[Account] = '" & [Forms]![frmBankAccounts]![Account] & "'" And "ID<=" & [ID]))

To recap, frmBankAccounts is the main form and Account is the textbox that has the Account name (text field). The first [Account] reference is the textbox on the subform, subfrmCheckRegister. The NewBalance field is the same total on every line (actually it's the final balance of all the records)

 

[DHookom]

=Val(DSum("Nz(Debit,0)-Nz(Credit,0)","qryCheckRegister","[Account] = '" & _
[Forms]![frmBankAccounts]![Account] & "' And ID <= " & [ID]))

 

[arnelgp]

=Val(DSum("Nz(Debit,0)-Nz(Credit,0)","qryCheckRegister","[Account] = '" & _
[Forms]![frmBankAccounts]![Account] & "' And ID <= " & [ID]))

already mentioned this won't work if there are "Late" entries on the transactions.

 

[DHookom]

I was just fixing the expression. Late entries just need to be understood by the OP.

 

[Gasman]

I would have hoped that Account would be an autonumber

 

[AngelSpeaks]

=Val(DSum("Nz(Debit,0)-Nz(Credit,0)","qryCheckRegister","[Account] = '" & _
[Forms]![frmBankAccounts]![Account] & "' And ID <= " & [ID]))

Thanks but I did try it without the quotes before ID (at least I think I did)

 

[AngelSpeaks]

I would have hoped that Account would be an autonumber

Imported by QuickBooks that way. I would have to convert it to use an autonumber

 

[Gasman]

Imported by QuickBooks that way. I would have to convert it to use an autonumber

Fair enough.

 

[Gasman]

Thanks but isn't that the same?

No.

Yours then Duanes

=Val(DSum("Nz(Debit,0)-Nz(Credit,0)","qryCheckRegister","[Account] = '" & [Forms]![frmBankAccounts]![Account] & "'" And "ID & =" &  [ID]))

=Val(DSum("Nz(Debit,0)-Nz(Credit,0)","qryCheckRegister","[Account] = '" & [Forms]![frmBankAccounts]![Account] & "' And ID <= " & [ID]))

 

[AngelSpeaks]

No.

Yours then Duanes

=Val(DSum("Nz(Debit,0)-Nz(Credit,0)","qryCheckRegister","[Account] = '" & [Forms]![frmBankAccounts]![Account] & "'" And "ID & =" &  [ID]))

=Val(DSum("Nz(Debit,0)-Nz(Credit,0)","qryCheckRegister","[Account] = '" & [Forms]![frmBankAccounts]![Account] & "' And ID <= " & [ID]))

I get #Error on this.

 

[GPGeorge]

I get #Error on this.

Did you get the same error using Duane's version?

 

[AngelSpeaks]

Did you get the same error using Duane's version?

I used Duane's version. My version gave the same amount on each record