Form Write Conflicts in VBA

[ewong1]

I have a Single Form(frmSequenceTemplates) that contains a Continuous Sub-Form(frmSelect). I have a series of fields in frmSelect(one in particular called templateVersion and another called templateComment). In Sub, I have an event procedure that is called on the "After Update" command that I would like to retrieve a comment and update the current record in the continuous form. See the code below:

    Dim WineType As String
    Dim Version As Integer
    Dim site As Integer
    Dim year As Integer
    Dim Comment As String
    Dim sql As String
    Dim update As String
    
    WineType = [Forms]![frmSequenceTemplates]![frmSelect]![tmplt_wn_typ_i]
    Version = [Forms]![frmSequenceTemplates]![frmSelect]![ver_n]
    site = [Forms]![frmSequenceTemplates]![site]
    year = [Forms]![frmSequenceTemplates]![year]

    Set db = CurrentDb()
    sql = "SELECT cmnt_e FROM wmgtp03_wp_tmplt_hdr WHERE tmplt_i = '" & WineType & "' AND ver_n = " & Version & " AND site_sys_I = " & site & " AND yr_n = " & year
    Set rs = db.OpenRecordset(sql)
    rs.MoveFirst
    
    Comment = rs.Fields(0)
    
    If (Comment = Me.templateComment) Then
    Else
        Me.templateComment = Comment
    End If

This seems to work fine if the record is being updated for the first time(i.e. null turns into "test comment") OR if the records comment has changed(i.e. "test comment" turns into "test comment 1").

Where I am having trouble is if the comment matches exactly what it was before(i.e. "test comment" turns into "test comment"). In this case the system is giving me a Write Conflict error: "This record has been changed by another user since you started editing it. If you save the record, you will overwrite the changes the other user made. Copying the changes to the clipboard will let you look at the values the other user entered, and then paste your changes back in if you decide to make changes."

Any ideas on why this is happening and how I can fix it would be appreciated. The desired result would be the system overwriting the comment field every time.

Thanks in advance for your help!

 

[Pat Hartman]

You are conflicting with yourself. Move the code to the form's BEFOREUpdate event. Modifying the current record in the form's AfterUpdate event puts the code into a loop that Access eventually breaks out of when the call stack gets full.

 

[ewong1]

Thanks for trying. I moved the code into the BeforeUpdate event and the system is still behaving the same?

 

[Pat Hartman]

Are you trying to update the current record with that SQL statement?

 

[ewong1]

Yes I am. Another technique I tried was to change the text field in my form to a Combo Box that is linked to a PK that pulls in my comments. Unfortunately, that yielded the same results.

 

[Pat Hartman]

If the code is running in the subform, use something like:

Me.Comment = Me.Parent.SomeField


If the code is running in the main form, use something like:
Me.SubformName.Form!Comment = Me.SomeField

Generally you would not be entering data on a main form that you actually want to store on a subform record. You might want to redesign your form.