Subquery Inside Insert query statement

[Sitakanta1989]

Hi All,



I am using a subquery to take a value inside a insert query. But it is saying "[HY000:-3035: on SQLHANDLE] [Microsoft][ODBC Microsoft Access Driver] Operation must use an updateable query."


insert into SC_789
(User_ID,Date_Time,Store_ID,Period_txt,Role,Role_F unctionality,Comment_Category,Comment_Subcategory, Remarks)
values
( 'Administrator', getdate(), 'BE1001','2014S1','CFO',

Select ([Role_Functionality]) from Transaction_Dropdown where [Comment_Subcategory] = 'Close down',
select ( [Comment_Category]) from Transaction_Dropdown where [Comment_Subcategory] = 'Close down',

'Close down', 'No Comments' );


Please help me to identify the issue and provide a sloution if possible

Thanks,
Sitakant

[ranman256]

If you linked more than 1 table in the query , it could prevent the update. It depends on the arrangement.
or if the table link is not ddynaset
or the table

[NoellaG]

Hi,
looking at the getdate() function in the statement it looks to me you're using SQL server syntax. Could you specify a bit in what environment you are working? Access front-end on SQL server back-end?

[Sitakanta1989]

Hi,
looking at the getdate() function in the statement it looks to me you're using SQL server syntax. Could you specify a bit in what environment you are working? Access front-end on SQL server back-end?

Hi,

I am using Access front-end and getdate() is replaced by date(). But still the query is throwing error in the subquery part. Is it possble to pass a value from different table in a insert query ?

Thanks,
Sitakant

[NoellaG]

Hi,

yes but you have to use a dlookup function, something like

dlookup("[Role_Functionality]","Transaction_Dropdown", "[Comment_Subcategory] = 'Close down'")

keep in mind that using subqueries is always slower than using joined tables.