Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Sep 2009
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Write Conflict 'error'

    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
    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("TargetBench Mark") = Me.txtTargetBenchMark
    rsFindCurrentWorkTypeBenchmark.Fields("Startdate") = Format(Now(), "dd/mm/yyyy")
    rsFindCurrentWorkTypeBenchmark.Fields("EndDate") = "31/12/9999"
    rsFindCurrentWorkTypeBenchmark.Update
    End If

    End If

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    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.
    Regards
    John



  3. #3
    New Lounger
    Join Date
    Jun 2010
    Location
    Gurnee, IL, USA
    Posts
    8
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Just an idea: Instead of opening a new query instance, why not just work with the form's recordset via the RecordsetClone?
    Regards,
    Kirk

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •