Write Conflict error in access even after a requery on the main form

Question

I've done a ton of research on this topic. I've tried every plausible solution under the internet's sun. Maybe I'm not thinking about this logically since I've been trying to figure out a solution for this for a week. After an actual change to the data and a save from the user I get the write conflict error message "Save Change, Copy to Clipboard, Drop Changes" this random little nuisance is becoming a bigger headache than it's worth.

Just for refrence my main form is called frmCNSUpdates which on a button press opens the subform frmContactInfo

What happens is after a user changes data on this subform and clicks the save button, then closes the subform and returns to the main form then proceeds to move away from the record that has been changed in the subform, saves it from the mainform, add it from the mainform, or even close out of the form I get the write conflict error. I have tried refreshing the parent form and the subform, and do a sql injection update instead of a record set, I have even tried just suppressing the error but I fear I'm putting my solutions in the wrong place. Here is the code for the save button on the subform:

Private Sub cmdSave_Click()
On Error GoTo Err_cmdSave_Click

    SaveRecord

Exit_cmdSave_Click:
    Exit Sub

Err_cmdSave_Click:
    MsgBox Err.Description
    Resume Exit_cmdSave_Click

End Sub
Private Sub SaveRecord()
    On Error GoTo Err_SaveRecord

    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim intRow As Integer
    Dim varItem As Variant
    Dim strSQL As String

    DoCmd.Hourglass True    
    strSQL = "select * from tblConstructionUpdates where [ProjectNo] =" & Forms![frmCNSUpdates]!ProjectNo
    Set db = CurrentDb

    Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)

    rst.Edit
     'rst("ProjectNo") = Me.txtID
     'rst("ContractNo") = Me.txtContractNo
     rst("EstimatedCompletionDate") = Me.EstimatedCompletionDate
     rst("PercentComplete") = Me.txtPercentComplete
     rst("SupervisorFirstName") = Me.txtSupervisorFirstName
     rst("SupervisorLastName") = Me.txtSupervisorLastName
     rst("SupervisorOfficePhoneNumber") = Me.txtSupOfficePhone
     rst("SupervisorMobilPhoneNumber") = Me.txtSupMobilPhone
     rst("ResidentFirstName") = Me.txtResidentFirstName
     rst("ResidentLastName") = Me.txtResidentLastName
     rst("ResidentOfficePhoneNumber") = Me.txtResOffice
     rst("TelephoneNo") = Me.txtResMobil
     rst("ResidentHomePhoneNumber") = Me.txtResHome
     rst("ConsultantFirstName") = Me.txtConsultantFirstName
     rst("ConsultantLastName") = Me.txtConsultantLastName
     rst("ConsultantOfficePhoneNumber") = Me.txtConOffice
     rst("ConsultantMobilPhoneNumber") = Me.txtConMobil
     rst("ConsultantHomePhoneNumber") = Me.txtConHome
     rst("Contractor") = Me.txtGenContractor
     rst("GeneralOfficePhoneNumber") = Me.txtGenOfficePhone
     rst("GeneralMobilPhoneNumber") = Me.txtGenMobilPhone
     rst("GeneralHomePhoneNumber") = Me.txtGenHomePhone
     rst("ContractorRep1Name") = Me.cboRep1
     rst("ContractorRep1OfficePhone") = Me.txtOffice
     rst("ContractorRep1MobilPhone") = Me.txtMobil
     rst("ContractorRep1HomePhone") = Me.txtHome
     rst("ContractorRep2Name") = Me.cboRep2
     rst("ContractorRep2OfficePhone") = Me.txtOffice2
     rst("ContractorRep2MobilPhone") = Me.txtMobil2
     rst("ContractorRep2HomePhone") = Me.txtHome2
     rst("TrafficControlCompanyName") = Me.cboTrafficName
     rst("TrafficControlContactName") = Me.txtContact
     rst("TrafficControlPhoneNumber") = Me.txtTrafficPhone
     rst("ElectricalContractor") = Me.txtContractor
     rst("ElectricalPhoneNumber") = Me.txt24hrElecPhone
     rst("StateRep") = Me.txtStateRep
     rst("StateSen") = Me.txtStateSen

    rst.Update
    blnChangeMade = False
    rst.Close
    LoadScreen

    MsgBox "The record has been saved.", vbOKOnly, "Save Complete"

Exit_SaveRecord:
    DoCmd.Hourglass False
    Exit Sub

Err_SaveRecord:
    LogErrorDAO Err.Description, CStr(Err.Number), Application.CurrentObjectName, _
                "SaveRecord"
    Resume Exit_SaveRecord

End Sub

Jens Jens · Accepted Answer · 2014-08-20T14:20:44

Change all the rst("XXXX") to rst![XXXX] = me.etc , or do it like:

with rst
    .edit
    ![XXX] = me.XXX
    ![AAA] = me.AAA
    ...
    ![something] = me.something
    .update
end with

That's a start. (Note that the [] are actually only needed in cases of spaces.)

Write Conflict error in access even after a requery on the main form

1 Answers