"Write Conflict" When Updating Table Behind Form?

[PeteCresswell]

Got a form with a subform based on a work table in C:\Temp.

The subform shows a list of dates and a computed amount for each date.

The amounts were computed into a field in the work table when the form
was loaded.

User changes a date.

In txtDate_AfterUpdate, I then open up a recordset into that work
table and re-compute the amount for each and every date.

Problem is, that somewhere along the line my code is provoking:
--------------------------------------------------------------------------------
Write Conflict: Record changed by another user since you
started editing.... If you save, you will overwrite....."
--------------------------------------------------------------------------------

One of the options in that dialog is "Drop Changes".

Clicking "Drop Changes" gives me the desired result: the new amount
for that date that I computed in VBA is now shown on the subform.
OTOH clicking "Save Changes" causes my re-computed amount tb lost.

I tried setting the subform's .Dirty=False, but no luck.

Anybody care to comment?

 

[PeteCresswell]

Maybe I should add that on the subform I've got .RecordLocks=No Locks

 

[BruceM]

Don't store the calculation result. Instead, calculate it on the fly as
needed.

 

[BruceM]

I was a bit brief in my reply. As I understand it, the message arises
because the SQL is trying to work on the form's current record, or maybe
it's because you are trying to work with a second instance of the recordset
to which the form is bound (I'm not sure which it is).

My original point remains. There are limited legitimate reasons for storing
the result of a calculation, but if you are routinely revising every record
in the table there is something suspect about either your database's
structure or your approach to calculated values.

 

[Albert D. Kallal]

Got a form with a subform based on a work table in C:\Temp.

The subform shows a list of dates and a computed amount for each date.

The amounts were computed into a field in the work table when the form
was loaded.

User changes a date.

In txtDate_AfterUpdate, I then open up a recordset into that work
table and re-compute the amount for each and every date.

Is the above after update event in the sub-form, or main form???

I tried setting the subform's .Dirty=False, but no luck.

At what point? You must run this code RIGHT before you run the sql update
code.

I would thus think that in your after update code, you go:

me.Dirty = false

'...your sql udpate code goes here....

 

[(PeteCresswell)]

Per Albert D. Kallal:

Is the above after update event in the sub-form, or main form???


At what point? You must run this code RIGHT before you run the sql update
code.

I would thus think that in your after update code, you go:

me.Dirty = false

'...your sql udpate code goes here....

I'll work on that... but it's a little more complicated than just
SQL code.

The totals are arrived at by summing a bunch of "Daily Accrual"
records depending on the type of security, the current date, and
a few other things.

I call a subroutine that does a bunch of table .Seeks to get the
numbers tb totaled.

For reasons I can't trot out right here off the top of my head,
an SQL Sum() doesn't fit the situation - or, maybe more properly,
an SQL Sum() and my limited expertise doesn't fit the
situation... -)

I'll try moving the Dirty=False to just before that call - or
even pass a pointer to the subform and keep setting .Dirty=False
over-and-over in the routine.

 

[gllincoln]

Hi Pat,

If I read this thread correctly, you have a user changing the value of a
bound control, then you are running code to edit the same recordset?

If so - then yep, that will pop a write conflict.

The way I've dealt with this has been to requery the form recordset before
the code-based edit - that has worked for me.

This might be worth a try - this code assumes you have a primary key named
ID and its available- you could use something else.

dim rs as DAO.Recordset
dim pk as long

pk = forms!myform!ID
Forms!myform.Requery
set rs = Forms!myform.RecordsetClone
rs.FindFirst "ID=" & pk
Forms!myform.Bookmark = rs.Bookmark
'you could reuse rs at this point to make
'your table edit
Set rs = Nothing


Hope this helps,
Gordon

 

[David W. Fenton]

I call a subroutine that does a bunch of table .Seeks to get the
numbers tb totaled.

Seek? Ack. Why not just filter to the appropriate records and
total/subtotal accordingly?

 

[(PeteCresswell)]

Per David W. Fenton:

Seek? Ack. Why not just filter to the appropriate records and
total/subtotal accordingly?

Too many "ifs", "ands", and "buts" for my limited SQL expertise.

I agree "Ack".... but sometimes you play with what you've got.

 

[(PeteCresswell)]

Per Albert D. Kallal:

At what point? You must run this code RIGHT before you run the sql update
code.

I would thus think that in your after update code, you go:

me.Dirty = false

'...your sql udpate code goes here....

I nailed it. You put me on the right track.

Was setting .Dirty=False and then, later on in the code, doing
something that the form construed as an update before calling my
subroutine. Moved .Dirty=False and it ran like a charm.