Intermittant "Write Conflict"?

[(PeteCresswell)]

I've got a pick list form: Parent form with two subforms.

Both subforms look into the same work table.

The left subform lists rows where a field named IsSelected=True
and the right subform lists rows where IsSelectdd=False.

When the user doubleclicks a row on either subform, I move it to the other
subform by setting !IsSelected=Not !IsSelected and requerying both subforms.

Two of the fields on the rows are dates and the user is allowed to edit them.

Every so often Access pops a "Write Conflict: This record has been changed by
another user since you started editing it." when the user changes a date field
in one of the rows.

It does not seem to be a trappable error because my VBA code isn't catching it
and every routine has an On Error Go To.... in effect.

I made up a mini-application with just that form/subforms plus supporting code,
but I have been unable to provoke the error reproducibly - either in the
original app or the mini-app. It just "happens" every so often.

Anybody been here?

 

[(PeteCresswell)]

Per (PeteCresswell):

but I have been unable to provoke the error reproducibly

Oops... Just figured out how to provoke it.

Sample application with instructions on the form itself at
http://tinyurl.com/u2scs

 

[Albert D. Kallal]

You get a write conflict when:


you dirty a records, and THEN

A) another user edits it
B) You execute sql statements that MODIFY this same record
C) You execute reocrdset code that modifies the record also.

this happens all the time.

In your case, it is "c" from the above.....

So, for MANY cases as a above, I force a disk write. In fact, I even out of
habit force a disk write when I launch atoner form from the current form. (I
wonder is this is why I had MANY clients run for more then 6 years without a
corruption).

Anyway, simply force a disk write before you execute that parent code...

eg:

With Me
.Refresh <--- add this
.Parent.TrancheMoveRiteSingle .txtRecordID
End With

I surprised you not run into this problem...it *used* to happen to me all
the time....
Anyway, you simply have to force the disk write, and THEN that other code
that runs/modifies the data will NOT encounter A DIRTY record.

Anytime code, or anoher user modifys a record that is dirty, but has pending
writes...you going to get a conflict...plain and simple...

Note that me.Refresh is a lazy mans way, and the preferred way is:

if me.dirty = True then
me.dirty = false
end if

(me.Refresh forces a disk write as a side effect of what it is desinged to
do...show updated reocords...but, I have used it for years this way...might
as well start out on the right foot..and use:

With Me
if .dirty = True then
.dirty = false
end if
.Parent.TrancheMoveRiteSingle .txtRecordID
End With

 

[(PeteCresswell)]

Per Albert D. Kallal:

I surprised you not run into this problem...it *used* to happen to me all
the time....
Anyway, you simply have to force the disk write, and THEN that other code
that runs/modifies the data will NOT encounter A DIRTY record.

Anytime code, or anoher user modifys a record that is dirty, but has pending
writes...you going to get a conflict...plain and simple...

Note that me.Refresh is a lazy mans way, and the preferred way is:

if me.dirty = True then
me.dirty = false
end if

(me.Refresh forces a disk write as a side effect of what it is desinged to
do...show updated reocords...but, I have used it for years this way...might
as well start out on the right foot..and use:

With Me
if .dirty = True then
.dirty = false
end if
.Parent.TrancheMoveRiteSingle .txtRecordID
End With

Thanks for bailing me out Albert.

I never, ever, would have figured that out by myself.
I think I never ran in to it before because all of my pick lists were just lists
- the only data updates going on being done by click event code behind the form.

This is the first one I've done where the user can actually modify fields on the
list.

Your reply was the best Christmas present I got this year - and I got a few
pretty decent gifts.

 

[Guest]

I think I've just had the same intermittent problem crop up for due to the
third reason.

One question regarding dirty- Is it just a "flag" then to give status? Or
is there anything else going on when you "reset" it? (ie: like a write).

And do I understand you usually reset dirty to false when you close out a
form that shares a recordset, or do you do it also between
functions/procedures also?

Loralee