Conundrum on two forms, same file. (1 Viewer)

[twgonder]

I've got a TableA with lots of fields. Some are basic information and go into FormA.
FormA has a command button that calls FormB that also uses TableA. This is more specific data that is optional for certain types of clients.
I'm trying to keep my forms short/simple, so each form works with a subset of the TableA fields.
But the user may update the record in FormB even though FormA isn't ready to have it's field's updated.
Can you think of any gotchas with this scenario? And how to avoid them?

 

[The_Doc_Man]

The big gotcha is that if both forms have the same record open at the same time, you risk the phenomenon of "destructive interference" - literally "the left hand not knowing what the right hand is doing." Particularly if the fields between A and B on the same record are different fields, nonetheless the same record - with ALL of its fields - is open twice. Depending on exactly how the recordsets and forms were configured, you could do the following: Open A; Open B; Update B; Update A - and because A's copy of the record is OLDER than B's copy, and because the fields don't necessarily overlap, you have a case where A will overwrite what B did OR A will throw an error that will be difficult to recover. And if you did the updates in the reverse order, the update of B would overwrite changes made through A OR cause an error again.

 

[twgonder]

Okay, I did a little test of the above scenario.
If FormA is open and dirty, and then I call FormB, I can't make changes to B. Good enough.
What's the best way to know when FormB opens that it's data is locked, and to maybe just print a message and shutdown?

 

[Pat Hartman]

The best solution is to simply NOT do this. If you insist on doing this, ALWAYS save the current record before opening any form or report. That eliminates conflicts. In fact, even when the second form is not even bound to the same record as the first form, best practice is to save the current record before opening ANY new object. If you don't save the current record, the new report or form won't "see" your pending changes.

An alternative method of organizing data in logical groups is to use a tab control. Do NOT use subforms on the tabs. Let them all be bound to the form's RecordSource. That eliminates any conflict entirely. Typically the tab control is used to show subordinate tables that are children of the main form but that isn't required. To use this method, put the fields you need to always see on the form itself all together at the top. Then put the variable fields in the tab control in the lower part of the form. No coding is required. All the validation still belongs in the form's BeforeUpdate event.

Here's a sample

 

[plog]

I've got a TableA with lots of fields.

Define "lots". Better yet, can you post a screenshot of all the fields in TableA?