The recordset is not updatable

[MilaK]

A message pops up when I click on any fields of a continuous form "The recordset is not updatable". Please suggest how to diagnose this problem.

Thanks, Mila

 

[JHB]

I suppose your form is found to a query.
The link explain in which cases a recordset isn't updateable.
http://allenbrowne.com/ser-61.html

 

[MilaK]

This limits my options. Is there anything that can be done to allow edits of some of the fields on a form that is bound to such query? Perhaps, having two forms side by side one static and one updatable? Thanks

 

[gemma-the-husky]

can you redesign your form/query to make the recordset updateable? what circumstance IS making it non-updateable?

 

[MilaK]

I wish I could.... it's a crazy query, or actually six different queries joined together. Any other options?

I only need to be able to update four fields. I was thinking about adding two forms one would contain the four fields that need to be updated with updatable record source and another form would contain the rest of the fields with unadaptable row source. Both forms would be filtered with a combo in the header. What do you think about this possibility?

 

[MilaK]

The query is read-only because I use Group By clause.

 

[JHB]

I wish I could.... it's a crazy query, or actually six different queries joined together. Any other options?
...

I have only focused on a solution, and not if you've a bad table design!
A solution could be using a temporary table, created it when your open the form, (change your query to a "Make table" query), and bound your form to the table created. Write the changers to the relevant table(s), record(s) and field(s) when the form close, using update queries.

 

[MilaK]

@JHB,

This sounds like a great idea that is hopefully going to save me lots of time and tears!

I have a few questions if you don’t mind:

1. It looks like every time the “make a table” query is executed it overwrites the previous table, correct?
Is there a way to suppress all of the prompts and warnings?
Also, it would make sense to limit this table to a subset of data. One of the fields present in the “make a table”query is the project name “project_name”. Is it possible to set that parameter “project name” from another form, based on the user’s action (If they click on a particular project record)?

2. Also, since the new table is going to be the row source for the form and going to be deleted each time the query is executed, do I need to redefine the row source for the form or not?

3. You’ve mentioned updating the original table via update queries. The user will only be allowed to make changes to three fields, all form the same table “tbl_Variants”. Is "tbl_Variants" going to be updated from the form or the new table that is the row source for the form. Also, how will it know which records to update? Do I tell it to update all three fields for data that belongs to that project.

Thanks, Mila

 

[JHB]

..
1. It looks like every time the “make a table” query is executed it overwrites the previous table, correct?
Is there a way to suppress all of the prompts and warnings?
Also, it would make sense to limit this table to a subset of data. One of the fields present in the “make a table”query is the project name “project_name”. Is it possible to set that parameter “project name” from another form, based on the user’s action (If they click on a particular project record)?

The warnings could be turn of/on by "DoCmd.SetWarnings False/True", just remember to turn in on again.
If you don't want some fields in then leave them out in the query.

..
2. Also, since the new table is going to be the row source for the form and going to be deleted each time the query is executed, do I need to redefine the row source for the form or not?

If you mean Recordsource, then yes, set it to the table created as I mention in the previous post, ("and bound your form to the table created").

..
3. You’ve mentioned updating the original table via update queries. The user will only be allowed to make changes to three fields, all form the same table “tbl_Variants”. Is "tbl_Variants" going to be updated from the form or the new table that is the row source for the form. Also, how will it know which records to update? Do I tell it to update all three fields for data that belongs to that project.

You need to update the table(s) which contain the fields.

 

[MilaK]

It’s almost working!

I run vba code to make a new table each time user clicks on project. The code deletes the table and recreates it. This works great.

However, when I open the form that is bound to the table that is recreated with “make a table query”, the form is blank. After I use combobox on the same form to filter the form, data shows up. (To clarify the form is a sub-form, the is not linked to the main form).

I need to use the same scenario with a “make a table query” as a row source for a linked subform. Since I’m continuously deleting and recreating the table how can I ever link it to another table? How can I specify the relationship for that linked subform to the main form?
Thanks, Mila

 

[JHB]

Could you post your database with some sample data + how to reproduce the problem you've.