Self Documenting Code

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

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>