Insert & Select in VBA

[Shallo9]

Hello All,

I am trying to select a row from a table and insert it into the same table with a different primary key. I've tried hard enough but get a syntax error :banghead:. Below is the code I've been using. Any sort of help will be appreciated.

strSQL = "Insert into tbl_A (CustomerID, quoteTypeID, OccupationClassID, strOccupationTitle, curAnnualEarnings, dblDiscount, dtmEffDate, dtmCommencementDate, lstPaymentFrequency, strAppNumber, ProductTypeID, strDesiredOccupationTitle, DesiredOccupationClassID, DesiredTerminationAgeID ) values ( Select tblQuotes.CustomerID, tblQuotes.quoteTypeID, tblQuotes.OccupationClassID, tblQuotes.strOccupationTitle, tblQuotes.curAnnualEarnings, tblQuotes.dblDiscount, tblQuotes.dtmEffDate, tblQuotes.dtmCommencementDate, tblQuotes.lstPaymentFrequency, tblQuotes.strAppNumber, tblQuotes.ProductTypeID, tblQuotes.strDesiredOccupationTitle, tblQuotes.DesiredOccupationClassID, tblQuotes.DesiredTerminationAgeID from tblQuotes where tblQuotes.QuoteID = " & Me.sfrmQuotes!quoteID & ");"

CurrentDb.Execute strSQL

NB: QuoteID is the Primary Key

 

[pbaldy]

It's VALUES or SELECT, not both.

 

[Shallo9]

Syntax error in INSERT INTO statement. (Error 3134)

Insert into tbl_A( CustomerID, PolicyID, quoteTypeID, blnSmoker, OccupationClassID, strOccupationTitle, curAnnualEarnings, AgentID, RateID, dblDiscount, dblLoadingAmount, blnTopUp, dtmEffDate, dtmCommencementDate, lstPaymentFrequency, strAppNumber, ProductTypeID, TerminationAgeID, strDesiredOccupationTitle, DesiredOccupationClassID, DesiredTerminationAgeID ) values (3398,3402,1,False,1,'Mortgage Advisor',31000,2,,0,,False,##,##,,'',1,21,'',,);

Am I doin' anything wron

 

[mdlueck]

Working examples of SELECT / INSERT / UPDATE via ADO objects...

Example of SQL INSERT / UPDATE using ADODB.Command and ADODB.Parameters objects to Access tables
http://www.access-programmers.co.uk/forums/showthread.php?t=219149

Example of SQL SELECT using ADODB.Command and ADODB.Parameters objects to Access tables
http://www.access-programmers.co.uk/forums/showthread.php?t=230610#post1176746

 

[mdlueck]

Oh, are you trying to do something like this?

INSERT INTO tmptblqry_projects ( id, authid, authusername, logtimestamp, title, budget, rptactiveflg, rpttitle )
SELECT t.id, t.authid, t.authusername, t.logtimestamp, t.title, t.budget, t.rptactiveflg, t.rpttitle
FROM vbaclsObjProjectsTbl_RefreshLocalTmpTbl_PT AS t;

And that you would need to put in a DAO object rather than ADO object since its output goes into an Access table, not into VBA variables.

 

[pbaldy]

Syntax error in INSERT INTO statement. (Error 3134)

Insert into tbl_A( CustomerID, PolicyID, quoteTypeID, blnSmoker, OccupationClassID, strOccupationTitle, curAnnualEarnings, AgentID, RateID, dblDiscount, dblLoadingAmount, blnTopUp, dtmEffDate, dtmCommencementDate, lstPaymentFrequency, strAppNumber, ProductTypeID, TerminationAgeID, strDesiredOccupationTitle, DesiredOccupationClassID, DesiredTerminationAgeID ) values (3398,3402,1,False,1,'Mortgage Advisor',31000,2,,0,,False,##,##,,'',1,21,'',,);

Am I doin' anything wron

The values that are two single quotes are probably fine, but the ## entries will likely error as will the empty ones. You have to insert something, even if it's a zero length string (your '') or Null.

 

[Shallo9]

The values that are two single quotes are probably fine, but the ## entries will likely error as will the empty ones. You have to insert something, even if it's a zero length string (your '') or Null.

Thank you ever so much for the help. Out of curiosity I am wondering if there is any other way of accomplishing this with much simpler method.

Basically my query is I want to select a particular row from a table and append it to the same table. However, with a brand new primary key which usually is an auto number.

I still remember something from my college days where I've done my project in Oracle 7 and VB as the front end, I've used the code below.

 Insert into table EMP (Fname,Sname,Designation) Values (Select Fname,Sname,Designation from EMP where EmpID='1011')

 

[pbaldy]

You can use a SELECT clause instead of a VALUES clause, and leave the autonumber field out. It will get populated automatically.