FredixBlog

It is sometimes needed to show the running total of the records. On a report, there is no problem as this feature in built-in. But on a form, a bit of coding is needed.
Here is a function that will work on any form. The most of it is that you can sort or filter the records, the running total still works because it works with the form’s recordsetclone.
Parameters of the function:
[Amountfield] is the field name we want to totalize
[Keyfield] is the field name of the primary key of your form’s table or query, or simply an unique field.

Public Function RunTotal(AmountField, KeyField)
Dim SumRs As Recordset
Dim frm As Form
Dim RunningValue As Double
Dim IsCancelled As Boolean

Set frm = Forms(AmountField.Parent.Name)
Set SumRs = frm.RecordsetClone

With SumRs
    ' Setting first amount
    RunningValue = 0
    On Error Resume Next
    .FindFirst "True"
   
    ' Summing all other
    While .Fields(KeyField.Name) <> frm.Controls(KeyField.Name) And Not IsCancelled
        RunningValue = RunningValue + .Fields(AmountField.Name)
        On Error Resume Next
        .FindNext "True"
        If Err <> 0 Then IsCancelled = True
        On Error GoTo 0
    Wend
       
    ' Do the current one
    RunningValue = RunningValue + .Fields(AmountField.Name)
End With

Set SumRs = Nothing
RunTotal = RunningValue
End Function

• AddReference()
• How can I e-mail a file from Microsoft Access?
• Create a linked table with VBA
• Dirty Flag for unbound form
• Custom Autonumber Access 2000
• Create ODBC DSN

The dirty flag doesn’t work when the form is unbound. Here the technique is to use the validation rule like an event in order to set the flag. Easy: A simple function returning the control’s text itself can do the job!
I bother for the performance so my code also checks for the existence of a former validation rule. Setting properties of many controls could make the form open slower without this check.
Once the form is first opened with this method, then saved, there is no need of re-doing the function so the form opens faster because for sure testing for the existence of SetDirty validation rule will make an exit.

Private Sub Form_Open(Cancel As Integer)
Dim ctr As Control
Dim frm As Form
Set frm = Me
For Each ctr In frm
    On Error Resume Next
    if ctr.validatonrule="SetDirty()" then ' Already done, let's not smash an open door!
        exit sub
    On Error Goto 0
    if isempty(ctr.Validationrule) then
        ctr.ValidationRule = "SetDirty()"
    end if
Next ctr
End Sub

And, of course don’t forget the SetDirty function:

Public Function SetDirty()
    IsDirty = True
    SetDirty = Me.ActiveControl.Text
End Function

The full EE thread can be read here:

http://www.experts-exchange.com/Databases/MS_Access/Q_11879478.html.html

• Site Map
• Running total on a form
• Replicate a database