Invoices: How to properly round and calculate totals

1. Don’t pass around unrounded values

2. Once you round, use the rounded value for all further totals

MS Access can’t do a running total/sum of calculated fields in a form like you can in DataEase. Instead, you have to use an AfterUpdate on the form/subform to call a function that adds up each line, rounding at each line:-

Private Sub Form_AfterUpdate()

Me.Parent.Form!curSubTotal = SumTotal(Me.RecordSource)
Me.Parent.Form!curVAT = SumTotalVAT(Me.RecordSource)
Me.Parent.Form!curTotal = Me.Parent.Form!curSubTotal + Me.Parent.Form!curVAT
Me.Parent.Form!curSettDiscAmt = SumDiscAm(Me.RecordSource)

End Sub

The functions:-

Public Function SumTotal(Me_Recordsource)
Set rs1 = CurrentDb.OpenRecordset(Me_Recordsource)
Do While Not rs1.EOF
SumTotal = SumTotal + Round(rs1!intQtyReqd * rs1!curPrice * (1 – (rs1!intLine / 100)), 2)
rs1.MoveNext
Loop
End Function

 

Public Function SumTotalVAT(Me_Recordsource)
Set rs1 = CurrentDb.OpenRecordset(Me_Recordsource)
Do While Not rs1.EOF
SumTotalVAT = SumTotalVAT + Round(rs1!intQtyReqd * rs1!curPrice * (1 – (rs1!intLine / 100)) * (1 – (rs1!intSett / 100)) * (rs1!intVAT / 100), 2)
rs1.MoveNext
Loop
End Function

 

Public Function SumDiscAm(Me_Recordsource)
Set rs1 = CurrentDb.OpenRecordset(Me_Recordsource)
Do While Not rs1.EOF
SumDiscAm = SumDiscAm + Round(rs1!intQtyReqd * rs1!curPrice * (1 – (rs1!intLine / 100)) * (rs1!intSett / 100), 2)
rs1.MoveNext
Loop
End Function