I completely agree with the Self Documenting Code posting.
Below is some sample code for summing up all the costs of a service order with a small number of comment lines. That's about right I'd say.
Now some may quibble with some things in that code. For example I could've created a subroutine and called it for each of the child tables for which I was getting a total. Sure, I could've but it's not that many more lines of code the way it is.
Another question might be why am I storing such a grand total in the first place? That's a violation of normalization. Correct, however to get that number on various reports would require a great deal of extra load on the system. I do have to ensure though that, among other things, whenever a child record is deleted that this routine gets called. Which I forgot to do and a client pointed out.
And what's that comment line "@@@ check srTotalCost"? The @@@ mean that's a comment I put in the code indicating I need to come back in an hour or two or three, once I've done other things, and double check or update the logic. In other words a reminder I can quickly go Ctrl+F to find.
Public Sub CalculateServiceTotal(ServiceID As Long, blnUpdateTotal As Boolean, blnUpdateForm As Boolean)
Dim ServiceAmount As Currency, RS As DAO.Recordset, strSql As String
Dim ExternalInvoicesAmount As Currency, FiltersAndFluidsAmount As Currency
Dim ServiceTechCosts As Currency
On Error GoTo CalculateServiceTotal_Error
ServiceAmount = 0
ExternalInvoicesAmount = 0
ServiceTechCosts = 0
' ==== Sum up invoices belonging to this service
strSql = "SELECT Sum(sriInvoiceAmount) AS SumInvoiceAmount " & _
"FROM ServiceRecordInvoices " & _
"WHERE sriServiceRecordID=" & ServiceID & ";"
Set RS = CurrentDb.OpenRecordset(strSql)
If RS.EOF = False Then
RS.MoveFirst
ExternalInvoicesAmount = Nz(RS!SumInvoiceAmount, 0)
End If
RS.Close
Set RS = Nothing
If blnUpdateForm Then _
Forms![Service Detail]!TotalInvoicesCost = ExternalInvoicesAmount
' === Sum up filters and fluids belonging to this service
strSql = "SELECT Sum(srsServiceExtendedAmount) AS SumOfServiceExtendedAmount " & _
"FROM ServiceRecordServices " & _
"WHERE srsServiceRecordID=" & ServiceID & ""
Set RS = CurrentDb.OpenRecordset(strSql)
If RS.EOF = False Then
RS.MoveFirst
FiltersAndFluidsAmount = Nz(RS!SumOfServiceExtendedAmount, 0)
End If
RS.Close
Set RS = Nothing
If blnUpdateForm Then _
Forms![Service Detail]!TotalFiltersAndFluidPrice = FiltersAndFluidsAmount
' === Sum up ServiceTechHours belonging to this service
strSql = "SELECT Sum([srtHours]*[srtRate]) AS SumOfTechCosts FROM ServiceRecordTechs " & _
"GROUP BY srtServiceID HAVING srtServiceID=" & ServiceID & ";"
Set RS = CurrentDb.OpenRecordset(strSql)
If RS.EOF = False Then
RS.MoveFirst
ServiceTechCosts = Nz(RS!SumOfTechCosts, 0)
End If
RS.Close
Set RS = Nothing
If blnUpdateForm Then _
Forms![Service Detail]!SumTechCosts = ServiceTechCosts
' Total Service Costs
ServiceAmount = ExternalInvoicesAmount + FiltersAndFluidsAmount + ServiceTechCosts
' @@@ check srTotalCost
' Update total cost
If blnUpdateTotal Then
If ServiceAmount <> 0 Then
strSql = "UPDATE ServiceRecords SET ServiceRecords.srTotalCost = " & ServiceAmount & " " & _
"WHERE srID=" & ServiceID
Else
strSql = "UPDATE ServiceRecords SET ServiceRecords.srTotalCost = Null " & _
"WHERE srID=" & ServiceID
End If
CurrentDb.Execute strSql, dbFailOnError
End If
On Error GoTo 0
Exit Sub
CalculateServiceTotal_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure CalculateServiceTotal of Module mdlService"
Exit Sub
Resume
End Sub