Updateable form, not updateable query?

[IqbalS]

Someone explain this one! The query and therfore form by design has until now been non updatable and its been working great. Somehow the form has become updatable even tho the properties have not changed, so allowedits etc is all set to no.

I have noticed now that the openform command was set to editable, but if the query is still not updatable how is the form?

I do import some tables from time to time and I noticed the indexes where not applied, I put them on and strangly this form appears to be editable!

Confused? I am!

 

[Banana]

Are you sure that the query itself is updateable? You can verify this by opening the query directly (without the form) in the QBE then switching to datasheet view and try to insert/update random rows.

Generally, whenever Access is unable to track each query's row back to its source from the base table, the query is nonupdateable, and thus will not work, even if the form is designed to be updateable.

Allen lists a few examples that makes a query nonupdateable.

 

[IqbalS]

Thats the catch, the query is still not-updateable and so the form shouldn't be either. Opening the query I cannot change the values as expected. However opening the form based on the query you can edit the values furthermore these values are actually saved in the table?!

 

[Banana]

Okay, I didn't read you right the first time. Sorry.

Did you verify that the query itself is nonupdateable by opening query builder and inserting random data in it?

If it is indeed nonupdateable, then check whether form *actually* uses that query or maybe another query with similar name, or had recordsource changed in VBA code.

Did that help?

 

[Banana]

One more possibility and I apologize in advance if this sounds dumb, but I wanted to make sure I've covered all bases-

is the query itself actually a recordsource of the form or merely a rowsource of a control?

 

[IqbalS]

The query cannot be changed. The form's recordsource is the query and I haven't been playing with the recordsource anywhere else! I have a backup which is as it should be - the only relevant thing i've changed is the indices on the tables its referring to.

I've managed to lock the form down again by setting the recordset type to snapshot - but it still shouldn't become editable in the first place!

 

[Banana]

Aha! That's the culprit!

From that link I gave you:

The fields in a JOIN are not indexed correctly: there is no primary key or unique index on the JOINed fields.

I'd bet that when you added the index, Access now could resolve each row back to their original base tables and thus could update it!

 

[IqbalS]

oops missed the link, although the orginal query itself is still not updatable... And there it is the query was snapshot too and so would have always not been updatable. And updating the indices did indeed enable access to work it out - and made it a bit faster too!

Bananaman thanks for all your help