Insert Syntax error blindeness

[Incisor]

Insert Syntax error blindness

Any humbling advice is appreciated

error 3134
--------------
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim sql, sqlInsert As String
Dim lp As Integer
Dim recdate As Date


sql = "Select Descr,Plus,Minus,Notes from tblBill"

Set db = CurrentDb
Set rs = db.OpenRecordset(sql)

lp = 0
'loop through the records.
Do While Not rs.EOF
lp = lp + 1
recdate = DateAdd("s", lp, Now())

'Debug.Print recdate

sqlInsert = "Insert into tblPlan(Name,Plan,Plus,Minus,Notes) Values(" & rs![Descr] & ",#" & recdate & "#," & rs![Plus] & "," & rs![Minus] & "," & rs![Notes] & ")"
DoCmd.RunSQL sqlInsert

rs.MoveNext
Loop

rs.Update
lp = 0
---------------

 

[Pat Hartman]

Nothing jumps out at me. However, using Name as the name of a column causes problems since Name is the name of a property and VBA has trouble working out when you are refering to the name of something or a table column called Name. I would strongly suggest changing it. I would also not do this as a code loop. It is particularly inefficient because it has an embedded SQL string that will need parsing for each iteration of the loop. Use an append query instead. The column names don't have to be identical. You simply need to match them up. Access can't do it for you. You can also supply a value for the date field.