Can not update table

[Prakashpdl]

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.

 

[plog]

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]

Are you linking to a spreadsheet?

 

[The_Doc_Man]

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]

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]

...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]

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]

You quoted my post but did nothing that I asked.

 

[Prakashpdl]

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]

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]

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]

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]

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]

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]

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.