Write Conflicts when updating fields on different sub-forms that point to the same table

[egstatus]

Hi everyone,
I have a table that contains approximately 200 fields.
I have a main form with 4 sub-forms. Each of the sub-forms access about 50 fields of the table. Each of the sub-forms record source use the same table.

I have a toggle button where I change from one sub-form to the others. What I do is just make the other form invisible while making the one they selected visible.

There are fields on each sub-form that get calculated as information on the other sub-forms are filled in.

I am running into a problem so when I change something on sub-form 1 for example and then I go to sub-form 2 and change something on sub-form 2. I get a "Write Conflict Error. This record has been changed by an other user since you started editing it. If you save the record you will overwrite the changes the other user made."

What Am I doing wrong here? I can't brake the table into smaller tables.

I am open to suggestions.

Thanks

Ed

 

[dhookom]

I think 200 fields is about 175 too many for a typical normalized table. Have you considered using a tab control with no subforms?

Duane
Hook'D on Access
MS Access MVP

 

[egstatus]

Hi Duane,
If you had no other option but the one I had explained. How would you tackle it?

My thoughts were to create a copy of the current record into temp tables and them point each of the copies to each of the sub forms. Then when I'm done creating/editing the forms update the record of the main table with the copies I created [Just the fields for each of the section that are associated with each form].

Thanks

Ed

 

[dhookom]

You might be able to get by with saving the data as controls are updated.

Duane
Hook'D on Access
MS Access MVP

 

[TheAceMan1]

How are ya egstatus ...

If I were you ... all four subforms would have unbound controls. This would alleviate the error. Its then a simple matter of a single button or event (with code) to save the entire record. You'll need looping structures for this one.

Cheers!

See Ya . . .

Be sure to see FAQ219-2884 Worthy Reading!
Also FAQ181-2886 Worthy Reading!

 

[MajP]

This should work. Like Duane states when you make the form invisible first set the dirty property to false committing your changes. Something like
dim subFrm as access.form
set subFrm = me.somesubformcontrol.form
subFrm.dirty = false

 

[egstatus]

I have broken down my one table into 4 different tables and use each of those table for each of the subforms.

So far its working as intended but I'm still in a testing state.

I thank you all for your suggestions.

Ed

 

[dhookom]

All the hoops you have to jump through when you have way more than the typical number of fields in a normalized table structure.

Duane
Hook'D on Access
MS Access MVP