Access - Getting an error I cannot figure out why

Hi Maxine,

Thank you for sending over the full procedure along with the screenshot. That level of detail is genuinely helpful, and I can see why this one has been frustrating, because the message points to another user when you are the only person working in the database.

To make sure we can solve the right problem together the first time, could you confirm some things for me?

• Are the tables behind this form stored locally in Access, or are they linked tables connected to SQL Server or another back end through ODBC? Linked tables can produce the very same message for an entirely different reason, so this determines which direction we take.
• Does the message appear every single time you leave the Lot No field, or only when you answer Yes to the prompt about updating the chemistry? This tells us whether the three queries are involved at all.
• Could you tell me what each of the three queries does, and in particular whether NewMN updates the same record that is currently open on the subform?

In the meantime, here is my working theory, which should already make sense of what you are seeing.

1. The line Me![Heat No] = DLookup(...) puts the form into edit mode. Your change is sitting in memory and hasn't been written to the table yet.
2. Then DeleteOldChemPerCert, NewMN and AddToChemPerCert run. These write straight to the table — including the very record your form is still holding.
3. When acCmdSaveRecord finally fires, Access compares the record on disk with the version it started editing, sees they no longer match, and reports a write conflict.

The fix is to save the record before the queries run, so nothing is left pending:

Private Sub Lot_No_AfterUpdate()   ' AfterUpdate is safer than LostFocus
        Dim rreply As Integer, OldHeat As String
        OldHeat = Nz(Me![Heat No], "")
        Me![Heat No] = DLookup("[Heat No]", "Lot Nos", "[Lot No]='" & Me![Lot No] & "'")
        If Nz(Me![Heat No], "") <> OldHeat Then
            rreply = MsgBox("Do you want to change the chemistry to the new Heat No?", _
                            vbYesNo, "US Welding")
            If rreply = vbYes Then
                If Me.Dirty Then Me.Dirty = False               ' <-- the key line
                If Forms!CertInput.Dirty Then Forms!CertInput.Dirty = False
                CurrentDb.Execute "DeleteOldChemPerCert", dbFailOnError
                CurrentDb.Execute "NewMN", dbFailOnError
                Me![MasterNo].Requery
                Forms!CertInput!MN = Left(Forms!CertInput!MN, _
                    InStr(Forms!CertInput!MN, "-")) & Me!LineNo & "-" & Me![Lot No]
                If Forms!CertInput.Dirty Then Forms!CertInput.Dirty = False
                CurrentDb.Execute "AddToChemPerCert", dbFailOnError
            End If
        End If
    End Sub

Three smaller changes worth keeping:

• Me.Dirty = False commits the pending edit, so the queries and the form are no longer fighting over the same record.
• CurrentDb.Execute ... dbFailOnError replaces SetWarnings False/True. If the code ever errors out halfway, SetWarnings stays off and every warning in your database goes silent from then on.
• LostFocus fires even when someone just tabs through the field without changing anything; AfterUpdate only fires on an actual edit.

Noted: if Lot No is a number rather than text, drop the single quotes in the DLookup.

For your references that you can concern:

• Form.Dirty property (Access) | Microsoft Learn
• Database.Execute method (DAO) | Microsoft Learn
• Write Conflict dialog box | Microsoft Support

I hope the information above helps point you in the right direction. If you have any updates after trying these steps, please feel free to share them here, and I'll be happy to continue assisting you.

Warm regards.

If the answer is helpful, please click "Yes". If you have extra questions about this answer, please click "Comment".