Data collisions

[Andy_Khosravi]

I'm having a bit of a nuisance problem with my A97 application, and I
was wondering if any of you might have any ideas on how to get around
it.

The setup: I have a large main table (call it tblBenIssue) that has
around 30 unique fields. In order to avoid over cluttering the form I
generated from that table I organized the fields into topics, and then
created separate forms for each topic (call them frmNtwks and
frmRecovery). Each of those forms links from a main form (call it
frmBenIssue) that contains the basic BenIssue header information. All
of these forms have the same datasource. Also note that ALL of these
forms have subforms (which all link to different tables).

The problem with this design is that the user has data collisions with
themselves sometimes when they edit a field in one form, close it, and
then edit a field in another form. (They get a warning MSG box that
indicates another user has modified their record before they saved it).
Apparently the same user has the same record open twice in edit mode.

I realize at this point that I should have probably broken up the main
table into several smaller tables with 1 to 1 relationships, but I'd
really rather avoid that at this point as I'd have to change a lot of
other things to accommodate this.

So, I've been working on trying to avoid the problem of a single user
having the same record open in edit mode twice with VBA. I thought that
executing a save record command on the form open buttons (before the
docmd.openform) and on the form close events would take care of the
problem. Unfortunately this is not the case (although it does seem to
make it occur less often).

I'm wondering if there is something else I could try to force a record
out of edit mode so that the update collision does not occur.

Any ideas or comments would be appreciated.

Thanks!

[Br]

An***********@bcbsmn.com wrote:

I'm having a bit of a nuisance problem with my A97 application, and I
was wondering if any of you might have any ideas on how to get around
it.

The setup: I have a large main table (call it tblBenIssue) that has
around 30 unique fields. In order to avoid over cluttering the form I
generated from that table I organized the fields into topics, and then
created separate forms for each topic (call them frmNtwks and
frmRecovery). Each of those forms links from a main form (call it
frmBenIssue) that contains the basic BenIssue header information. All
of these forms have the same datasource. Also note that ALL of these
forms have subforms (which all link to different tables).

The problem with this design is that the user has data collisions with
themselves sometimes when they edit a field in one form, close it, and
then edit a field in another form. (They get a warning MSG box that
indicates another user has modified their record before they saved
it). Apparently the same user has the same record open twice in edit
mode.
Use a simpler design.

Use one form but use a TAB control or something to simplify the number
of fields on screen at one time. Or use one long form with page dividers
and use buttons to navigate between pages. Or close all the forms but
the current one so you only have one open at a time.
I realize at this point that I should have probably broken up the main
table into several smaller tables with 1 to 1 relationships, but I'd
really rather avoid that at this point as I'd have to change a lot of
other things to accommodate this.
I don't think there is any need for that here.
So, I've been working on trying to avoid the problem of a single user
having the same record open in edit mode twice with VBA. I thought
that executing a save record command on the form open buttons (before
the docmd.openform) and on the form close events would take care of
the problem. Unfortunately this is not the case (although it does
seem to make it occur less often).

I'm wondering if there is something else I could try to force a record
out of edit mode so that the update collision does not occur.

Simply don't open the same recordset more than once at a time. Change
your form design.
--
regards,

Bradley

A Christian Response
http://www.pastornet.net.au/response

[Andy_Khosravi]

Thanks for the ideas Bradley. Completely closing the form rather than
leaving it open in the background did the trick. I don't know why I
didn't think of that.