Error 3218 Could not update; currently locked

[achh007]

Hi,

I have a form 3 tables. Table 1 contains records Table 2 Contains an empty table which is a replica of Table 1 but has a few more fields to update.
Now when the user opens the form the Employee id(thro the system login) comes up thro a module which works fine. I have run a dlookup on the form load page where it looks into table 3 where the employee number and name are given. The next field gives the name from D lookup.
When the employee uses the form the enter the customers phone number and search fields based on that number from table 1(again using d lookup) the employee then fills up the new fields and then updates Table 2 with the updated fields. The form then requery and again the D lookup is used for the Employee name. The front end is on the desktop and backend on a network server with read and write right for the employee.
I have 7 employees using the database and the issue which comes up after sometime is the 3218 error. It starts with one and affects all users one by one and the starting error which debugs is the Dlook up for the employee name.
Its a pain as the employees are in different places and telling them to log out doesnt solve the problem. I was wondering if Dlookup is the best way. I am no expert and this i just learnt by reading stuff.

Any help would be much appreciated!

 

[JamesMcS]

Can you copy the code that's failing here?

Also, is the back end an Access database? What are the users doing when the locking error starts coming up?

 

[achh007]

Hi James,

The code which is causing the error is this

Private Sub Form_Load()
CsrName = DLookup("Name", "T Number Fields", "TNumber= '" & Forms![In Life Trigger Tracker]!Username & "'")
End Sub

Yes the back end is an access database - The error just comes up when i guess the form is idle for a while which can be for like 20 mins and when the user inputs the data they get an error or it opens a new form but it is soo slow and doesnt show the employee name + you cant enter any number to search as it comes up with the 3218 error!

 

[JamesMcS]

Code looks alright...

I'd try this - get everyone off the DB but for two users. Try and duplicate the problem and look in the tables to see what's being added - I have a feeling that it's trying to duplicate a primary key autonumber somewhere along the line. If that's the case you're going to need some code to save the record with just the autonumber, so it 'reserves' it for that user's session in the table.

 

[achh007]

Hi James,

Thanks for your reply.
I tried it for 2 users and it was working fine but the more number of users come in it starts giving the error. The other strange thing is that it would work without any errors for 2 hours (even with 7 people) and suddenly start giving errors.

 

[JamesMcS]

I think we need to find out who's editing what, and to compare that with what records are being created - still sounds like a duplicate PK to me at the moment

 

[achh007]

Hi James,

I maybe wrong but when the user opens the form the only code which runs is the Dlookup for the username which hangs up the whole form. If the user tries to enter any number in the search field then it doesn't enter the number and comes up with an error 3218.
I would think if it would be a primary key error then the error should only come up when i click the submit button and not when i open the form.

I have noticed one thing that in the form Table 3 is linked table from the backend database i was thinking if i make it as a table on the form it self and then run a dlook up on it. would it somehow help as every user has their own table to run and may not cause the dlook up error at startup

 

[missinglinq]

This is one of the classic symptoms when you have a multi-user database that has not been split into a front end/back end configuration! Has this app been split?

Linq ;0)>

 

[achh007]

Yes it has been split.
I have the front end on the desktop while the backend is on a shared drive with read and write access.