[AccessD] Prevent Duplicates

Virginia,

    My sample db named Forms_CheckReqdAndDuplicates might be of interest to you. It is available at Rogers Access Library (other developers library). Link - http://www.rogersaccesslibrary.com

    You could adapt the underlying approach suitably, for your specific needs.

Best wishes,
A.D.Tejpal
--------------

  ----- Original Message ----- 
  From: Hollis, Virginia 
  To: accessD at databaseadvisors.com 
  Sent: Wednesday, December 14, 2005 21:19
  Subject: [AccessD] Prevent Duplicates


  I do not want the user to duplicate a number already used when entering a new record. They enter the permit number (text) for the record. I tried the below code on the BeforeUpdate, AfterUpdate, LostFocus of the field, but the duplicate MsgBox never pops up telling them it is a duplicate permit number. They move to another field without the message telling them to enter a different number & it does not even undo the entry, it does not let them save the record either (nothing happens when you click save). Do I have the quotes wrong for a text field around
  PermitNumber? Or where should I place the code? Somewhere on the form or does it go on the field? 

  Virginia

   

  Private Sub PermitNumber_BeforeUpdate(Cancel As Integer)

    If Not IsNull(PermitNumber) Then

          If DCount("PermitNumber", "tblPermitLog", "PermitNumber = " &
  [PermitNumber]) > 0 Then   

              MsgBox "You have entered a Permit Number that already
  exists"

              PermitNumber.SetFocus

              PermitNumber.Undo

          End If

      End If

  End Sub