Checking for duplicate data
Posted on 2012-04-03
I have a very simple form called form2 based on a table called final_tbl. The field on my form is called ser_serialnumber (based on final_tbl). Basically the operator will scan a barcode tag into the field on the form. I have the following code that checks to see if the serial number is a duplicate.
Private Sub SER_SerialNumber_BeforeUpdate(Cancel As Integer)
Dim Answer As Variant
Answer = DLookup("[ser_serialnumber]", "final_tbl", "[ser_serialnumber] = '" & Me.SER_SerialNumber & "'")
If Not IsNull(Answer) Then
MsgBox "Duplicate Serial Number" & vbCrLf & "Please enter again.", vbCritical + vbOKOnly + vbDefaultButton1, "Duplicate"
Cancel = True
The issue that I am having is that I have the form set to open to a new record and if I scan in a serial number that I know is a duplicate it will take it. If I scan it a second time the code will work. This seems to happen for any known duplicate the first time any record is scanned. After the first scan is taken care of the code seems to work. I am not a programmer so I don't know what I could be missing.