Get sum on current subform

Question

I have a "Mainform" in Microsoft Access that automatically computes the summation of my "Subform". Now my trouble is this, in my "Subform" there is a Yes/No check box that corresponds on Amount_Lent, Date and Name. I want to get the total summation of the Amount_Lent in my current "Subform" when the value of my check box is "Yes".

Would it be possible to use the DSUM function using VBA?

you don't need VBA for this. just use the =Dsum(amount_lent,table_name, [yes/noFieldName] <> false) — Krish, Jun 29 '15 at 09:13

score 0 · Answer 1 · answered Jun 29 '15 at 08:43

You would be better of with a custom function:

Public Function AmountLentSum() As Currency

    Dim rs        As DAO.Recordset
    Dim AmountSum As Currency

    Set rs = Me!NameOfSubformControl.Form.RecordsetClone
    While Not rs.EOF
        If rs!YourCheckBox.Value = True Then
            AmountSum = AmountSum + Nz(rs!Amount_Lent.Value, 0)
        End If
        rs.MoveNext
    Wend
    rs.Close
    Set rs = Nothing

    AmountLentSum = AmountSum

End If

Then use as ControlSource: =AmountLenSum()

Get sum on current subform

1 Answers1