Code to prevent duplicate entry is preventing editing of existing records

[Hrithika]

Given below is my code to prevent entry of duplicate record based on three different fields. This code is in the before update event

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.Title <> "" And Me.StartDate <> "" And Me.EndDate <> "" Then
If DCount("*", "tblLeaveRequest", "[Title]= '" & [Title] & "' And [StartDate] = #" & [StartDate] & "# And [EndDate] = #" & [EndDate] & "# ") > 0 Then
MsgBox "Leave request already exists."
Cancel = True
Me.Undo
End If
End If
End Sub

This code successfully prevents saving of duplicate record. But the bad part is that this code is also preventing editing of any existing records. I do want to keep the ability to update existing records.

Could someone please tell me what is wrong with this code

 

[Isskint]

Your Dcount() statement will include the current record (when editing). You will need to edit your code to ;

Identify new or edited record.
With edited record, include in the Dcount criteria AND pkField <> Me.pkFieldControl

 

[Hrithika]

Thanks for looking. I found the answer. All I needed to do was add an unique field into the where condition and now I can edit the record.

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.Title <> "" And Me.StartDate <> "" And Me.EndDate <> "" Then
If DCount("*", "tblLeaveRequest", "[Title]= '" & [Title] & "' And [StartDate] = #" & [StartDate] & "# And [EndDate] = #" & [EndDate] & "# And [Id]<> " & [id] & " ") > 0 Then
MsgBox "Leave request already exists."
Cancel = True
Me.Undo
End If
End If
End Sub

 

[Hrithika]

Thanks. That's exactly what I did.

Your Dcount() statement will include the current record (when editing). You will need to edit your code to ;

Identify new or edited record.
With edited record, include in the Dcount criteria AND pkField <> Me.pkFieldControl