VBA Access Insert SQL

Question

my following code is giving error on insert statement when it comes to field rent and area
Private Sub Command4_Click()
Dim dd1 As Date
Dim dd As Date

Dim RID As Integer
Dim area As Currency

Dim rent As Currency

DoCmd.SetWarnings False

dd = Me.DATEFROM

Do While dd <= Me.DATETO

dd1 = DateSerial(Year(dd), Month(dd) + 1, 1) - 1
RID = Me.RID
area = Me.AREASQFT
rent = Me.mrent

DoCmd.RunSQL "insert into rentinvoice ( RENTAGREEMENTID, DATEFROM, DATETO, AREA, RENTPERMONTH) values (rid, #" & Format(dd, "mm/dd/yyyy") & "#, #" & Format(dd1, "mm/dd/yyyy") & "#, area, rent) "
'DoCmd.RunSQL ("insert into rentinvoice ( RENTAGREEMENTID, DATEFROM, DATETO, AREA, RENTPERMONTH) Values ( 'forms!form1!ID', #" & Format(dd, "mm/dd/yyyy") & "#, #" & Format(dd1, "mm/dd/yyyy") & "#), area, rent;")

dd = dd1 + 1
Loop

DoCmd.SetWarnings True

MsgBox ("Records Saved")

End Sub

Answer 1

ENTER PARAMETER FOR rent

Then I guess table "rentinvoice" has a column "rent", which is defined as mandatory; you have to include the column in your INSERT statement as well.

Answer 2

this not working too!

error is
ENTER PARAMETER FOR rent

Answer 3

Avoid the clumsy and longwinded SQL concatenating. Use the power of Access and DAO, which is cleaner and also way faster in a loop:

Private Sub Command4_Click()

    Dim Records As DAO.Recordset

    Dim dd1     As Date
    Dim dd      As Date

    dd = Me!DATEFROM.Value    

    Set Records = CurrentDb.OpenRecordset("Select * From rentinvoice", dbOpenDynaset, dbAppendOnly)
    Do While dd <= Me!DATETO.Value
        dd1 = DateSerial(Year(dd), Month(dd) + 1, 0)
        Records.AddNew
        Records!RENTAGREEMENTID.Value = Me!RID.Value  ' ?: Me!ID.Value
        Records!DATEFROM.Value = dd
        Records!DATETO.Value = dd1
        Records!AREA.Value = Me!AREASQFT.Value
        Records!RENTPERMONTH.Value = Me!MRent.Value
        Records.Update

        dd = DateAdd("d", 1, dd1)
    Loop
    Records.Close

    MsgBox "Records Saved"

End Sub