citychap26
Programmer
Hi,
Been out of practice of coding for a while, please can you tell me if there is a better way of doing this.
Should I be using a recordset clone ?
Cheers
SK
Been out of practice of coding for a while, please can you tell me if there is a better way of doing this.
Should I be using a recordset clone ?
Cheers
SK
Code:
Function flag_wrong_move()
On Error GoTo ERR_HANDLER
Dim db As Database
Dim rst As DAO.Recordset
Dim sSQL As String
Dim iCURR_AC_RK As Long
Dim iNEXT_AC_RK As Long
Dim iCURR_MONTH As Integer
Dim iNEXT_MONTH As Integer
Dim iCURR_BAL As Long
Dim iNEXT_BAL As Long
Dim iDIFF_BAL As Long
Dim iCOUNT As Double
sSQL = "select * from tbl_004_MORTGAGE_BALANCES ORDER BY ACCOUNT_RK, V_FROM_YEAR, V_FROM_MONTH"
iCOUNT = 0
Set db = CurrentDb()
Set rst = db.OpenRecordset(sSQL, dbOpenDynaset)
With rst
.MoveFirst
Do Until .EOF
iCURR_AC_RK = .Fields(0)
iCURR_MONTH = .Fields(1)
iCURR_BAL = .Fields(4)
'--- Get details of next record.
'--- If Account_RK is different then move on, else compare.
If Not .EOF Then
.MoveNext
iNEXT_AC_RK = .Fields(0)
iNEXT_MONTH = .Fields(1)
iNEXT_BAL = .Fields(4)
.MovePrevious
If iCURR_AC_RK = iNEXT_AC_RK Then
'Debug.Print iCURR_AC_RK & ", " & iNEXT_AC_RK
'--- This part of the lovely code will catch instances where there are missed months.
'--- Should write to a table really.
'If iCURR_MONTH + 1 <> iNEXT_MONTH And iCURR_MONTH <> 12 Then
'Debug.Print "**** PROBLEM MISSED MONTH"
'Debug.Print "CURRENT_MONTH: " & iCURR_MONTH
'Debug.Print "NEXT_MONTH: " & iNEXT_MONTH
'End If
'--- Update the difference column
iDIFF_BAL = iNEXT_BAL - iCURR_BAL
With rst
'--- we are comparing to previous month so need to move forward one.
.MoveNext
.Edit
.Fields("MONTH_CHANGE").Value = iDIFF_BAL
.Update
.MovePrevious
End With
End If
End If
.MoveNext
iDIFF_BAL = 0
iCOUNT = iCOUNT + 1
If iCOUNT Mod 1000 = 0 Then
DoEvents
Debug.Print iCOUNT & " " & Now()
End If
Loop
End With
NORMAL_EXIT:
Exit Function
ERR_HANDLER:
Select Case Err.Number
Case 3052
Resume Next
Case Else
MsgBox Err.Number & ", " & Err.Description
Debug.Print "WORKING ON : " & iCURR_AC_RK & ", " & iCURR_MONTH
Resume Next
End Select
End Function