Can not update table (1 Viewer)

Prakashpdl

New member
Local time
Today, 13:16
Joined
Apr 25, 2020
Messages
27
I have a main form and 5 subforms all based on single table. When I enter a new record in the main form, it says locked by another user. I am the only user. I can not add a new record to the table using main form or subform. I am new to access.please help.

My form is for data collection and I need to update as well as add new data from time to time. Therefore, I have a main form where there is client information and subforms with the different details of the same client. Subforms are linked with the main form (parent child). I have a serch form as well, so I have main form based on query. Main form and all subforms are looking up the same single table. I have several codes behind subforms as well.
I can change the existing data, but can not add new data. Error msg is "could not update, currently locked by another session on this machine". I am attaching the database, can you please help.
 

Attachments

  • pp database -F4 - Copy.zip
    2.5 MB · Views: 144
Last edited:

plog

Banishment Pending
Local time
Today, 02:31
Joined
May 11, 2011
Messages
11,613
I have a main form and 5 subforms all based on single table.

Why? My gut is telling me you haven't set up your table correctly.

Can you either post your database or take a screenshot of all the fields in that table and post it?
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 07:31
Joined
Jul 9, 2003
Messages
16,245
Are you linking to a spreadsheet?
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 02:31
Joined
Feb 28, 2001
Messages
27,001
By any chance, is any of the subforms linked to the main based on parent/child tables that have a 1-to-1 relationship?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 00:31
Joined
Oct 29, 2018
Messages
21,358
How exactly do you enter a new record on the main form? Is there any code behind the form? If so, can we please see it? Thank you.
 

missinglinq

AWF VIP
Local time
Today, 03:31
Joined
Jun 20, 2003
Messages
6,423
...is any of the subforms linked to the main based on parent/child tables that have a 1-to-1 relationship...

...I have a main form and 5 subforms all based on single table...

The question, to me, is why would you have a Main Form and 5 Subforms based on a single Table?

Linq ;0)>
 

Prakashpdl

New member
Local time
Today, 13:16
Joined
Apr 25, 2020
Messages
27
Why? My gut is telling me you haven't set up your table correctly.

Can you either post your database or take a screenshot of all the fields in that table and post it?
My form is for data collection and I need to update as well as add new data from time to time. Therefore, I have a main form where there is client information and subforms with the different details of the same client. Subforms are linked with the main form (parent child). I have a serch form as well, so I have main form based on query. Main form and all subforms are looking up the same single table. I have several codes behind subforms as well.
I can change the existing data, but can not add new data. Error msg is "could not update, currently locked by another session on this machine". I am have attached the database in my first post, can you please help.
 

plog

Banishment Pending
Local time
Today, 02:31
Joined
May 11, 2011
Messages
11,613
You quoted my post but did nothing that I asked.
 

Prakashpdl

New member
Local time
Today, 13:16
Joined
Apr 25, 2020
Messages
27
By any chance, is any of the subforms linked to the main based on parent/child tables that have a 1-to-1 relationship?
My form is for data collection and I need to update as well as add new data from time to time. Therefore, I have a main form where there is client information and subforms with the different details of the same client. Subforms are linked with the main form (parent child). I have a serch form as well, so I have main form based on query. Main form and all subforms are looking up the same single table. I have several codes behind subforms as well.
I can change the existing data, but can not add new data. Error msg is "could not update, currently locked by another session on this machine". I am have attached the database in my first post, can you please help.
 

plog

Banishment Pending
Local time
Today, 02:31
Joined
May 11, 2011
Messages
11,613
Then you copied/pasted your unrelated message to 3 other people. That's not how forums work. Everyone can see that initial text you put up, you are not personally responding to everyone.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 02:31
Joined
Feb 28, 2001
Messages
27,001
Main form and all subforms are looking up the same single table.

This structuring is the source of your lock conflict. The tables are ALL open for editing - which is fine - but if you actually change something, you have other tables looking at potentially the same data and, as THEY are also open for edit, you block yourself that way.

Rethink this design because it is never going to work quite right this way. You have to be able to get out of your own way.
 

Prakashpdl

New member
Local time
Today, 13:16
Joined
Apr 25, 2020
Messages
27
This structuring is the source of your lock conflict. The tables are ALL open for editing - which is fine - but if you actually change something, you have other tables looking at potentially the same data and, as THEY are also open for edit, you block yourself that way.

Rethink this design because it is never going to work quite right this way. You have to be able to get out of your own way.
In that case, Can I still use all those forms separately to update the single table? Can I use multiple forms to update the same table, without making subforms?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 00:31
Joined
Oct 29, 2018
Messages
21,358
In that case, Can I still use all those forms separately to update the single table? Can I use multiple forms to update the same table, without making subforms?
Hi. You never answered my question. In any case, you can only have one process editing a record at a time. If two processes edit the same record at the same time, one of them will win and the other will lose.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 02:31
Joined
Feb 28, 2001
Messages
27,001
The problem is simultaneous access. It is possible to set a form's lock method to "No locks" and thus allow multiple forms to access the same record simultaneously without stepping on each other. However, there is still the matter of "destructive interference" - commonly described as "the left hand doesn't know what the right hand is doing." If you have two forms pointing at the same table and they happen to select the same record at the same time, the form takes a SNAPSHOT of the record that was selected. If you then start to make changes, you are changing the record based on a snapshot. But if someone else ALSO tries to make changes based on his/her own snapshot of the same record, you and that other person can make successful changes to the same record - but only the one who came in SECOND will win. The person to first make changes will have those changes overwritten by subsequent users.

If we were talking about making changes to different records, the "No Locks" method should work OK. If you are talking about making changes to the same record from different places, you face a terrible dilemma - simplicity or security?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 03:31
Joined
Feb 19, 2002
Messages
42,981
We all agree that your design is a problem. You are getting the error message because as others have alluded to, you are stomping on it yourself. You have the main form open and DIRTY. That means the edited record hasn't been saved You now switch to a different form bound to to the same record and Access knows that someone else is already editing the same record. That is what it is telling you.

You can resolve the problem without changing your tables. Rather than using subforms, create a tabbed form. The form is bound to your table/query. Divide the fields up logically and place them on different tabs. Only the fields on the active tab will be visible but at one time but all the fields will be accessible. Remember, NO SUBFORMS. Just use the tab control to make a visual separation.

Best advice is to fix the underlying design issue before moving on. It only gets harder to fix a bad design as you build more and more functionality that depends on it.
 

Users who are viewing this thread

Top Bottom