Solved But it's not a duplicate - so why is Dcount() saying so? (1 Viewer)

[dgreen]

Visually, it's intuitive. Record 1 and 2 are not duplicates. So what did I do wrong in the code? Or should I place the code on a different triggering event?

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim ClickResult As VbMsgBoxResultEx
    'Make sure there is some value in the field.  If not, remove the blank cell.
    If Nz(Me.Certification_ID, "") = "" Then
        Me.Undo
        Exit Sub
        
    ElseIf DCount("*", "[t_Contacts_Certification_Relationship]", "[Name_ID]= " & Me.[Name_ID] & " And [Certification_ID]= " & Me.[Certification_ID] & " And [Certication_Level_ID]= " & Nz(Me.[Certication_Level_ID], 0)) > 0 Then
        ClickResult = Dialog.RichBox("This is a duplicate record. " & "</p>" & _
                      "Click OK to remove it.", vbOKOnly + vbCritical, "Duplicate Entry", , , 0, False, False, False)
        If ClickResult = vbOK Then
            Me.Undo
        End If
    End If
End Sub

 

[arnelgp]

test it against "> 1"

 

[The_Doc_Man]

Your first question: The _BeforeUpdate event on the form is a decent place for error checking.

Your second question: Depends on information not immediately visible from your exhibit. What are the data types of [Name_ID], [Certification_ID], and [Certification_Level_ID] ?? I'm suspecting that a data type mismatch is causing something to not compare correctly but I can't tell from what I see (which is why I asked the question I did).

A third point that you didn't ask... You certainly CAN use a Me.Undo to lose ALL data you would have saved, but you have that great-big Cancel argument that is even easier (and less disruptive) than a Me.Undo. If you would want to preserve what had been selected but just not try to do an update, I would think you could do a Cancel = -1 followed by your Exit Sub and prevent an unwanted save. Which would allow someone to correct the error and try again.

 

[HiTechCoach]

Visually, it's intuitive. Record 1 and 2 are not duplicates. So what did I do wrong in the code? Or should I place the code on a different triggering event?
View attachment 80472

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim ClickResult As VbMsgBoxResultEx
    'Make sure there is some value in the field.  If not, remove the blank cell.
    If Nz(Me.Certification_ID, "") = "" Then
        Me.Undo
        Exit Sub
     
    ElseIf DCount("*", "[t_Contacts_Certification_Relationship]", "[Name_ID]= " & Me.[Name_ID] & " And [Certification_ID]= " & Me.[Certification_ID] & " And [Certication_Level_ID]= " & Nz(Me.[Certication_Level_ID], 0)) > 0 Then
        ClickResult = Dialog.RichBox("This is a duplicate record. " & "</p>" & _
                      "Click OK to remove it.", vbOKOnly + vbCritical, "Duplicate Entry", , , 0, False, False, False)
        If ClickResult = vbOK Then
            Me.Undo
        End If
    End If
End Sub

This is normally caused by not filtering out the current record being updated in the DCout()'s criteria.

Example:

Assuming the field is called ContactCertPKey

You must filter out the current record:

ContactCertPKey <> Me.ContactCertPKey

DCount("*", "[t_Contacts_Certification_Relationship]",  "[ContactCertPKey] <> " & Me.ContactCertPKey 
& "[Name_ID]= " & Me.[Name_ID] & " ...

Now you are looking for records that are not the record you are editing.