Write Conflict issue

I have a form/subform set up that uses the main form to choose a group key
for a set of records that then display in the sub-form.

I am using a SQL update statement in the Mainform.fields afterchange routine
to propogate the new data to all records in the table with the group key.

I have used the requery option on the sub-form to reflect the new change
made on the field from the main to the sub form. Once the focus leaves the
main form either by jumping to a field in the sub-form OR reading the next
set of group keys for display, I am getting the Write Conflict. If I simply
ignore the update the underlying table has the correct changes.

Is there a way to programatically choose the ignore option. I have tried
Me.Dirty = false to make the main form forget that it had a change, but that
doesnt work. The SQL update has already forced the write to the disk. Its
the form buffer that is holding the change and it is mistakenly thinking that
another user has changed the data. In the routine I know that my code has
made the update and I want a way to force it to take that and ignore the
rest?


--
Regards,
John

Your evaluation of your problem is spot on.

The simple solution here is to simply force a disk write of the form in
question BEFORE you run the sql, and the problem goes away.


eg:

if me.dirty = true then
me.Dirty = false
end if

-- you sql update code etc. goes here.....

If the records is in a sub form, and your code running from the main form,
in most cases MS access automatically forces a disk write when the focus
changes from the sub form to the main form -- thus this should not present a
problem for you. However in case this is a problem, you could do the
following:

the following the code assumes that you're running in the main form, and a
sub form is giving you a dirty team record problem

if me.mySubFormName.form.Dirty = true
me.mySubFormName.Form.dirty = false
end if

.... you sql update + recordset code, or whatever goes here....


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada

It was the Main form that needed the "cleaning"
I had put me.dirty=false AFTER the run SQL which was why I had the issue.

Works perfectly now.


--
Regards,
John


"Albert D. Kallal" wrote:

Your evaluation of your problem is spot on.

The simple solution here is to simply force a disk write of the form in
question BEFORE you run the sql, and the problem goes away.


eg:

if me.dirty = true then
me.Dirty = false
end if

-- you sql update code etc. goes here.....

If the records is in a sub form, and your code running from the main form,
in most cases MS access automatically forces a disk write when the focus
changes from the sub form to the main form -- thus this should not present a
problem for you. However in case this is a problem, you could do the
following:

the following the code assumes that you're running in the main form, and a
sub form is giving you a dirty team record problem

if me.mySubFormName.form.Dirty = true
me.mySubFormName.Form.dirty = false
end if

.... you sql update + recordset code, or whatever goes here....


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada