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("qryFindCurrentWorkTypeBenchma rk")
qryFindCurrentWorkTypeBenchmark.Parameters("[varWorkTypeID]") = Me.txtWorkTypeID
Set rsFindCurrentWorkTypeBenchmark = qryFindCurrentWorkTypeBenchmark.OpenRecordset(dbOp enDynaset)
If rsFindCurrentWorkTypeBenchmark.RecordCount > 0 Then
If rsFindCurrentWorkTypeBenchmark.RecordCount = 0 Then
MsgBox "Could not find the current benchmark for this work type. Please see the database administrator", vbInformation, "STOP!"
ElseIf rsFindCurrentWorkTypeBenchmark.RecordCount > 1 Then
MsgBox "Found more than one 'current' benchmark for this work type. Please see the database administrator", vbInformation, "STOP!"
rsFindCurrentWorkTypeBenchmark.Fields("EndDate") = Format(Now() - 1, "dd/mm/yyyy")
rsFindCurrentWorkTypeBenchmark.Fields("WorkTypeID" ) = Me.txtWorkTypeID
rsFindCurrentWorkTypeBenchmark.Fields("TargetBench Mark") = Me.txtTargetBenchMark
rsFindCurrentWorkTypeBenchmark.Fields("Startdate") = Format(Now(), "dd/mm/yyyy")
rsFindCurrentWorkTypeBenchmark.Fields("EndDate") = "31/12/9999"