Recordset not updatable after minor changes. Help!

[osx99]

I have a form with several subforms which are driven by a very large query with lots of calculations.

However, I need to reduce the length of the query as it crashes NT systems.

I have created an additional query which uses the calculations from the original large query to calculate additional info from the cost data table. I have then linked this to the original main query (now reduced in size). However, when I open the database I cannot change or add a new record. I get error 'Recordset is not updatable'

Can anyone point me in the right direction here. I don't want to lose any referential integrity on any tables either.

Let me know if I need to explain in more detail.

Many thanks,
Osx

 

[ajdesalvo]

If the second query has a one-to-many relationship, or has calculations, you cannot update the recordset. You may have to create a temporary table with the data you wish to join to your main recordset query in order to update the records. You could create the table(s) on the load of the form you will use to edit/add the records.

Hope this helps.

Anthony J. DeSalvo
President - ScottTech Software
"Integrating Technology with Business"

 

[RickSpr]

Anthony is partially mistaken about updates being blocked by a one-to-many relationship or calculations (although you can't update a calculated column, of course). Queries over one-to-many relationships can usually be updated. Totals queries (those that contain the GROUP BY clause) can't be updated, but other kinds of calculations are ok.

Look in the help file for information about what disqualifies queries and columns from being updated. For Access 97, it's in the topic "update queries, data that can be updated." In Access 2000 Help, on the Answer Wizard tab enter the term "updatable query" and then select the topic "When can I update data from a query?".

If your compound query contains many tables, it could be hard to figure out what is causing the disqualification. It's probably that you have a many-to-one-to-many combination of relationships. If you get stuck, perhaps you should show us the SQL of all the queries.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[osx99]

Not sure what was causing this to be not updatable as my query only had simple calculations without aggregates like SUM etc.

However, I have used ajdesalvo's method creating a temporary table which works just fine. Just not as elegant.

Thanks all,

osx