Update Query Problem. Copy from one Table to Another

[Stormrider83]

Hi,

I simply want to copy the field Qty from Qry_Paint_Order_Parts to the field Qty in Tbl_Paint_Order_Parts where the records are joined by Part_No in both tables

I use the following code:

strSQL = ""
strSQL = strSQL & " UPDATE Tbl_Paint_Order_Parts "
strSQL = strSQL & " SET Qty = [Qry_Paint_Order_Parts].[Qty]"
CurrentDb.Execute strSQL

but get the error - too few parameters expected one.

Can anyone help?

 

[KenHigg]

strSQL = strSQL & " SET Qty = " & [Qry_Paint_Order_Parts].[Qty]


??

 

[Stormrider83]

strSQL = strSQL & " SET Qty = " & [Qry_Paint_Order_Parts].[Qty]


??

Any ideas what it should be?

 

[KenHigg]

That was the alternate version of the third line in your code:

strSQL = ""
strSQL = strSQL & " UPDATE Tbl_Paint_Order_Parts "
strSQL = strSQL & " SET Qty = " & [Qry_Paint_Order_Parts].[Qty]
CurrentDb.Execute strSQL

 

[Stormrider83]

Ah sorry Ken didnt notice you had changed it!

The new line of code gives me the error:

Run-time error '2465':

Microsoft Access can't find the field '|' reffered to in your expression.

 

[Stormrider83]

This is my current code

strSQL = ""
strSQL = strSQL & " UPDATE Tbl_Paint_Order_Parts "
strSQL = strSQL & " SET Qty = " & [Qry_Paint_Order_Parts].[Qty]
CurrentDb.Execute strSQL

 

[Stormrider83]

I have been advised to use the following code:

strSQL = ""
strSQL = strSQL & " UPDATE Tbl_Paint_Order_Parts "
strSQL = strSQL & " SET Qty = ( SELECT Qry_Sum_Paint_Order_Parts.Qty FROM Qry_Sum_Paint_Order_Parts WHERE Tbl_Paint_Order_Parts.Part = Qry_Sum_Paint_Order_Parts.Part_No )"
strSQL = strSQL & " WHERE EXISTS ( SELECT 1 FROM Qry_Paint_Order_Parts WHERE Tbl_Paint_Order_Parts.Part = Qry_Paint_Order_Parts.Part_No ) "
CurrentDb.Execute strSQL

But I still get the error too few parameters. Expected One