Check to see if part number already exists in database

I have a form which the user uses to enter a new part in the database.  The following code is supposed to halt the entry if the same part already exists.  Note that PART_NO is a text field.

But the code isn't working, the alert pops up even if the part does not exist already.

    'Code to check for existing part...
    Dim Answer As Variant
    Answer = DLookup("[PART_NO]", "tblParts", "[PART_NO] = '" & Me.txtPART_NO & "'")
    If Not IsNull(Answer) Then
        MsgBox "This part already exists in the database.  Please re-enter the details."
    Me.Undo
    End If

What am I doing wrong?
SteveL13Asked:
Who is Participating?
 
Gustav BrockCIOCommented:
You may have better luck using the BeforeUpdate event of the form and its parameter Cancel:

     ' Code to check for existing part...
     Dim Answer As Variant
     Answer = DLookup("[PART_NO]", "tblParts", "[PART_NO] = '" & Me.txtPART_NO & "'")
     Cancel = Not IsNull(Answer)

     If Cancel = True Then
         MsgBox "This part already exists in the database.  Please re-enter the details."
     End If

/gustav
0
 
Rey Obrero (Capricorn1)Commented:
try this codes instead


     If dcount("*", "tblParts", "[PART_NO] = '" & Me.txtPART_NO & "'")>0  Then
         MsgBox "This part already exists in the database.  Please re-enter the details."
     Me.Undo
     End If
0
 
SteveL13Author Commented:
Didn't work.  Sorry.

I am running the code from a command button on a form.  Here is the code with your suggestion in place...

Private Sub cmdCommitPartN_Click()
    'Code to check for existing part...
    If DCount("*", "tblParts", "[PART_NO] = '" & Me.txtPART_NO & "'") > 0 Then
        MsgBox "This part already exists in the database.  Please re-enter the details."
    Me.Undo
    End If
End Sub

Open in new window

0
 
Dale FyeCommented:
Both your DLOOKUP and Rey's DCOUNT syntax are correct, for what you have described.  You might try:
Dim strCriteria as string

strCriteria = "[PART_NO] = '" & Me.txtPART_NO & "'"
debug.print strCriteria
If dcount("*", "tblParts", strCriteria)>0  Then
    MsgBox "This part already exists in the database.  Please re-enter the details."
    Me.txtPart_No.Undo
End If 

Open in new window

At least this way, you can actually see what is being tested, then you can open tblParts and confirm that the value is actually there.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.