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.