Solved Unable to update field values on form (despite having properties set appropriately?)

[jumnhy]

Hi folks,

Running into a maddeningly inconsistent issue with a form. It's a Single Form, recordsource is a basic select query that prompts for the ID of the record.

AllowInsertions, AllowEdits, AllowDeletions are all set to true. There are no record locks, the recordset type is "Dynaset" for both the form and underlying query.

If I toggle any non-consequential property, save the changes, and reload the form, I will be able to edit the record. If I then close the form, and reopen it again, I can't edit it period. This is true if I switch to datasheet view as well. When I can't edit the values of the fields, it's like the form isn't properly registering that I'm even attempting to edit the form--the status in the lower left that would flicker with "This form does not xxx" or something similar doesn't change from "Form View". However, the cursor does blink on the field, and I can highlight existing text within the fields, and can tab the cursor down the page.

Any ideas why this is happening?

Please let me know if there are further details I can provide for context--this is driving me utterly crazy.

 

[The_Doc_Man]

OK, let's see if I get this. On your form, you say you can change the form in some minuscule way, save it, and reload leading to ONE opportunity to edit the record, after which it "locks up" again. The form is a select query that prompts. How are you doing that prompting? Through the form or through the query?

My first place to look might be in the form's (or bound control's) BeforeUpdate or AfterUpdate events. What happens in those events (if you have them)?

Regarding the underlying query on that form, is it a query you entered directly to the .RecordSource or is it a named query that is in the collection of QueryDefs? Check the property sheet for the query to see if anything looks funky there.

Is there another similar form in the same DB that works correctly every time?

Then, of course, there is the possibility of corruption. Have you done a Compact & Repair on this DB lately?

I'm throwing out a few places to look for oddities. These are the things I might check first.

 

[jumnhy]

Thanks for the ideas, doc! I think you've got the picture.

Re the query prompt-- through the query itself. Just a simple [Enter ID:] under the criteria for the ID field. The query is a named query stored in the QueryDefs collection. From what I can see on the query's property sheet, it *should* be updateable, and if I run the query separately/directly, I can edit the values on the datasheet. This is also true if I open the form (when it's not updateable) and then separately open the query in a new tab. I also tried swapping in the direct SQL string versus a named query for the .RecordSource property, and get the same behavior.

Re events/code/macros:
There is one macro in the form's OnLoad event, which passes the value of a text box to a temporary variable, and then opens two additional forms using the value (the PK of the form) as the foreign key for each of these associated forms. I'm curious if that is somehow the culprit here, but I tried deleting the macro, which (similar to the "edit one miniscule property") buys me one more chance to edit the values, but then once closed and reloaded no longer allows editing.

There is another form that works flawlessly--different named query behind it, but same form structure, and pulls up a different set of the underlying record, which you use the record selector along the bottom to page through. With that form, I've never had an issue with not being able to update as needed.

I did start by doing a compact and repair earlier today, didn't seem to make a difference.

Thanks again for the ideas, it's exactly the sort of thing I'm looking for, even if it's not solved yet. Gonna experiment with having a procedure programmatically toggle some of the settings--if that reliably allows an edit, I'll hate myself for doing it, but it would at least get the job done...

 

[Gasman]

Easiest would be to load enough for people to try themselves?

 

[jumnhy]

As in, post a copy of my database for y'all to replicate the issue?

 

[jumnhy]

Slight update, maybe relevant: I stated the conditions that allowed for "one update" incorrectly before--if I change a property on the form in design view, and before saving, just click form view, I get to edit the form. If I save the form, close the tab, and open it again, still locked out. Weird as all getout.

 

[Gasman]

As in, post a copy of my database for y'all to replicate the issue?

Yes, just enough, with instructions on how to replicate. Change any sensitive data (see my first link, if you need to do that)

 

[jumnhy]

Another update: as mentioned above, I decided to just have a function toggle the AllowEdit property for all the controls on the form in the OnLoad event to true (they're all already set to true, I have a sneaking suspicion that I could toggle any property and this would work) and ... voilá. No idea why this is working here.

I'll try to post an anonymized copy for others to learn from tomorrow. Thanks for the brainstorming and assistance in any case!

 

[The_Doc_Man]

The only problem with this is that an OnLoad event for a main form only fires once, but if you edit the form design, save it, and then oprm it in form view - that counts exactly as though you are starting over. So what does the OnLoad event do in detail?

There is another question in my mind here. You've got ["Enter ID:"] for one field, but that value can't simultaneously be bound and asked. Data location has to be unique. So is this ID field trying to be part of what you store in a record? I'm confused about how you use it.

 

[jumnhy]

So, uh. I'm an idiot. The command button on the menu that opens this form uses a macro that sets the Mode to "Read Only". This overrides the form level properties. Never looked at the button before. What a facepalm.

 

[The_Doc_Man]

At least you found it. Don't know how much we helped, but you are off of ground zero now.

Good luck going forward with your project.

By the way, don't feel so bad. Every one of us has had more than one resounding face-palm. Hell, some of us are the V-8 poster children we do it so often. Welcome to our elite (?) club.

 

[jumnhy]

Thanks, Doc--having a sounding board of knowledgeable people here is sometimes all the help you need!

And I'm happy to be part of the bruised forehead club--if you can learn how to fix the stupid mistakes you get to graduate to the more complicated ones eventually...