question

CFOPIDC-5661 avatar image
0 Votes"
CFOPIDC-5661 asked gustav answered

VBA Access Insert SQL

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

office-vba-devoffice-access-dev
· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

my following code is giving error

And which error message do you get?
0 Votes 0 ·

Check if this works:

DoCmd.RunSQL "insert into rentinvoice ( RENTAGREEMENTID, DATEFROM, DATETO, AREA, RENTPERMONTH) values (rid, #" & Format(dd, "mm/dd/yyyy") & "#, #" & Format(dd1, "mm/dd/yyyy") & "#, " & area & ", " & rent & ") "

Or use more reliable techniques.


0 Votes 0 ·

this not working too!

error is
ENTER PARAMETER FOR rent

0 Votes 0 ·

Maybe this will works:

DoCmd.RunSQL "insert into rentinvoice ( RENTAGREEMENTID, DATEFROM, DATETO, AREA, RENTPERMONTH) values (" & rid & ", #" & Format(dd, "mm/dd/yyyy") & "#, #" & Format(dd1, "mm/dd/yyyy") & "#, " & area & ", " & rent & ") "

Or try some alternatives that use parameterised queries.


0 Votes 0 ·
OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered

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.
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

CFOPIDC-5661 avatar image
0 Votes"
CFOPIDC-5661 answered

this not working too!

error is
ENTER PARAMETER FOR rent

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

gustav avatar image
0 Votes"
gustav answered

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
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.