Chapter 7 Exercise 27

27. Write a procedure to add an interest charge to customer accounts with a balance due. Make sure to handle concurrency/locking problems.

Forms: frmAddInterestCharges. But, make a copy of Customer and CustomerTransactions before running the code if you want to keep the original data.

Private Sub cmdApply_Click()
'On Error GoTo Err_cmdApply_Click

    Dim cnn As ADODB.Connection
    Set cnn = CurrentProject.Connection
    Dim rst As ADODB.Recordset
    Dim sSQL As String
    Set rst = CreateObject("ADODB.Recordset")
    sSQL = "SELECT CustomerID, BalanceDue FROM Customer WHERE BalanceDue>1"      ' Need to ignore small round-off values
    Dim cmd As ADODB.Command
    Set cmd = CreateObject("ADODB.Command")
    cmd.ActiveConnection = cnn
    cmd.CommandType = adCmdText
    rst.Open sSQL, cnn, adOpenForwardOnly, adLockOptimistic
    Dim bal As Currency, interest As Currency
    Dim CID As Long
    Do Until rst.EOF
        bal = rst("BalanceDue")
        interest = Round(bal * txtRate, 2)
        cnn.BeginTrans
        rst("BalanceDue") = bal + interest
        CID = rst("CustomerID")
        rst.Update
        cmd.CommandText = "INSERT INTO CustomerTransaction (CustomerID, TransactionDate, Amount, Description)" _
            & " VALUES (" & CID _
            & ", #" & Now() & "#" _
            & ", " & interest _
            & ", 'Interest Added')"
        cmd.Execute
        cnn.CommitTrans
        rst.MoveNext
    Loop
    rst.Close
    Set rst = Nothing
    Set cmd = Nothing
    
Exit_cmdApply_Click:
    Exit Sub

Err_cmdApply_Click:
    MsgBox Err.Description
    cnn.RollbackTrans
    Resume Exit_cmdApply_Click
    
End Sub