"The recordset is not updatable."

[bigdavidt]

I have a query that draws on four tables. Table One connects to Table Two with an Inner Join. Table Two connects to Table Three and also Table Four through Inner Joins. The query results are displayed in a form with a sub-form. All the records display correctly, but if I try to change any fields I get the message "The recordset is not updatable."

When I remove Table Four from the query I can put information in the boxes on the form, but the records do not display. At the bottom the form shows a count of one record, with all the fields blank. I do get a message that says "the Microsoft Access database engine cannot find a record in the table Table Two with key matching Table Three.CNCOrderID” if I try to close the form. (I have looked in both tables, and I can find matching entries in the respective fields.)

I have made sure that all the fields involved in the Joins are indexed.

So either I have a query with four tables that generates a record set I cannot update, or a query with three tables that allows me to make entries in the boxes on my form but does not display any existing records

Any ideas?

 

[dhookom]

I expect your issue is with your tables and has nothing to do with your form. If you view the datasheet of your record source you should be able to determine if the records can be updated/appended.

I rarely include more than two tables in a record source that I want to update. I will use subforms to display the "child" records for updating.

Can you provide your SQL view as well as the primary and foreign key fields?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[bigdavidt]

Hi Duane,

I was able to solve my problem this afternoon. Part of it was that Table Two and Table Four were linked on fields that were indexed but were not used as the primary key. I removed Table Four from the main query and accessed information from it in a different way. There was an apparently an additional issue also; I am not sure what it was, but by going back to an earlier version of my form and building on that, I was able to get things working.

Thank you for your feedback.