If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
can't change data in form even thought the query is updateable
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 |
#2
|
|||
|
|||
can't change data in form even thought the query is updateable
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 '================================================= === Paul wrote: 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 |
#3
|
|||
|
|||
can't change data in form even thought the query is updateable
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 "John Spencer" wrote in message ... 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 '================================================= === Paul wrote: 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 |
Thread Tools | |
Display Modes | |
|
|