Automatic data insertion - personal finance database

[Luthius]

I would like to have an automatic insertion on my financial transactions based on a check box.
What I mean - On my tblFinanceTransaction table I have a YES/NO field called FixedExpense. When I have a fixed expense I mark the check-box on userform. I would like a way to automatic generate the same expense for the next month when the expense is closed (paid) like a copy of the expense but with next month date.

Fixed expenses are internet services, lighting, insurance, etc.

Just summarizing the table fields below:

tblFinanceTransaction

• TrID (PK)
• TrDate (Date)
• Description (Text)
• CrValue (Currency)
• FixedExpense (Yes/No)
• TrStatus (Yes/No)

Luthius

 

[Luthius]

On a button of my userform I would like to pay all expenses based on a startDate and EndDate of my userform when I press the buttom.

strCriteria="like TrDate between #" & Format(Me.dtStart, "mm/dd/yyyy") & "# and #" & Format(Me.dtEnd, "mm/dd/yyyy") & "#;"

To pay based on criteria I can use the statement

strSQLUpdate="UPDATE tblFinanceTransaction SET tblFinanceTransaction.TrStatus = True WHERE " & strCriteria

But it pays all and don't generate for the next month a new fixed expense.

I was thinking on some algorithm that works this way.

#Open the record Set
#Identify what is fixed expense
#Clone the record adding future date using the function dateAdd
#Update the record
#Move Next
#

I don't know how to clone and use the record set.
I just scratch the SQL statement below - But it is not useful because it doesn't use proper criteria.

Dim strSQL As StringDim rs As Recordset
strSQL = "INSERT INTO tblFinanceTransaction ( TrDate, Description, CrValue, FixedExpense, TrStatus )" _
         & "SELECT DateAdd('m',1,[TrDate])as nextMonthDate, Description, CrValue, FixedExpense, TrStatus FROM tblFinanceTransaction;"

How can I create it?

 

[Luthius]

Solved using the algorithm.

Dim strSQL As String
Dim strCriteria As String
Dim rs As Recordset


strCriteria = "like TrDate between #" & Format(Me.dtStart, "mm/dd/yyyy") & "# and #" & Format(Me.dtEnd, "mm/dd/yyyy") & "#;"
rsSQL = "SELECT * FROM tblFinanceTransaction WHERE " & strCriteria


For i = 1 To rs.RecordCount
    strSQL = "INSERT INTO tblFinanceTransaction (TrDate, Description, CrValue, FixedExpense, TrStatus )" _
    & "SELECT DateAdd('m',1,[TrDate])as nextMonthDate, Description, CrValue, FixedExpense, TrStatus FROM tblFinanceTransaction;"
    
    If rs("FixedExpense") = True Then
        DoCmd.RunSQL (strSQL)
    Else
    End If
    rs.Edit
    rs("TrStatus").Value = True
    rs.Update
    rs.MoveNext
Next i