Help! Lost ability to EDIT

[Jack G]

I have suddenly lost the ability to edit the data in my database. I'm not
sure when it happened, but recent changes include:
* Split the database into a front-end and back-end
* Changed format to 2002/2003 from 2000
* Created an .mde version of the front-end (but I have the same problem
whether using the .mde or the .mdb front-end).

All of my forms have the "Allow Edits" property set to true (as well as all
of the other Allow...'s). I've checked all of my code for instances of
"AllowEdit.False" (had one and deleted it). My form works through a query
that is in the front-end. If I open a back-end table from the database
window, I CAN edit the table.

Can anyone help?

Jack

 

[J. Goddard]

Sounds like the query behind the form might not be editable. Have you
tried opening the query outside the form (i.e. through the database
window) to see if you can edit it?

John

 

[Jack G]

John,

Indeed, I can't edit it; and when I tried the same thing on an older version
of the database I could edit it.

But I can't find where you control the ability to edit queries (??). I
looked at the query's properties, but the only properties that looked like
possibilities were "Run Permissions" (set to "User's") and "Record Locks"
(set to "No Locks"); and both of these properties were set the same in both
versions of the database.

Do you know how I might "unlock" that query??

Jack

 

[J. Goddard]

Hi -

Making queries editable or not is not something you can control with a
setting somwhere. It has to to with the type of joins that are in them,
not something I know much about.

Having said that, in A2000 which I use, I was able to magically "unlock"
*some* queries I was sure should be updatable. What I did was to look
at the one-to-many relationships in the query (which are typically
lookup types of thing), and make sure the field on the "many" side was
the primary key of it's table.

It worked like a charm - but why it worked is (for me), one of MS
Access' innermost secrets. Maybe one of our MVP's can give us an
explanation?

Hope this helps if it applies.

John

 

[Jack G]

John,

Thanks, again. There did some to be some join problems. First, I had an
errant duplicate table in my query (Main and Main1) that shouldn't have been
there, so I deleted it. Then I noticed that one of the relationships did not
have "cascade update related fields" checked as it had been in older
version.

But unfortunately, I still can't edit. I think I'll look further into the
join relationships and see if I can find something else.

I appreciate your help.

Jack

 

[Jack G]

The problem MUST have been with the joins, but I guess I'll never know
exactly what. I just re-did the query and joins from scratch and this time
it worked.

Access sure is finicky and not much help in pinpointing errors!

Jack

 

[Jack G]

Me again.

Well, once more, I've lost the ability to edit (both in the form and in the
query it's based on. I think it might have occured after I changed one of
the links in the query, but changing it back to the way it was doesn't help.

Can anyone help me out here?

Jack

 

[Rick Brandt]

Me again.

Well, once more, I've lost the ability to edit (both in the form and
in the query it's based on. I think it might have occured after I
changed one of the links in the query, but changing it back to the
way it was doesn't help.

Can anyone help me out here?

The editability of queries can be complex to figure out. In many database
systems a query with more than one table is NEVER editable. Access is one of
the most flexible and forgiving in this area, but there are still limits and
rules. Basically any time it becomes ambiguous which table an update should be
applied to the database will (in the interest of protecting your data),
dis-allow edits.

It is seldom a good idea to use multi-table queries for data entry. They are
better suited to analysis and reporting. For data entry, forms with sub-forms
(each bound to ONE table) is a much more reliable way to go.

There is a help topic entitled "When can I update data in a query" which shoudl
give you some of the guidlelines and rules involved.

 

[Jack G]

Thanks, Rick. That should help me.

Jack

The editability of queries can be complex to figure out. In many database
systems a query with more than one table is NEVER editable. Access is one of
the most flexible and forgiving in this area, but there are still limits and
rules. Basically any time it becomes ambiguous which table an update should be
applied to the database will (in the interest of protecting your data),
dis-allow edits.

It is seldom a good idea to use multi-table queries for data entry. They are
better suited to analysis and reporting. For data entry, forms with sub-forms
(each bound to ONE table) is a much more reliable way to go.

There is a help topic entitled "When can I update data in a query" which shoudl
give you some of the guidlelines and rules involved.