Operation Must Use an Updateable Query 1

[crystalreporting]

Hi,

I've developed this SQL statement and it runs fine in MS/SQL Query Analyzer;

Update OEORDHDR_SQL set OEORDHDR_SQL.tot_sls_amt = (Select SUM(Round(OEORDLIN_SQL.qty_to_ship * OEORDLIN_SQL.unit_price * ( 100 - OEORDLIN_SQL.discount_pct ) / 100,2 )) from OEORDLIN_SQL where OEORDHDR_SQL.ord_type = OEORDLIN_SQL.ord_type AND OEORDHDR_SQL.ord_no = OEORDLIN_SQL.ord_no AND OEORDHDR_SQL.selection_cd = 'S')

I'm now wanting this to run within an MSAccess application as part of a click event so I created this code;

'Recalculate the total sales amount based on the orders selected and the qty to ship
sql_statement = "Update dbEORDHDR_SQL set dbEORDHDR_SQL.tot_sls_amt =" & _
"(Select SUM(Round(dbEORDLIN_SQL.qty_to_ship * dbEORDLIN_SQL.unit_price * ( 100 - dbEORDLIN_SQL.discount_pct ) / 100,2 )) " & _
"from dbEORDLIN_SQL where dbEORDHDR_SQL.ord_type = dbEORDLIN_SQL.ord_type AND " & _
"dbEORDHDR_SQL.ord_no = dbEORDLIN_SQL.ord_no AND dbEORDHDR_SQL.selection_cd = 'S')"
DoCmd.RunSQL (sql_statement)

I get a message that tells me the operation must use an updateable query. So I tried to do this as two separate queries (a select followed by an update) but I still get the same message.

Any ideas?

Peter Shirley
Macola Consultant, PA and surrounding states.

http://www.srhconsulting.com

 

[fneily]

Check out:

http://msdn.microsoft.com/en-us/library/aa198446.aspx

Queries that do not contain primary keys are not updatable. Does yours include the PK?

 

[dhookom]

If the tables are all on SQL Server, you could try run this as a pass-through query.

Duane
Hook'D on Access
MS Access MVP

 

[crystalreporting]

Thanks dhookom - worked perfectly. A star for you.

Peter Shirley
Macola Consultant, PA and surrounding states.

http://www.srhconsulting.com