Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2012

    Question Unanswered: Check for duplicate values in field

    I have a database that records computer and printer equipment in the building. I have found that I have employees that will enter data into a form and leave the serial number (SerNo) field blank. I want to prevent that from happening and prevent duplicate data in this field.
    On the SerNo entry in the table the properties are set to “Yes (No Duplicates)”. I use the code below to check for duplicate data, but when the user goes back and enters a unique value the same error message comes up.
    What appears to happen is once a duplicate data situation occurs it will repeat the error and never let the user continue:

    Private Sub SerialNumber_BeforeUpdate(Cancel As Integer)

    If DCount("serialnumber", "comprt", "[serialnumber] = '" & Me.SerialNumber & "'") > 0 Then
    MsgBox "This Serial Number Already Exists!"
    SerialNumber.BackColor = vbRed
    Cancel = True
    Exit Sub
    End If
    End Sub

  2. #2
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    Put the code in the same control's after update event. Where you have it at present means the event fires fires before the user puts in data so it will always be empty
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jun 2005
    Richmond, Virginia USA
    Provided Answers: 19
    Quote Originally Posted by healdem View Post

    ...Where you have it at present means the event fires fires before the user puts in data...
    How does a Control's BeforeUpdate event fire before data has been entered?

    Linq ;0)>
    Hope this helps!

    The problem with making anything that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts