Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


Check to see if part number already exists in database

Posted on 2014-11-23
Medium Priority
Last Modified: 2014-11-28
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."
    End If

What am I doing wrong?
Question by:SteveL13
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40460662
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."
     End If

Author Comment

ID: 40460678
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."
    End If
End Sub

Open in new window

LVL 48

Assisted Solution

by:Dale Fye
Dale Fye earned 1000 total points
ID: 40460810
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."
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.
LVL 51

Accepted Solution

Gustav Brock earned 1000 total points
ID: 40460934
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


Featured Post

What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question