[dba-SQLServer]Convert VBA to SQL pointers (2)

Group,

AXP FE, SQL2000 BE.

Along similar lines to my other question on this subject.  This time I am 
calculating outstanding accounts.  This function is called from a loop.  I 
am not sure how I can convert it all into a single sproc that can calculate 
the new CurrentMth, OneMonth, curMthTwo, and ThreeMonths fields.


LOOP CALLING OF FUNCTION
     Set rstStatement = basRunDataObject("dbo.spMthEndCurrentStatements", 
adCmdStoredProc)
     Do Until rstStatement.EOF
         Call basUpdateAgedDebts(rstStatement!CustIDNo, 
rstStatement!StatementID)
         rstStatement.MoveNext
     Loop


Public Function basUpdateAgedDebts(lngCustID As Long, lngStatID As Long)

     On Error GoTo Err_basUpdateAgedDebts

     Dim intStatNo As Integer, curReceipts As Currency, curCurrent As Currency
     Dim curMthOne As Currency, curMthTwo As Currency, curMthThree As Currency
     Dim rst As ADODB.Recordset

     Call basInfo("Updating aged debtor fields." & vbCrLf & 
DLookup("MName", "dbo.tblCustomers", "CustomerID = " & lngCustID))

     'Get aged debts from previous statements
     intStatNo = DLookup("InvNumber", "dbo.tblCustomers", "CustomerID = " & 
lngCustID)
     curCurrent = Nz(basCustomDLookUp("CurrentMth", "dbo.tblCustStatement", 
"CustIDNo = " & lngCustID & " and StatementNumber = " & intStatNo - 1), 0)
     curMthOne = Nz(basCustomDLookUp("OneMonth", "dbo.tblCustStatement", 
"CustIDNo = " & lngCustID & " and StatementNumber = " & intStatNo - 1), 0)
     curMthTwo = Nz(basCustomDLookUp("TwoMonths", "dbo.tblCustStatement", 
"CustIDNo = " & lngCustID & " and StatementNumber = " & intStatNo - 1), 0)
     curMthThree = Nz(basCustomDLookUp("ThreeMonths", 
"dbo.tblCustStatement", "CustIDNo = " & lngCustID & " and StatementNumber = 
" & intStatNo - 1), 0)
     Set rst = basRunDataObject("dbo.vwMthEndStatReceipts", adCmdTable)
     rst.Find "LinkIDNo = " & lngStatID
     If Not (rst.EOF Or rst.EOF) Then 'Receipt record exist
         curReceipts = Nz(Abs(rst!Receipts), 0)
     Else
         curReceipts = 0
     End If
     'Deduct receipts from aged debts from oldest to current
     If curReceipts >= curMthThree Then
         curReceipts = curReceipts - curMthThree
         curMthThree = 0
     Else
         curMthThree = curMthThree - curReceipts
         curReceipts = 0
     End If
     If curReceipts >= curMthTwo Then
         curReceipts = curReceipts - curMthTwo
         curMthTwo = 0
     Else
         curMthTwo = curMthTwo - curReceipts
         curReceipts = 0
     End If
     If curReceipts >= curMthOne Then
         curReceipts = curReceipts - curMthOne
         curMthOne = 0
     Else
         curMthOne = curMthOne - curReceipts
         curReceipts = 0
     End If
     curCurrent = curCurrent - curReceipts
     'Age debts up by one month
     curMthThree = curMthThree + curMthTwo
     curMthTwo = curMthOne
     If curCurrent >= 0 Then
         curMthOne = curCurrent
         curCurrent = 0
     Else
         curMthOne = 0
     End If
     'If more paid than due then curCurrent will be in negative at this point

     Set rst = basRunDataObject("dbo.spMthEndStatCharges", adCmdStoredProc)
     rst.Find "LinkIDNo = " & lngStatID
     If Not (rst.EOF Or rst.EOF) Then
         curCurrent = curCurrent + Nz(rst!Charges, 0)
     End If

'Make faster(?) by opening a query with just the current customers being 
processed
     Set rst = basRunDataObject("dbo.tblCustStatement", adCmdTable)
         With rst
             .Find "StatementID = " & lngStatID, , adSearchForward
             If Not (.BOF Or .EOF) Then 'Statement record exists
                     !CurrentMth = curCurrent
                     !OneMonth = curMthOne
                     !TwoMonths = curMthTwo
                     !ThreeMonths = curMthThree
                     !StatementNote = DLookup("StatementNote", 
"dbo.tblCustomers", "CustomerID = " & lngCustID)
                 .Update
             End If
         End With
     rst.Close
     Set rst = Nothing



Regards

David Emerson
DALYN Software Ltd
25b Cunliffe St, Johnsonville
Wellington, New Zealand
Ph/Fax (877) 456-1205