Record Locking With Access FrontEnd And SQL BackEnd

[Moonshine]

Hi All

Im trying to create an Amend Form, which bascially amends a record that is already in the table. I open the recordset with the following code:

Dim rstEdit As Recordset
    Set rstEdit = CurrentDb.OpenRecordset("select * from [eaOTFormDetails] where eaOtFormId = " & Me.OT_FORM_ID, dbOpenDynaset, dbSeeChanges)

I presume this is where the problem lies, once i have then made the changes i use the following code to edit the record that is in rstEdit:

    With rstEdit
    .Edit
        .Fields!eaRequisitionDate = Me.REQUISITION_DATE
        .Fields!eaRequisitionId = Me.REQUISITION_ID
        .Fields!eaDateOT12RecByLoanstore = Me.DATE_OT12_REC_BY_LOANSTORE
        .Fields!eaDateOT12DeliveredByLoanstore = Me.DATE_OT12_DELIVERED_BY_LOANSTORE
        .Fields!eaDateOTFormReceivedBySs = Me.DATE_OT_FORM_RECEIVED_BY_SS
        .Fields!eaPropertyType = Me.PROPERTY_TYPE
        .Fields!eaReason7DaysExceeded = Me.REASON_7_DAYS_EXCEEDED
        .Fields!eaResponseReceived = Me.RESPONSE_RECEIVED
    .Update
    End With

When i click "Amend" i am prompted with an error that says, unable to conmplete as the you and another user are trying to edit the same record. I guess it means the OpenRecordset command has locked it

How can i get around this? So annoying! I cant close the rstEdit, as i need it open to edit that record.