azjz
asked on
URGENT - Could not update currently locked error issue
Hi Experts:
I have a database I developed using Access 2003. The data resides on a network server, and the front end contains forms, queries, etc. resides as an mde file on the users’ pc’s. There are about 15 users that can be using the application at a given time. There is no mdw file with the application.
There application has 4 forms. One form is a log-on form. When the user logs in successfully, there is a main menu to select which form to launch. The form which is experiencing an issue is a form that uses a main form linked to a subform to input data into the database. The data input into the main form updates Table 1 and the data input into the subform updates Table 2.
Users are sometimes experiencing an issue and getting a message stating, “Could not update, currently locked” when trying move from the main form to the subform. I have code to save any main form edits in the main form's lost focus event, as follows:
DoCmd.RunCommand acCmdSaveRecord
I also have code in the main form's after update event to save any edits made to data on the main form, as follows:
DoCmd.RunCommand acCmdSaveRecord
Me.Refresh
Forms!Frm_CaseNoteMain!Cbo
Can any expert please tell me if this code may be what's causing the issue the users are experiencing? This code is in the form to help automatically save edits in case they don't hit the save button on the form. The users have been told to "save often", and that seems to be helping. Any ideas would be appreciated because I am pretty lost and frustrated with trying to solve this problem because I've tried and still not resolution. Thanks experts.
I have a database I developed using Access 2003. The data resides on a network server, and the front end contains forms, queries, etc. resides as an mde file on the users’ pc’s. There are about 15 users that can be using the application at a given time. There is no mdw file with the application.
There application has 4 forms. One form is a log-on form. When the user logs in successfully, there is a main menu to select which form to launch. The form which is experiencing an issue is a form that uses a main form linked to a subform to input data into the database. The data input into the main form updates Table 1 and the data input into the subform updates Table 2.
Users are sometimes experiencing an issue and getting a message stating, “Could not update, currently locked” when trying move from the main form to the subform. I have code to save any main form edits in the main form's lost focus event, as follows:
DoCmd.RunCommand acCmdSaveRecord
I also have code in the main form's after update event to save any edits made to data on the main form, as follows:
DoCmd.RunCommand acCmdSaveRecord
Me.Refresh
Forms!Frm_CaseNoteMain!Cbo
Can any expert please tell me if this code may be what's causing the issue the users are experiencing? This code is in the form to help automatically save edits in case they don't hit the save button on the form. The users have been told to "save often", and that seems to be helping. Any ideas would be appreciated because I am pretty lost and frustrated with trying to solve this problem because I've tried and still not resolution. Thanks experts.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for the response, puppydogbuddy. Can you please tell me if you think I'd also need to put a Me.Refresh after the save command code? I'm wondering if part of the problem with the way the code is currently is that on the lost focus event of the main form the only line of code is the save command. Of course, if the lost focus event isn't even being executed, then it doesn't really matter what code is in the lost focus event of the main form.
Thanks for the response Flyster. I neglected to say the main form is set to No Locks for the Record Locking Property. My original post had a typo. Sorry.
Thanks for the response Flyster. I neglected to say the main form is set to No Locks for the Record Locking Property. My original post had a typo. Sorry.
Aziz,
The lost focus event is being executed, but too late (the subform control (which is bound to table 2 already has focus), and that is what I believe is causing your problem. That is why you need to execute your save code in an earlier event, most likely, the event prior to the on-enter event for the subform control. AS to refresh, it should be ok if the save were executed in the proper event.
To get a better idea of the order your events are firing, you might take a look at the tab key index. Place form in design view, then from the command menu select View> tab order. Try to identify which control has the focus prior to the subform control, and try putting your save code in the exit event of that control.
The lost focus event is being executed, but too late (the subform control (which is bound to table 2 already has focus), and that is what I believe is causing your problem. That is why you need to execute your save code in an earlier event, most likely, the event prior to the on-enter event for the subform control. AS to refresh, it should be ok if the save were executed in the proper event.
To get a better idea of the order your events are firing, you might take a look at the tab key index. Place form in design view, then from the command menu select View> tab order. Try to identify which control has the focus prior to the subform control, and try putting your save code in the exit event of that control.
ASKER
I've done some further analysis on this. When you enter data on the main form and then focus goes to the subform, it seems that Access is automatically saving the main form data to the database right after the main form before update event is executed. Then the main form after update event is executed - this is the event that contains the save command. So the order is: 1) main form before update event is executed; 2) Access automatically saves the main form data to the database; 3) the main form after update event is executed (which contains the save command).
So my question now is if it is possible Access is trying to save the record twice and that's where the conflict arises? I'm not sure because I have code to show a message when the main form after update event executes (starts and ends), but when I look at the record when Access automatically saves it and when the save code in the main form after update event is executed, the time stamp does not change.
Any thoughts on this very thoroughly confusing situation?
So my question now is if it is possible Access is trying to save the record twice and that's where the conflict arises? I'm not sure because I have code to show a message when the main form after update event executes (starts and ends), but when I look at the record when Access automatically saves it and when the save code in the main form after update event is executed, the time stamp does not change.
Any thoughts on this very thoroughly confusing situation?
It is confusing. The before and afterupdates you are referring to are related to controls on the form, not the form itself. The after update event for a form occurs only if there are no enabled controls on the form. Therefore the After update event could occur for each control being updated. ....and the first form could be dirty* at the time you open a second form. Go to Access Help and type in Order of Events for more info. In the meantime, I am going to research on how to display via code the Orderthat events are firing as you move through your form in the normal situation.
* The form could dirtied from changes to controls via background processes as well as from entry into a control.
* The form could dirtied from changes to controls via background processes as well as from entry into a control.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Aziz,
Probably the easiest way to determine the order that events are firing in your form, is to place the form in design view, select events, then click on some of the form events like activate, deactivate, got focus, lost focus. Then in that event procedure, place a message box that will display the name of the form and the event proc when it is fired. For example for the MainForm lost focus event, type in: MsgBox me.name & " lost focus."
After you have your message boxes in place, then test your form under normal situations of use. and jot down the order the message boxes pop up.
As for event procs on controls, I would place message boxes in the on-enter event of the subform control and the exit event of the control prior to the subform control.
Probably the easiest way to determine the order that events are firing in your form, is to place the form in design view, select events, then click on some of the form events like activate, deactivate, got focus, lost focus. Then in that event procedure, place a message box that will display the name of the form and the event proc when it is fired. For example for the MainForm lost focus event, type in: MsgBox me.name & " lost focus."
After you have your message boxes in place, then test your form under normal situations of use. and jot down the order the message boxes pop up.
As for event procs on controls, I would place message boxes in the on-enter event of the subform control and the exit event of the control prior to the subform control.
Aziz,
Here is some info, from EE expert Harfang, that I found in another post that may or may not be relevant to your situation:
Pop-up forms never get "deactivated" and never "lose focus" because they exist "outside" of the normal application windows stack (check the "window" menu). For example, you can drag them outside of the application window, they get minimized on the bottom of your sceen, etc. Even the application's toolbar buttons don't work on them.
In addition, when switching between a normal form and a popup, the normal form does not lose or get the focus...Modal forms lose the focus only when they are closed or hidden.
Here is some info, from EE expert Harfang, that I found in another post that may or may not be relevant to your situation:
Pop-up forms never get "deactivated" and never "lose focus" because they exist "outside" of the normal application windows stack (check the "window" menu). For example, you can drag them outside of the application window, they get minimized on the bottom of your sceen, etc. Even the application's toolbar buttons don't work on them.
In addition, when switching between a normal form and a popup, the normal form does not lose or get the focus...Modal forms lose the focus only when they are closed or hidden.
ASKER
Thanks so much for all the ideas. They are really appreciated.
I looked at the object dependencies within the database, but can see nothing there that would cause an issue (at least to my eyes, anyway).
I have put message boxes in the code in the main and subform so I can try to determine what events are firing when. Events the message boxes were placed in include activate, deactivate, got focus, lost focus, current, query, before update, after update, and current to name some of them. I cannot see anything that would cause the issue the users are experiencing.
Can I get some feedback about removing the subform function from the form and placing it in its own form? The new form would still be a main/subform scenario, but data entry could only occur on the subform itself. The main form would perform look up only.
Thanks again to all.
I looked at the object dependencies within the database, but can see nothing there that would cause an issue (at least to my eyes, anyway).
I have put message boxes in the code in the main and subform so I can try to determine what events are firing when. Events the message boxes were placed in include activate, deactivate, got focus, lost focus, current, query, before update, after update, and current to name some of them. I cannot see anything that would cause the issue the users are experiencing.
Can I get some feedback about removing the subform function from the form and placing it in its own form? The new form would still be a main/subform scenario, but data entry could only occur on the subform itself. The main form would perform look up only.
Thanks again to all.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for the info puggydogbuddy. Just some more info. The error message the users are getting is, "Could not update; currently locked." The can update data on the main form with no problem. The main form never locks. Its only when the user tries to set focus on the subform (by clicking on the subform or tabbing to the subform) that they encounter the error message and the issue.
That's why I thought separating out the two functions (the data entry function of the main form on one form by itself and the data entry function the current subform is being used for into two separate forms) may be the way to go.
The new second form would be a main form/subform design. However, the main form data would be read only and not updateable. The subform data would be updateable so the user can enter data or update dat
That's why I thought separating out the two functions (the data entry function of the main form on one form by itself and the data entry function the current subform is being used for into two separate forms) may be the way to go.
The new second form would be a main form/subform design. However, the main form data would be read only and not updateable. The subform data would be updateable so the user can enter data or update dat
Aziz,
You can try it, however before doing that .... something I just thought of.....
1. try entering data into the tables directly without the form and subform......if you still get the lock conflict .....it is not because of your form and subform, but some other reason.
2. Another tip I found is that if you have any boolean fields in your table, they can not be empty(null). If you got lock conflict in #1, check if boolean fields empty then test again.
3. Also, when working with boolean in access VBA and VB. The default for VB boolean is 0 and -1. In SQL the bit is 0 and +1. Tinyint is 0 to 255. So smallint is the smallest data type that really handles the boolean correctly.
4.if you are connecting to Access via ODBC/ADO, make sure cursor not set for pessimistic locking.
will get back to you.
You can try it, however before doing that .... something I just thought of.....
1. try entering data into the tables directly without the form and subform......if you still get the lock conflict .....it is not because of your form and subform, but some other reason.
2. Another tip I found is that if you have any boolean fields in your table, they can not be empty(null). If you got lock conflict in #1, check if boolean fields empty then test again.
3. Also, when working with boolean in access VBA and VB. The default for VB boolean is 0 and -1. In SQL the bit is 0 and +1. Tinyint is 0 to 255. So smallint is the smallest data type that really handles the boolean correctly.
4.if you are connecting to Access via ODBC/ADO, make sure cursor not set for pessimistic locking.
will get back to you.
to pull apart main form/subform:
1. copy and paste subform under new name
2. copy mainform(with old subform control)
3. paste under new name (subform control collapses but still exists on new main form)
4. delete subform control from new main form
5. new forms are separate and ready to be worked on
1. copy and paste subform under new name
2. copy mainform(with old subform control)
3. paste under new name (subform control collapses but still exists on new main form)
4. delete subform control from new main form
5. new forms are separate and ready to be worked on
ASKER
Thanks for ideas puppydogbuddy. A couple of more things. The database does not use a workgroup file. This would not cause an issue would it? Also, the data database which resides on the server has settings on the Tool, Options, Advanced tab of shared, no locks, and checked "Open using record level locking". Could any of these settings cause a conflict? I assume checking "Open using record level locking" means record level locking would be used rather than page level locking.
Oooh, so much frustration....
Oooh, so much frustration....
You have to have a system.mdw. It is the default security file that is created when you install access for the first time. If you did not have it, you would be geting an error message to that effect and you are not gettting such a message. its default location is "the windows system directory". Have a meeting, will get back to you on locking.
In regards to your questions about locking…….go to Access command menu and select tools>options>advanced tab, you should have “open the db in shared mode” selected and “no locks” or “edited record” selected as your default locking schema, and “open all databases with record level locking” checked off. Record level locking means just what it says.
With ODBC connected databases, Microsoft Access doesn't lock records; instead, the rules of that SQL database govern locking. In this instance, regardless of the record-locking setting you choose for your database, Microsoft Access always acts as though the No Locks setting has been selected.
.ldb file
I believe should take a closer at this. In most cases, Microsoft Access automatically deletes the locking information file when the last user closes the database file. However, in cases of a lock conflict, the .ldb file may not have been properly closed….so pleased verify (without loading Access) that you have no .ldb file in your Access directory. If the .ldb exists, you have to erase it….but may have to reboot the system before it will allow you to erase.
With ODBC connected databases, Microsoft Access doesn't lock records; instead, the rules of that SQL database govern locking. In this instance, regardless of the record-locking setting you choose for your database, Microsoft Access always acts as though the No Locks setting has been selected.
.ldb file
I believe should take a closer at this. In most cases, Microsoft Access automatically deletes the locking information file when the last user closes the database file. However, in cases of a lock conflict, the .ldb file may not have been properly closed….so pleased verify (without loading Access) that you have no .ldb file in your Access directory. If the .ldb exists, you have to erase it….but may have to reboot the system before it will allow you to erase.
ASKER
Thanks for the info. I'll let you know how it goes.
Hi azjz,
Have not heard from you. Have you resolved your problem? See this link :
http://www.archive.freeola.com/m.p.access/yaaua-attempting-tctsd.shtml
it seems that direct sql code (e.g. to update a table) runs in the background and does not trigger events in access, and thus can cause a conflict with an access update query or entries made on a bound access form if not properly coordinated.
Have not heard from you. Have you resolved your problem? See this link :
http://www.archive.freeola.com/m.p.access/yaaua-attempting-tctsd.shtml
it seems that direct sql code (e.g. to update a table) runs in the background and does not trigger events in access, and thus can cause a conflict with an access update query or entries made on a bound access form if not properly coordinated.
ASKER
Thanks for the info. Still working on the issue. I've checked our network, user's pc, and nothing. Also found this article from msdn.
http://support.microsoft.com/default.aspx?scid=kb;en-us;331594
I am able to reproduce the error, and tried coding the MSDASQL as provider into my DAO code, still doesn't seem to help. I also tried to separate the main and sub forms. No answer there either.
Still looking and still frustrated but thanks.
http://support.microsoft.com/default.aspx?scid=kb;en-us;331594
I am able to reproduce the error, and tried coding the MSDASQL as provider into my DAO code, still doesn't seem to help. I also tried to separate the main and sub forms. No answer there either.
Still looking and still frustrated but thanks.
Azjz,
Sorry to hear that your problem is still not resolved. Question (peviously asked,never answered).....do you experience the problem if you manually enter the updates to the tables directly, without going thru the forms? if yes, the forms and form code are not the culprits...probably either bad table structure/relations or bad data; if no, forms/form code/connection are the liikely culprit.
Other:
1. you did check the VB library references in Access and verify that there are no missing library references?
2. Have you recompiled the VBA/VB code with option explicit turned on? Any problems there?
3. Look at the data in the tables are there any duplicates? Are the table indexes working properly.....if not they should be deleted and rebuilt (takes only a few minutes). I mention this because it may be corrupt indexes that are creating the perception by Access that there is a locking conflict.
Sorry to hear that your problem is still not resolved. Question (peviously asked,never answered).....do you experience the problem if you manually enter the updates to the tables directly, without going thru the forms? if yes, the forms and form code are not the culprits...probably either bad table structure/relations or bad data; if no, forms/form code/connection are the liikely culprit.
Other:
1. you did check the VB library references in Access and verify that there are no missing library references?
2. Have you recompiled the VBA/VB code with option explicit turned on? Any problems there?
3. Look at the data in the tables are there any duplicates? Are the table indexes working properly.....if not they should be deleted and rebuilt (takes only a few minutes). I mention this because it may be corrupt indexes that are creating the perception by Access that there is a locking conflict.
ASKER
Hi puppydogbuddy:
Thanks for the additional ideas. I looked at all of them, with the following results:
1) I can add rows directly into the tables without going through the forms.
2) The VB reference libraries in the code database don't appear to be missing anything.
3) Option Explicit is used on all but two forms in the database, the main menu that appears after users sign in and the subform which is on the main form that the error is occurring on. I added Option Explicit in the code on the subform in a test database and compiled, it compiled fine.
4) There's no duplicate data in the tables. The indexes appear to be fine. (But I don't know how I'd know if the indexes weren't fine.)
Still a mystery...
Thanks for the additional ideas. I looked at all of them, with the following results:
1) I can add rows directly into the tables without going through the forms.
2) The VB reference libraries in the code database don't appear to be missing anything.
3) Option Explicit is used on all but two forms in the database, the main menu that appears after users sign in and the subform which is on the main form that the error is occurring on. I added Option Explicit in the code on the subform in a test database and compiled, it compiled fine.
4) There's no duplicate data in the tables. The indexes appear to be fine. (But I don't know how I'd know if the indexes weren't fine.)
Still a mystery...
ASKER
Can I get an opinion on something else I found yesterday. I was on msdn and got some information about service pack fixes for Windows XP. The list mentioned "File Lock or Access Denied Error Message When You Save Files Over the Network." The machines this is happening on run Windows XP. The article states that sometimes when you try to save a file over a network (the database resides on a network server), you can have issues with locked files. However, the error messages you would get are "Cannot Read File, File is locked," "Access denied," or "Lock Error." What's the possibility of the two being related?
If you are able to do the following without error as you stated in your previous post
<<1) I can add rows directly into the tables without going through the forms.>>>>
I would say, not likely........more likely to be missing service pack upgrades for Access and Jet.
However, because your problem is extraordinary, I would check and make sure you are current on all releases/service packs....but would start with Access, then Jet, then XP.
<<1) I can add rows directly into the tables without going through the forms.>>>>
I would say, not likely........more likely to be missing service pack upgrades for Access and Jet.
However, because your problem is extraordinary, I would check and make sure you are current on all releases/service packs....but would start with Access, then Jet, then XP.
Aziz,
How are you doing?? In case you have not resolved error yet, here is another tip:
You can try to trap the error in the Form's Error event...place the following in your error handler
If DataErr = 7787 Then
Msgbox "Write Conflict"
Response = acDataErrContinue
Else
'your normal error handling code
End If
How are you doing?? In case you have not resolved error yet, here is another tip:
You can try to trap the error in the Form's Error event...place the following in your error handler
If DataErr = 7787 Then
Msgbox "Write Conflict"
Response = acDataErrContinue
Else
'your normal error handling code
End If
Flyster