can't change data in form even thought the query is updateable

  • Thread starter Thread starter Paul
  • Start date Start date
P

Paul

I have a simple continuous form that's been working well for over a year,
but will no longer permit the user to change data in any of the fields.
When I open the underlying query in datasheet view, I can change data in
those fields. But when the form is open, I can no longer change the data in
any of the fields.

I'm quite certain that this problem has something to do with the underlying
query rather than the form for two reasons: first, I checked the form and
control properties as shown below and there is nothing that would prevent
the fields from being edited in the form. Second, since this form has been
working well for the past year up until today, I opened a backup copy from
two months ago, and I could not change the data in the form in that version
either.

Form Properties:
Allow Edits Yes
Allow Deletions No
Allow Additions No
Data Entry No
Recordset Type Dynaset
Record Locks No Locks

Individual field properties:
Enabled Yes
Locked No
There are also no validation rules in any of the controls.

So again, the problem must be in the underlying query. But why would I be
able to change data directly in the query datasheet view, but then be
prohibited from changing data in a form based on the same query?

Thanks in advance,

Paul
 
Form corruption is a possibility.

Another possibility is that you are not using the query you think you
are (been there, done that)

Another possibility is that you have code in the form that is changing
the form's record source and the new record source is not the same as
the one listed (been there, done that too)

Here is some advice from Allen Browne on recovering from corruption.
From: Allen Browne
Okay, so something has gone haywire with this database. Suggestions to
recover it:

1. Make a backup copy of the mdb file, without overwriting any existing
backups, in case something goes wrong.

2. Uncheck the boxes under:
Tools | Options | General | Name AutoCorrect Explanation of why:
http://allenbrowne.com/bug-03.html

3. Compact the database to get rid of this junk:
Tools | Database Utilities | Compact

4. If the tables are attached, open the data file, and repeat steps 1 -
3 for that file as well.

5. Close Access. Decompile the database by entering something like this
at the command prompt while Access is not running. It is all one line,
and include the quotes:
"c:\Program Files\Microsoft office\office\msaccess.exe" /decompile
"c:\MyPath\MyDatabase.mdb"

6. Open Access, and compact again.

7. Open a code window.
Choose References from the Tools menu.
Uncheck any references you do not need.
For a list of the ones you typically need in your version of Access, see:
http://allenbrowne.com/ser-38.html

8. Still in the code window, choose Compile from the Debug menu.
Fix any errors, and repeat until it compiles okay.

9. Compact again.

At this point, you should have a database where the name-autocorrect
errors are gone, the indexes are repaired, inconsistencies between the
text- and compiled-versions of the code are fixed, and reference
ambiguities are resolved.

If it is still a problem, the next step would be to get Access to
rebuild the database for you. Follow the steps for the first symptom in
this article:
Recovering from Corruption
at:
http://allenbrowne.com/ser-47.html
'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
Thanks so much for providing this information from Allen Browne about how to
recover from corruption, John. I'm going to run both my front end and back
end through that process to clean them up.

While I was checking the things you mentioned like running code that resets
the forms recordsource (I too have done those things before), I just
happened to notice something amiss, and also found that was causing the
problem. What I stumbled on was that the primary key in the main table of
the underlying query was no longer a primary key. The field and data were
still there, and the records were unique, but it was no longer defined as a
primary key in the table in the back end database. I'm trying to imagine
how it happed, and the only thing I can imagine is that at one point I made
a backup copy of the table while I experimented with something, and I might
have somehow interchanged the backup and original tables which could have
confused the Relationships. (I did also notice that the Relationships
window was disorganized beyond recognition).

So I'm going to run both the front and back ends through this decompile
steps in your message in the hopes of eliminating other problems that might
be developing.

Again, thanks for the info.

Paul
 
Back
Top