MSAccess Insert with sub select wont work

Question

Hi all. I have simple query, but when I'm trying to execute this query I'm getting error: Query input must contain at least one table or query. (Error 3067)

Query: INSERT INTO FV_Ko ( FvId, OldPriceNetto ) SELECT [PFvId], (SELECT FV.PriceNetto1 FROM FV WHERE FV.FVnr = '123');

Thanks in advance

Answer 1

The subquery is OK, but you aren't saying where PFvID is coming from. Your INSERT should be something like this:

INSERT INTO FV_Ko ( FvId, OldPriceNetto ) 
SELECT AnotherTable.PFvId, 
(SELECT FV.PriceNetto1 FROM FV WHERE FV.FVnr = '123') FROM AnotherTable

Answer 2

...   
SELECT [PFvId], --> you should define a FROM table at end of the query
(SELECT FV.PriceNetto1 FROM FV WHERE FV.FVnr = '123');

Answer 3

This should work - it should ask you for [PFvId]

INSERT INTO FV_Ko ( FvId, OldPriceNetto ) 
SELECT [PFvId], FV.PriceNetto1 FROM FV WHERE FV.FVnr = '123';

Answer 4

If PFvld is a parameter and the query (MyQuery) is saved (part of the MSAccess database), then you should be able to do:

Dim qdf As DAO.QueryDef
Set qdf = db.QueryDefs("MyQuery")
qdf.Parameters("[PFvld]") = myValue

If you compose the query on the fly and execute it, then you might just specify a value when composing the SQL code instead of a parameter (not that it is a better solution though).