Error message: "Couldn't save; currently locked by user

[caringsharingbristolbilly]

Hi to all you lovely people in MrExcel.comland...

I've got a bizarre error message appearing on a database I've made. It's a form & Macro driven database that multiple users use at the same time, updating records when they have done work required.

A number of colleagues have had the following message crop up in various situations, but mostly when sorting the records on a form and applying filters:

Couldn't save; currently locked by user 'Admin' on machine '12QHQ0J'
[OK] [Help]

(the 12QHQ0J is the 'service tag' for the specific PC causing the error).

This message occurs when trying to return to the main menu via a very simple macro (closes the window, opens another one, runs a query, then maximises).

If they click on [OK], they get the following messages (in this order):

(i) The save operation failed
[OK]

Out of memory
[OK] [Help]

Action failed
Macro Name: back_to_main_menu_Macro
Condition: True
Action name: Close
Arguments: , , Prompt

Does anyone know why this is happening? There are so many messages, it's difficult to know which one is the important one. Is it because the records are being sorted and filtered? Is it the memory running out?

I've investigated the PCs causing the errors (there's two specifically), and their Record Locking properties are the same as everyone else's, so I've drawn a blank on that one.

Any help would be HUGELY appreciated!!

Many thanks,
CSBBB.

 

[Andrew Fergus]

Re: Error message: "Couldn't save; currently locked by

Hi CSBB

This sounds like a multi-user / record locking problem and IMO the most important error is the first one. The 2nd and 4th errors are a direct consequence of the first but I can't explain the 3rd error (out of memory).

There are some articles from MS here, here and here that I found through here.

You mentioned this :

This message occurs when trying to return to the main menu via a very simple macro (closes the window, opens another one, runs a query, then maximises).

Can you explain what the query is doing? If we can focus on that query it should resolve the problem.

HTH, Andrew.

 

[caringsharingbristolbilly]

Re: Error message: "Couldn't save; currently locked by

Actually, there's a number of queries, I only mentioned one because they're all pretty much identical.

I have four tables of unrelated data which are accessible via a "main menu" screen. This menu uses these queries to show the number of records in total for that department, and the number that are yet to be 'worked' (which it can tell by a field which the user completes for each record). There are 8 queries, one for total number of records, and one for pending records for each of the 4 reports.

I'm not sure how best to display this, but I'll give it a bash on one of them:

SELECT Count(Close_Match_Table.Proper_Department) AS CountOfProper_Department
FROM Close_Match_Table
WHERE (((Close_Match_Table.Proper_Department)=[Forms]![Department_Selection_Form]![DepartmentChoices]) AND ((Close_Match_Table.Done_Or_Not_Done)="Not Started" Or (Close_Match_Table.Done_Or_Not_Done)="Awaiting Further Information" Or (Close_Match_Table.Done_Or_Not_Done) Is Null));

If that's complete gobbledegook, let me know. It basically counts the number of records which refer to a pre-selected department that are "Not Started" or "Awaiting Further information".

In all honesty, I'm not sure the queries are the problem. The reason for this is that the fourth message refers to the "Close" part of the Macro, which is the first step.

I've checked all the record-locking avenues your links described, but unfortunately they all appear to be set correctly.

The only avenue I haven't tried is setting up security groups for the database. Could this be of any use? As I'll be handing this over to someone soon (if that elusive promotion comes my way), I need to keep it as simple as poss, so I haven't tried this yet....

I really appreciate your help, any more you can offer would probably make you a deity in my eyes!!! ray:

 

[Andrew Fergus]

Re: Error message: "Couldn't save; currently locked by

Hi CSBB

If you have a look at the macro in design view, in particular the first line which has the close statement, at the bottom of the screen are 3 options : object type, object name and save. Have you set the save option to yes? If so, change it to no - given the user is not changing the design of the form it is not necessary to save the form. If you're not comfortable with this then try it on a copy of the database. Let me know if that works (or not).

HTH, Andrew.

 

[caringsharingbristolbilly]

Re: Error message: "Couldn't save; currently locked by

Hi again Andrew.

I did that as part of the record-locking stuff, and it hasn't helped unfortunately. Sorry, I should have mentioned it, but I've tried so much to try and fix it, I think I just forgot!

I'm at a complete loss. A couple more PC service tags have been popping up, so I'm not convinced any more that it's a couple of users' settings.

It's really bizarre. I'm judging by the All Quiet On The Western Front ambience that greeted the original posting that it's baffled everyone else out there too. I'm beginning to think it might be a glitch somewhere, rather than any specific settings...

Big big thanks for your help so far, Andrew, it's been much appreciated. If you have any further thoughts, please let me know!

 

[Andrew Fergus]

Re: Error message: "Couldn't save; currently locked by

As Homer Simpson would say : "D'Oh!" I thought that would have fixed it. It has me stumped but before I give up I have just a couple more questions.

Regarding this :

This menu uses these queries to show the number of records in total for that department, and the number that are yet to be 'worked'

... are the results from all 8 queries showing on the main form at the same time? Or are you only showing the results for each department two at a time? (i.e. the total and open items)

Lastly, the problematic macro, what form is it attempting to close and which form is it trying to open?

Andrew

 

[Andrew Fergus]

Re: Error message: "Couldn't save; currently locked by

[A93 : This repsonse was edited a couple of times]

I have just gone back and re-read your original question and I see you are returning to the main menu - whoops.

You mentioned that the problems mostly occur when the user sorts and filters the data in the form. It still sounds to me like Access is trying to save the form - it may not be through the close macro, there may be another macro in the form events. In the form design, check the On Unload, On Lost Focus, On Deactivate and On Close properties of the form - are there any other macros there that would interfere with the closing of the form? I still stand behind my diagnosis on the 2nd and 4th errors - I have seen this enough to know they are caused by the first failure (which is the error on closing the form) so I'm 99% certain the problem lies in the closing and saving of the from.

It might be worth rechecking your macro to confirm it is not set to save the form upon closing [edited] - actually I see you had it set to "prompt" - can you confirm that it is set to "no"? Plus you might want to test this such that you know it fails when you filter and sort the data on the form and it doesn't fail when you don't filter or sort the data.

HTH, Andrew.

 

[caringsharingbristolbilly]

Re: Error message: "Couldn't save; currently locked by

... are the results from all 8 queries showing on the main form at the same time? Or are you only showing the results for each department two at a time? (i.e. the total and open items)

- The 'count' results for all the queries are showing at the same time, however, the results showing only relate to one department. I.e. I have 8 numbers showing on the report:

Table 1: No. worked & No. pending
Table 2: No. worked & No. pending... and so on.

Lastly, the problematic macro, what form is it attempting to close and which form is it trying to open?

- It's closing the form which allows users to update records manually (one at a time), runs the queries, then opens up a 'main menu' form which shows the results of the queries, along with a load of macro buttons.

It might be worth rechecking your macro to confirm it is not set to save the form upon closing [edited] - actually I see you had it set to "prompt" - can you confirm that it is set to "no"? Plus you might want to test this such that you know it fails when you filter and sort the data on the form and it doesn't fail when you don't filter or sort the data.

- It's definitely set to "no". I changed it after I originally posted, and problems still occur. It's difficult to test the failure, as it only occurs sporadically. The only thing I can say is that when it does occur, it's repeatable by sorting & filtering but doesn't reoccur otherwise.

I'll check out the things you suggested though, and get back to you with anything I uncover!

Many many thanks again,
CSBBB.