Duplicate record names error message when only editing existing record..

[vb9063]

Hi Guys, I have the following code in the beforeupdate event for a supplier name. It works if I try and put a new supplier in with the same name (and undoes the entry if it is a duplicate) but if I want to edit the name by perhaps putting a capital letter in then it gives the error message..is there a way to stop this from happening? I have now got a primary key ID number that also identifies the records..

Private Sub Sup_Name_BeforeUpdate(Cancel As Integer)

    Dim SNM As String
    Dim stLinkCriteria As String
    Dim rsc As DAO.Recordset
    Set rsc = Me.RecordsetClone
    SNM = Me.Sup_Name.Value
    stLinkCriteria = "[Sup_Name]=" & "'" & SNM & "'"
    'Check Suppliers table for duplicate Supplier
     If DCount("Sup_Name", "Suppliers", _
              stLinkCriteria) > 0 Then
        Undo duplicate entry
        Me.Undo
        'Message box warning of duplication
        MsgBox "Warning the Supplier: " _
             & SNM & " has already been entered." _
             & vbCr & vbCr & "You will now been taken to the record.", _
               vbInformation, "Duplicate Information"
        'Go to record of original supplier
        rsc.FindFirst stLinkCriteria
        Me.Bookmark = rsc.Bookmark
    End If
    Set rsc = Nothing
End Sub
 
Thanks v much,
VB

 

[missinglinq]

You can test for a new record and only run your check for dups code if it is a new record by wrapping the code like this

If Me.NewRecord Then
 'Validation code here
End If

 

[MarkK]

And for edits you can exclude the current record by modifying the criteria in your DCount() ...

criteria = "[Sup_Name] = '" & SNM & "' AND SupID <> " & me.SupID

 

[vb9063]

THanks guys that worked! Cheers VB