Form no longer allows editing

[prn]

Hi all,

Among other things, I maintain an Access application that I have inherited from about a half-dozen previous maintainers. :(

I got a call from one of the users to the effect that one of the forms no longer allows editing of data. That is, you can click in the box, but can't change the data that appears in them. The cursor goes where you click, it sits there and blinks, but when you try to type or delete, nothing happens.

The data resides on a SQL Server with the Access piece being a front-end containing forms and queries with links to the tables. I pulled down the Linked-Table Manager and refreshed all the linked tables. That didn't help.

I checked the properties of the affected textboxes. they are Enabled and NOT Locked. Another idea that came up in my web search was that some queries are not updatable. It struck me that if something changed without my realizing it, the element that changed could as easily be the query as the form, so I checked that. I got an old version of the Access database (front-end) and checked the corresponding form on that. The fields were editable. I then copied the SQL from the record-source query for the table from both versions of the database and ran them through WinMerge (a diff utility), which reported that they are identical.

I also used Tools|Analyze|Documenter to create descriptions of the form from the old and new versions. I selected everything in the options and exported them both to .txt files and then compared them with WinMerge. Aside from one small wording change that I had made several months ago the only other differences were a number of places where some previous maintainer had changed the case of several variables from lower to upper. Otherwise nothing.

I've about run out of ideas. Do any of you folks have any clues for me? What else might cause this kind of behavior?

Thanks,
Paul

[NeoPa]

1. I would get the underlying query (or SQL whatever) and open it directly. Then test that it is still updatable.
2. Check through each property of the form and the relevant controls to see if anything unexpected has caused the issue.

That's all I can think of for now, as that (#1) also covers any potential problem updating data of the SQL Server from Access (which I have experienced recently with large updates and a SQL Server running short of resources).

Good luck!

[prn]

Hi NeoPa,

Thanks for your advice. I eventually just rolled back by one version, which was enough, and then redid the latest change. I still have no clue what went wrong with that form or why, and that bothers me, but after wasting hours trying to make sense of it, I figured that it was time to move on.

Thanks a lot,
Paul

[NeoPa]

Clearly a man of wisdom Paul :)

When to let go and try a different route is often a hard thing to see.

[missinglinq]

And you thought that your instructors were just being picky when they said "Back up, back up, back up!"

;0)>

[prn]

And don't forget the importance of change logs. I'd really be up the creek if I couldn't remember what change I'd last made.

Paul

[missinglinq]

Excellent point! I love it when an OP says "I made a few changes and now my form doesn't work." and when you ask "What changes?" they go, "Oh, I'm not really sure!"

You want to quote Bill Cosby: "Was your head not attached to your body that day?"

;0)>

[prn]

In this case, the change made between the (then) latest and penultimate versions had nothing to do with this form. It was off in an entirely different module that is not used in this form. The application in question contains a bit over 200 forms (including subforms) and 1250 queries (some, i don't know how many, are actually obsolete and unused).

Some of the "features" of the database are probably used no more often than once in two or three years. In fact, last Friday (today is Monday) I spent pretty much the entire day recovering data that was dropped by a macro. The macro was needed in the first place because some previous maintainer had denormalized data and then he or an intermediate maintainer had needed to change some of that denormalized data, so he (as far as I know all of them have been "he"s) made up a macro to remove the relevant data from a table, change it as well as the corresponding data in the table where it should have been in the first place, and then copy the changed data back into the table it had been removed from. The problem was that someone (possibly one of those two or possibly someone else) had added another, required, field to that table in the meantime and now the query that reinserted the data back into the table it had been deleted from failed. The temporary table then got dropped and the data was gone, causing me to spend hours trying to recover it as well as trying to figure out what happened to it. Needless to say, it took a lot longer to diagnose this comedy of errors than to describe it here. :(

Paul

[NeoPa]

Don't you just love it when you're passed a crock to look after.

"Here, just take this and make a small amendment to it to produce a report differently.

Oh, and by the way, it's an abortion that was created by the lobotomised offspring of a lower-level demon. Enjoy!"

[prn]

Oh, that isn't the half of it!

This one has truly made the rounds. Even just the "interesting" variation in naming conventions gives life such spice. Some examples (all query names):

• Site Enrollment report -- with spaces between words
• ServiceAgreementsPrinted -- using Pascal case
• Site_activation_query -- with underscores separating words
• Adm letter_UPDATE_query -- with one space and two underscores.
• qBookOrder -- with a 'q' prefix and Pascal case
• q_BookOrder_form -- with a 'q' prefix and mixed Pascal case and underscore
• qryEmailCount -- with a 'qry' prefix and Pascal case
• qry_Bookstore_Order_XLS -- with a 'qry' prefix and underscores
• QUpdateOrderQty -- here the 'q' prefix is capitalized
• QtEMPUPDATE -- Looks like the CapsLock was on and so cases reversed
• frmSiteBrowseDrop -- with a prefix that makes it look like a form rather than a query.

Talk about anti-mnemonic naming! It drives me nuts just trying to remember where to look for a query even when I remember the "content" part of its name.

Paul

[NeoPa]

I had one that suffered from similar symptoms. Less extreme than those if I'm honest. Doesn't really help.