SQL - copying records

[Nathan]

I am trying to copy records from one table into another
using a SQL command. Two fields in the destination table
need to be filled. The "SerialNo" field will be the same
for all records copied and comes from a textbox on an open
form ("Main"). The PListID field comes from
the "TemplateList" table. The user will select a Template
on another open form and I want to copy the PListID's
which correspond to the chosen template into the "Parts"
table.

Here is the SQL command I came up with:

INSERT INTO Parts (SerialNo, PListID) VALUES ([Forms]!
[Main]![Cranes1].[Form]![Serial #], SELECT PListID FROM
TemplateList WHERE TemplateID=[Forms]![Choose Template]!
[combo13])

Only I get a syntax error on the SELECT statement. I
can't find anything wrong with my syntax, but I'm not
really sure about my logic. Is there a conflict because
the SELECT statement may return multiple records?

How can I accomplish this?

Thanks!
Nathan

 

[Dirk Goldgar]

I am trying to copy records from one table into another
using a SQL command. Two fields in the destination table
need to be filled. The "SerialNo" field will be the same
for all records copied and comes from a textbox on an open
form ("Main"). The PListID field comes from
the "TemplateList" table. The user will select a Template
on another open form and I want to copy the PListID's
which correspond to the chosen template into the "Parts"
table.

Here is the SQL command I came up with:

INSERT INTO Parts (SerialNo, PListID) VALUES ([Forms]!
[Main]![Cranes1].[Form]![Serial #], SELECT PListID FROM
TemplateList WHERE TemplateID=[Forms]![Choose Template]!
[combo13])

Only I get a syntax error on the SELECT statement. I
can't find anything wrong with my syntax, but I'm not
really sure about my logic. Is there a conflict because
the SELECT statement may return multiple records?

How can I accomplish this?

You don't use the VALUES clause for this sort of append query. Try
something like this:

INSERT INTO Parts (SerialNo, PListID)
SELECT
[Forms]![Main]![Cranes1]![Serial #] As SerialNo,
PListID
FROM TemplateList
WHERE TemplateID=[Forms]![Choose Template]![combo13];