Record Locked after editing text box (1 Viewer)

[mhorner]

I've been struggling for over 1 year to resolve this bug being experienced on 2 of my 7 data input forms for one of my databases at work.

If you enter data into any text or combo box on the form, the record locks and a pencil appears in the corner. If you try to leave the form using the close form command button I've created, the user is prompted with an error saying "The RunCommand action was cancelled". As far as I can tell, this comes from the close form button's code:

DoCmd.RunCommand acCmdSaveRecord

When the pencil appears and the record is locked, the line of code in my close form button creates the run command error. I know this because if I put a ' before this line, the error goes away, but the record does not save.

The only way I've found to prevent this error, save the record, and close the form -- is to enter the data into the text box. Then press escape (which removes the pencil sign), then you can press the command button to close the form.

This "bug" (or maybe bad design on my part) has been plaguing 2 of my 7 forms - creating major confusion for some of my users. Any time a user edits a record, they have to first press escape to unlock the record, then they can press close form. The other 5 data input forms do not have this issue. You can change the data in a field no problem and just press the close form button (and the record saves).

Any ideas what is causing the record to be locked? Why can I press escape and the record becomes unlocked? Is this simulating a change in object focus? Is there a vba command I can send to mimick the escape key being pressed? I'm racking my brain here why this form locks until pressing escape, and why running a docmd save record command while locked would create a run command error.

 

[theDBguy]

Hi. I think the best way to tell you what's wrong is for us to examine the form. Are you able to share it?

 

[Micron]

DoCmd.RunCommand acCmdSaveRecord

You have this in the form close event? Then the form has already unloaded, thus has no data, thus I expect the command would have to be canceled by Access. That's a guess because I'd put form saving code (if needed) somewhere else. A record edit is canceled by esc, so if there' anything there, it gets removed first. You could try moving off of the control, moving off of the record, using Me.Dirty = false (but not in close event). The button click event should be OK - before calling the close form method.
Or you can attach your db as I think was suggested.

 

[MajP]

replace that line with
if me.dirty then me.dirty = false

 

[Pat Hartman]

Whenever you save a record (you are not saving a form) using ANY method, the event needs to have an error trap procedure because if there is a problem with the data, Access won't save the record but it will raise an error in the event that triggered the save.

It is not clear what you mean by "close form button's code". If this is a button control's click event, then saving is OK in that event. If it is the Close event of the form, Micron is correct.

 

[mhorner]

Thank you for the replies everybody. I put aside my work yesterday and dedicated myself fully to troubleshooting this further. I've found the resolution and figured I'd post the solution in case anybody else encounters this. Thank you all for the input and willingness to help. Here is what I've found:

I systematically began commenting out lines of code through the objects and form events until I discovered the following. On the form, I have a text box called txtLastModified. This text box is on numerous data input forms through my database. Any time a record is updated, the current date and time is pushed into this textbox and saved in the record using a simple line of VB.

The true culprit was not the do command to save the record, but rather the fact that I mistakenly put code to update the record into the AFTER update event, not the BEFORE update event.

Private Sub Form_AfterUpdate()

Me.txtLastModified.Value = Now()
Me.txtLastModified.Requery

End Sub

After removing these 2 lines, the error and bug completely were resolved. I moved these 2 lines of code to the before update event and now everything works perfectly. I suspect Access was getting trapped in a sort of loop where it was trying to update the record's LastModified value after the update occurred. It appears to me that you can't push record updates in the after update event. Sounds silly, but was easy form me to mistakenly overlook.

The same fix worked on both forms experiencing this problem. So it appears to be fully resolved.

 

[theDBguy]

Hi. Thanks for the update. Glad to hear you got it sorted out. I think it's fine to update data in either Before or After Update event. It's just maybe there was a conflict in this case. So, possibly, it's better to avoid using both at the same time. Good luck with your project.

 

[mhorner]

Thanks for clarifying, DBGuy. I always appreciate your support.

 

[Pat Hartman]

I think it's fine to update data in either Before or After Update event

Absolutely not if you are referring to FORM level events. In fact, you can't update a control in the control's BeforeUpdate event but you can in its AfterUpdate event. The opposite is true in the FORM level events. You do your validation in The FORM's BeforeUpdate event and so any updating that you need to do with code. NO UPDATES should ever be done in the FORM's AfterUpdate event since that just sends the form into an endless loop. If you dirty a record in the AfterUpdate event, the BeforeUpdate event must run prior to saving the record and then after the save, the AfterUpdate event runs where you dirty the record again causing an endless loop. Earlier versions of Access would simply freeze if you did this. Current versions are smarter and after some levels of looping, Access works out that you've created an endless loop and breaks out of it gracefully.