title: Access VBA - prevent duplicate entry
body: <p>I'm trying to avoid Access built-in msgbox about entering duplicate records, by checking entries  in before update event. Code works, but one BIG problem - you cannot edit those records anymore. Any way to accomplish both - allow edits and avoid Access msgbox ?</p>
  
  <pre><code>Private Sub Form_BeforeUpdate(Cancel As Integer)
  
  Dim rs As Recordset
  Set rs = CurrentDb.OpenRecordset("MyTable", dbOpenDynaset)
  
  If Data_Changed Then 'Variable of Boolean that is set to True in Dirty event
  
       If Not IsNull(ID_Field) Then
  
           If MsgBox("You have done changes. you wish to save? ?" &amp; vbCrLf &amp; vBCrLf &amp; "Click Yes for saving changes or NO for Undoing changes ! " &amp; _
           , vbQuestion + vbOKCancel + vbDefaultButton1) = vbOK Then
  
               rs.FindFirst "ID = " &amp; Me.ID_Field
  
               If Not rs.NoMatch Then
                   Cancel = True
                   MsgBox "Error. You cannot add another record with same ID!", vbCritical
                   DoCmd.RunCommand acCmdUndo
                   Exit Sub
               End If
  
  
           Else
  
               DoCmd.RunCommand acCmdUndo
  
           End If
  
        Else
  
            MsgBox "Error. you cannot save record with blank ID !", vbCritical
  
            DoCmd.SetWarnings False
            If Me.Dirty Then Me.Undo
            DoCmd.SetWarnings True
  
            Exit Sub
  
        End If
  
        Me.ID_Field.Locked = True
        Me.Other_Field.Locked = True
  
  End If
  
  End Sub
  </code></pre>