CODEWITHSUNDEEP
ms-accessvba
LuckyLuke82
LuckyLuke82

Reputation: 606

Access VBA - prevent duplicate entry

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 ?

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? ?" & vbCrLf & vBCrLf & "Click Yes for saving changes or NO for Undoing changes ! " & _
         , vbQuestion + vbOKCancel + vbDefaultButton1) = vbOK Then

             rs.FindFirst "ID = " & 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

Upvotes: 1

Views: 5672

Answers (3)

Andre
Andre

Reputation: 27634

Let's say your primary key column is myPK.

To check ID_Field against all but the current record, don't use a Recordset and FindFirst, use DLookup instead.

Dim TestID as Long
TestID = Nz(DLookup("ID_Field", "MyTable", "ID_Field = " & Me.ID_Field & _
                    " AND myPK <> " & Nz(Me.myPK, 0)), 0)
If TestID > 0 Then
    ' Dupe
Else
    ' ok
End If

You may need to add myPK as invisible text box to the form, to be able to read its value.

Upvotes: 2

Andre
Andre

Reputation: 27634

To prevent the built-in message, it is best to use the Form.OnError event.
See: https://msdn.microsoft.com/en-us/library/office/ff836345.aspx

E.g. like this:

Private Sub Form_Error(DataErr As Integer, Response As Integer)

    ' Error 3022 = "Cannot add a duplicate value because of a key constraint..."
    If DataErr = 3022 Then
        MsgBox "Your duplicate warning", vbExclamation
        ' Remove duplicate ID and set focus into ID control
        Me!ID_Field = Null
        Me!ID_Field.SetFocus
        ' Dont't show default error message
        Response = acDataErrContinue
    Else
        ' Other errors: show default error message
        Response = acDataErrDisplay
    End If

End Sub

Upvotes: 0

Paul Ogilvie
Paul Ogilvie

Reputation: 25266

A "snapshot" takes the records in their current state for e.g. inspection. They are read-only.

To open the record set read/write, use dbOpenDynaset

Upvotes: 0

Related Questions