Update conflicts on same record

[Gary Schuldt]

I thought this would be easy: I have

Form frmF (edit mode) based on
Table tT (and not a query).

I wanted to extend the info in tT that you could update via frmF.

I designed it so, if you clicked on a row in an existing Listbox in frmF,
the OnEnter event would display a frmPopupN that allowed you to edit a few
additional fields not displayed directly on frmF. The "N" means a
potentially different popup for each item in the Listbox.

frmPopupN is based on a query that's based on tT.

So far so good. But, when I run it and actually type a value into frmPopupN
and close it, I get a message the effect that "Someone else updated the
record". Or, if I don't get that, then, when I close frmF I get a "write
conflict".

I can sorta understand what's happening here--two forms simultaneously open
that could update the same record in the same table.

But I'm not sure of the "correct" way around this. That's where I could use
some help, because I see so many alternate ways to handle this problem. For
example, it occured to me I could put the "extended" fields currently in tT
into another table to avoid the concurrent update conflict, but that would
be unnecessary "hypernormalizing".

There's probably a "best practice" here I should know about.

Thanks,

Gary

 

[Allen Browne]

Hi Gary.

First thing to do is ensure that frmF is saved before frmPopupN is opened to
edit the same record. That should avoid the concurrency issue for simple
cases.

However, if the table has memo fields, this still won't work. In that case,
frmPopupN needs to be unbound. Use its Load event to copy the fields from
frmF into unbound text boxes, and copy them back into frmF's fields at the
end (assuming frmPopupN has Ok and Cancel buttons).

 

[Gary Schuldt]

Hi, Allen,

Boy, I'm starting to see all kinds of problems involved in my current
solution, and I'm willing to rethink the design strategy.

Yes, table tT has memo fields.

I have never dealt with multi-page forms, but I'm starting to get a feeling
that that might be the solution I'm looking for.

Something like this: Page 2 of frmF would be displayed if you clicked on an
item in the Listbox (which currently brings up the frmPopupN). Page 2 would
contain controls for all these additional fields I want to be able to
display and update. Or maybe a n+1-page form, so a click on any item would
take me to its own page, each with a "Top" cmd to get back to Page 1, or
something.

It sounds like I'm trading a messier user interface (multiple pages instead
of popups) for a more robust design (a single form based on a single
object).

I'd appreciate your opinion!

Gary

 

[Gary Schuldt]

Allen,

just had another thought:

I could still go with the individual frmPopupNs, and, as you suggested make
them unbound. Then ON CLOSE of each, copy the fields into hidden controls
on frmF.

Would that work? Then I could have the UI advantage of the popups and avoid
the multipage form.

Gary

 

[Allen Browne]

Yes, that's kinda what I had in mind. Open the popup in acDialog so the user
must deal with that before they go changing record on the original form.
Personally, I would give Ok and Cancel buttons so the user has a way NOT to
transfer their changes back to the original form if they mess up.

Your other suggestion of the tab control with different details on different
pages (all in the same record) also has merit.

 

[Gary Schuldt]

Thanks, Allen, I now have some new ideas on how to handle the situation!

Gary