Option Compare Database
Option Explicit
Private Sub Button5_Click()
Dim prevMonth As Integer
Dim curMonth As Integer
Dim prevYear As Integer
Dim curYear As Integer
Dim CurRecordMonth As Integer
Dim rst As Recordset
Dim rst2 As Recordset
Dim db As Database
Dim monthText As Variant
Set db = CurrentDb
'fill an array with the text for months names
monthText = Array("", "Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec")
' find previous and current Year and month. If current month = jan then go back to Dec of the year before
curMonth = Month(Date)
curYear = Year(Date)
prevYear = Year(Date)
prevMonth = Month(Date) - 1
If prevMonth = 0 Then
prevMonth = 12
prevYear = prevYear - 1
End If
' count number of existing records for current year and month
' If DCount("Month", "TTransactions", "month = '" & monthText(curMonth) & "' and year = " & curYear) = 0 Then
If DCount("Month", "tTransactions", "month = " & curMonth & " and year = " & curYear) < 2 Then
'if zero current month and year does not exist in table
' open table and find last months record
Set rst = db.OpenRecordset("tTransactions", dbOpenDynaset)
rst.FindFirst "month = " & prevMonth & " and year = " & prevYear
' open table again to write a new record
Set rst2 = db.OpenRecordset("tTransactions", dbOpenDynaset)
Do Until rst.NoMatch ' loop through all records meeting the criteria
rst2.AddNew
rst2![TelNo] = rst![TelNo]
rst2!Year = curYear
rst2!Month = curMonth
rst2!Rental = rst!Rental
rst2![fees] = rst![fees]
rst2![Vat] = rst![Vat]
rst2.Update
rst.FindNext "month = " & prevMonth & " and year = " & prevYear
Loop
rst.Close
rst2.Close
Set rst = Nothing
Set rst2 = Nothing
End If
Set db = Nothing
End Sub