insert into syntax

[KevB]

Could someone please assist.
I am trying to copy several records from a table called, PAYE, and create
identical records with the only different being that the value in the field
"current" changes from True to False. I have used the following but it
generates an error message.

strSQL = "INSERT INTO paye ( current, newdate, code, limit1, rate1, limit2,
rate2, limit3, rate3, limit4, rate4, limit5, rate5, sl, ratesl) " _
& "SELECT false as current, " _
& "FROM paye " _
& "WHERE Current=True"
db.Execute strSQL, dbFailOnError
MsgBox db.RecordsAffected & "records were added"

any help would be most appreciated [I have not used the INSERT Into before
so amd not sure of the syntax?

 

[Tom van Stiphout]

On Tue, 2 Sep 2008 20:37:01 -0700, KevB

Your SELECT clause only contains a single column. It should have as
many columns as referenced in the INSERT INTO clause:
insert into X(a,b,c,d)
select a,b,c,d
from Y

Excellent work on the dbFailOnError parameter. Too many developers
forget to do this.

-Tom.
Microsoft Access MVP

 

[Magius96]

strSQL = "INSERT INTO paye ( current, newdate, code, limit1, rate1, limit2,
After reading your SQL statement, it sounds to me like you need to "UPDATE"
the data already in the table, instead of "INSERT"ing new records. I may
have read it wrong, but I believe this is what your looking for

STRsql = "UPDATE paye SET paye.Current = False WHERE paye.Current = TRUE"
db.Execute strSQL, dbFailOnError
MsgBox db.RecordsAffected & " records were added."