Results 1 to 5 of 5
  1. #1
    Join Date
    May 2006

    Unanswered: Form to add to table without duplicates

    Access 97

    I have a table, PartNumber, that I imported from excel. It has the following fields:

    PartID (PK)


    After the import I added the three fields in red. So they contain null data.

    I created a form based on that table using the wizard. It works great for adding a new record with all new information. However, it will let me add a record with a PartNumber that already exists. I want a form that prevents me from adding a duplicate PartNumber.

    Ideally, I would like to enter the PartNumber and when I hit tab to exit the field Access pull up the information if it already exists. That way I can add the missing information. If it doesn't exist, then I can add it.

    I didn't use the PartNumber field as a PK because, in some instances, it can contain a hypen, ie. 17818-05.

  2. #2
    Join Date
    Aug 2002
    Melbourne, Australia
    Put the following code in the Before Update of your PartNumber control; You will have to change "Blue" to suit your database

     If (Not IsNull(DLookup("[PartNumber]", "tblParts", _
                "[PartNumber] ='" & Me!PartNumber & "'"))) Then
            MsgBox "The Part number that you attempted to enter is already in the database. " & vbCrLf & vbCrLf _
             & "Click OK, then re-enter the correct Part Number."
       Cancel = True
       End If

    John A

  3. #3
    Join Date
    May 2006
    Thank you very much. That code does half of what I'm looking for, unless I entered the blue sections incorrectly.

    The code does check and flashes the "already in use" message" for a duplicate. What I hope is possible is that it pulls up the existing record so I can fill in the blank sections.

    My table Is called "PartNumber" and unfortunetly I named a field in that table "PartNumber" also (as shown above). I will change the table name in the future to avoid confusion but currently I have many queries based on it.

    Thanks for looking and most importantly your help!

  4. #4
    Join Date
    Sep 2003
    The extremely Royal borough of Kensington, London
    Create a constraint and all of your problems will be solved.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  5. #5
    Join Date
    May 2006
    Thank you r123456 for adding to my post. I have used Access for about 4 weeks now. I'm sorry but I don't understand how to do what you suggest. I searched the forums for information on "constraints" but didn't find what I needed to know. If you could elaborate a little more I would appreciate it.

Posting Permissions

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