Write Conflict ‘error’

Topic

New Reply

I have a table called ‘tblWorkTypes’ which links to another table called ‘tblWorkTypeBenchmark’ through the ‘WorkTypeID’ which is the primary key in ‘tblWorkTypes’

Each work type (held in tblWorkTypes) can have multiple benchmarks (held in tblWorkTypeBenchmark). The form which people use to edit these benchmarks is based on a query which is just all the fields from tblWorkType and adding the ‘current’ benchmark figure from ‘tblWorkTypeBenchmark’ as well as start and end dates.

I’m trying to design the edit worktype form so that when a user changes the benchmark, the ‘original’ figure stays in the database and the ‘EndDate’ of that benchmark gets updated to yesterdays date. Then what should happen is a new entry is added for the worktype with the ‘new’ benchmark figure, a start date of today and end date of “31/12/9999”

I’ve got the code working ok (I think), but when I close the form I get a ‘Write Conflict’ error saying the record has been changed by another user. I understand why this is happening, but is there a way to ‘suppress’ this error, or should I be updating the benchmark figure a different way? A sample of my code is shown below:

NewBenchmark = Me.txtTargetBenchMark
If OriginalBenchmark NewBenchmark Then

Set qryFindCurrentWorkTypeBenchmark = CurrentDb.QueryDefs(“qryFindCurrentWorkTypeBenchmark”)
qryFindCurrentWorkTypeBenchmark.Parameters(“[varWorkTypeID]”) = Me.txtWorkTypeID

Set rsFindCurrentWorkTypeBenchmark = qryFindCurrentWorkTypeBenchmark.OpenRecordset(dbOpenDynaset)

If rsFindCurrentWorkTypeBenchmark.RecordCount > 0 Then
rsFindCurrentWorkTypeBenchmark.MoveLast
rsFindCurrentWorkTypeBenchmark.MoveFirst
End If

If rsFindCurrentWorkTypeBenchmark.RecordCount = 0 Then
MsgBox “Could not find the current benchmark for this work type. Please see the database administrator”, vbInformation, “STOP!”
Exit Sub
ElseIf rsFindCurrentWorkTypeBenchmark.RecordCount > 1 Then
MsgBox “Found more than one ‘current’ benchmark for this work type. Please see the database administrator”, vbInformation, “STOP!”
Exit Sub
Else
rsFindCurrentWorkTypeBenchmark.Edit
rsFindCurrentWorkTypeBenchmark.Fields(“EndDate”) = Format(Now() – 1, “dd/mm/yyyy”)
rsFindCurrentWorkTypeBenchmark.Update
rsFindCurrentWorkTypeBenchmark.AddNew
rsFindCurrentWorkTypeBenchmark.Fields(“WorkTypeID”) = Me.txtWorkTypeID
rsFindCurrentWorkTypeBenchmark.Fields(“TargetBenchMark”) = Me.txtTargetBenchMark
rsFindCurrentWorkTypeBenchmark.Fields(“Startdate”) = Format(Now(), “dd/mm/yyyy”)
rsFindCurrentWorkTypeBenchmark.Fields(“EndDate”) = “31/12/9999”
rsFindCurrentWorkTypeBenchmark.Update
End If

End If

Reply | Quote

Replies

Your code changes a value in a table while the same record is open in a form, with this line.
rsFindCurrentWorkTypeBenchmark.Fields(“EndDate”) = Format(Now() – 1, “dd/mm/yyyy”)

Could you just update the value in the form on the screen instead?

me.txtEndDate = Format(Now() – 1, “dd/mm/yyyy”)
or me.txtEndDate = Date() – 1

The Now() returns the current Date and Time. The Date() function returns just the current Date.

Reply | Quote

Just an idea: Instead of opening a new query instance, why not just work with the form’s recordset via the RecordsetClone?
Regards,
Kirk

Reply | Quote