Write Conflict

[uhadey]

Hi, I am working with Access 2003.
I have a MainForm with few subforms. I want to update the "LastUpdated" field in the source table of MainForm, but I get "Write Conflict" message. There are no other users on my PC.
Here is the code in MainForm:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim MyRecSrc As String
Dim rst As DAO.Recordset
Dim db As Database
If Not Cancel Then
Set db = CurrentDb
MyRecSrc = Me.RecordsetClone.Name
Set rst = db.OpenRecordset(MyRecSrc)
rst.Edit
rst.Fields("LastUpdated") = Now
rst.Update
Set rst = Nothing
Set db = Nothing
End If
End Sub

What I am doing wrong? Thank you.

 

[GinaWhipp]

Try using the Forms On_Dirty event. I use...

Me.txtDateModified = Now()

Just change to...

Me.LastUpdated = Now()

Much less coding! Side note, you are trying to UPDATE a record that has not even commited yet which explains the error. If you want to leave it there then you'll have to UPDATE the Primary Key first.

 

[uhadey]

Thank you, GinaWhipp.
First, I do not want too many controls on the form, as I do not want to display this info (even if I put it in Footer/Header and make it invisible, it is still there). However, this is not an issue. More important that OnDirty will update this field even if later the Cancel will be set to -1 in BeforeUpdate, which I don't want. Does it make any sense?

 

[GinaWhipp]

I thonk it makes sense... what you are syaing is if no matter what happens to that record that field will update (including if the User backs out of the changes) which is what I want to happen so I know *someone* touched that record!

 

[uhadey]

I want to update "LastUpdated" only when data was really changed and not when *someone* touched that record. That's why "BeforeUpdate" (If Not Cancel) and not "OnDirty".

 

[GinaWhipp]

Hmmm, well that would be *touched* the record. Just reviewing it won't change anything. But changing *anything* would cause the that field to be updated. Now, if that's not what you want please explain under what conditions you want the feild updated. And let's keep in mind Before_Update still won't work because the record is not commited yet so it doesn't know if it's been changed or not...

 

[GinaWhipp]

Okay, there is a way to control Users by creating your own Undo button BUT if the User simply retypes the value then no control over that because that value though not committed has still changed the record.

A better way might be to confirm changes on the Before_Update event at which point you could return the values to there previous data via acCmdUndo...

http://www.databasedev.co.uk/confirm-record-update.html