Could Not Update, Currently Locked

[jlclark4]

I have been doing a lot of reading on this issue and everyone talks about getting this error when editing records.



I currently have a multi-user environment. Record locking set to No Locks. Approximently 7-10 users at a time inputting into the same form/table. The database is split FE and BE with the BE on the network drive and each individual has their own FE on their desktop. The form is set to Data Entry.

These users editing any records, only creating new ones. Is there some sort of code to help prevent this?

This error became more provenlet after the latest update that was done with the database. There is a start task button that is clicked to record a time prior to fields populating. Once that button is clicked, fields populate. The red means they are required. Then once end task is click, the add record button populates.

Any help/comments/suggestion is greatly apperciated.

[ajetrumpet]

have you considered the fact that code can't run on recordsets at the same time from more than one person?

I think that's the case, isn't it? But at any rate, the primary purpose of record locks anyway, is to PREVENT corruption that would probably take place by more than 1 user trying to run code on a source table. That's not a good idea, IMO.

As a matter of fact, a lot of sophisticated database systems use the locking technique, or flagging technique to prevent multiple updates like this. My personal advice would be to tell your users to just suck it up and deal with the locking because it's a whole lot better than receiving a corrupted database out of your current process. Corruption is extremely serious because there's never any possible way to determine the source of the corruption. The variable count in those scenarios are always too overwhelming.

Oh, and BTW, this post is NOT meant to be condescending in nature. If it sounds like that, please look at it in a different light. (that is the disclaimer that's now required on this forum, per an Admin here. There has been confusion in the past about who is smarter than who.)

Have you experimented with all the different locking options that Access gives you? I think there's 3, right? Record Only, Table, and no locks?

[boblarson]

So why do you have code to set everything invisible BEFORE you save the record? And you aren't really saving the record but are trying to do it by default when moving to a new record.

I would try changing your code by adding this to the top of it:

If Me.Dirty Then Me.Dirty = False

which will force the save and THEN you can set everything's visible property and move to a new record if you wish.

But I would also use the TAG of the controls and set it to be something like

CVisible

and then you can just iterate through using shorter code:

Dim ctl As Control
 
For Each ctl In Me.Controls
   If ctl.Tag = "CVisible" Then
      ctl.Visible = False
   End If
Next

[John_G]

When exactly does the error occur, i.e. what procedure is running or what button is clicked? Does it happen to every user, and more or less randomly? Does it happen to eveyone at the same time? You said that all users are only doing data entry - but are there other users who might be doing something with the data that would cause the locks?

J.

[jlclark4]

@ John G: The error occurs when they click the Start Task button. The fields appear and then they can not input into the first field. The users should only be entering data.

@boblarson. I was pretty new at VB when I created this and have been learning as I go along. I made everything invisible at load because it was the only way I knew how to make things not appear but re-appear when certain things were selected. Field 2 and Field 3 are drop downs in my database (made them text boxes in the sample). When a "Task" is selected from Field 3, certain check boxes populate below. I also did not know how to save a record using code. I used the wizard to create the Add Record button. If I use the If Me.Dirty Then Me.Dirty = False code you stated above, does that start saving the record? I read somewhere about basing the form off a query and filter the query based on user. Is this a possible option?

@ajttrumpet If only telling them to suck it up was that easy.

[boblarson]

I made everything invisible at load

You can just set their visible property to NO in design view and then make them appear as you need them. No need really to do it in the form's On Load event.

If I use the If Me.Dirty Then Me.Dirty = False code you stated above, does that start saving the record?

Yes, it saves the record. It will initiate the save, which will initiate the form's Before Update event which, when it is done, will initiate the form's After Update event and then it will come back to where you were in your code which then allows you to set things invisible. But you need to make sure to move the focus to a control which will remain visible before trying to set the rest to invisible.

I read somewhere about basing the form off a query and filter the query based on user. Is this a possible option?

It is an option.