Solved

MSACCESS error: Operation must use an updateable query

Posted on 2009-04-08
2
2,720 Views
Last Modified: 2012-05-06
I am getting the error "Operation must use an updateable query."

I understand why I am getting the error but this is what I need to do.

1. I have an ODBC link to a table which holds the specific gravity (measure of weight to volume) of materials and manufactured products
2. Some materials are manufactured items (intermediates) so the SG is calculated based on the mix of all the materials within the manufactured product and then stored in the same table
3. A finished good can be comprised a mixture of purchased and manufactured products

When you update the SG of a purchased material or change the formula to make the product the SG's of intermediates and finished products are not automatically updated so I decided to write an "Update" query to maintain them .... but alas because I need to update the same table I need to calculate the results from I am not allowed.

How do I overcome this?


This is the 4 part query I have written that doesn't work in the update but gives the correct result in the 'select' view:

Query: 330-01 Formulakey Kilos and Litres
SELECT FMMAST.Formkey, Sum(FMITEM.wtorunitqty) AS Kilos, Sum([wtorunitqty]/[specgrav]) AS Litres
FROM (FMITEM INNER JOIN FMMAST ON FMITEM.Formkey = FMMAST.Formkey) INNER JOIN INMSPEC ON FMITEM.Itemkey = INMSPEC.Itemkey
WHERE (((FMITEM.Linetyp)="I"))
GROUP BY FMMAST.Formkey;



Query: 330-02 Formulakey Specific Gravity
SELECT [330-01 Formulakey Kilos and Litres].Formkey, [330-01 Formulakey Kilos and Litres].Kilos, [330-01 Formulakey Kilos and Litres].Litres, [kilos]/[litres] AS SpecificGravity
FROM [330-01 Formulakey Kilos and Litres];



Query: 330-03 Itemkey Specific Gravity
SELECT [330-02 Formulakey Specific Gravity].Formkey, INMAST.Itemkey, INMAST.Itemdescription1, [330-02 Formulakey Specific Gravity].SpecificGravity AS CalculatedSpecificGravity, INFSPEC.Itemstat, [specgrav]/[calculatedspecificgravity] AS SGRatio
FROM INMSPEC INNER JOIN ((INMAST INNER JOIN INFSPEC ON INMAST.Itemkey = INFSPEC.Itemkey) INNER JOIN [330-02 Formulakey Specific Gravity] ON INFSPEC.Formkey = [330-02 Formulakey Specific Gravity].Formkey) ON INMSPEC.Itemkey = INMAST.Itemkey
WHERE (((INMAST.Itemkey)="AAK0106") AND ((INMAST.Itemtype)="F"))
ORDER BY [330-02 Formulakey Specific Gravity].Formkey, INMAST.Itemkey;



Query: 330-04 Correct Specific Gravity
UPDATE INMSPEC INNER JOIN [330-03 Itemkey Specific Gravity] ON INMSPEC.Itemkey = [330-03 Itemkey Specific Gravity].Itemkey SET INMSPEC.Specgrav = [330-03 Itemkey Specific Gravity]![CalculatedSpecificGravity]
WHERE ((([330-03 Itemkey Specific Gravity].SGRatio)<>1));
0
Comment
Question by:Zarbs
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 2

Accepted Solution

by:
pskeens earned 500 total points
ID: 24104152
Create a TEMP Table and append your results to this table, then update the records in your MainTable with the records in the TEMP table, after this is complete, delete the TEMP Table.

0
 

Author Closing Comment

by:Zarbs
ID: 31568313
Such an easy solution .... thanks
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

729 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question