Link to home
Start Free TrialLog in
Avatar of SteveL13
SteveL13Flag for United States of America

asked on

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?
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

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
Avatar of SteveL13

ASKER

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

SOLUTION
Avatar of Dale Fye
Dale Fye
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial