Results 1 to 9 of 9
  1. #1
    dmon2010 is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Sep 2013
    Posts
    39

    Need some help preventing duplicate entries please.

    Hi,
    I'm pretty new to Access and I've created a table and a bunch of forms and everything is working good. My problem is that I want to avoid entering duplicate items based on 3 fields and can't figure out how to get it done. I read something about indexing, but tbh (like I said) I'm a total noob at this and have no idea what to do. Any help would be greatly appreciated.

    I dont know what I should post here for anyone to be able to look and and maybe help me out, but I'm using Aceess 2007, my only table (which all of my forms, queries and report work off of) is MainInventory. The 3 fields that need to be checked for duplicates are Item, PN and Version (but version doesn't always apply and is sometimes a blank field).
    For example:

    Item = Filter
    PN = 3665
    version = A

    or

    Item = Bracket
    PN = 45589
    version = (blank)

    When entering new data, I don't want to create another entry if I already have this item in inventory. I'd like to get a error message that says I already have this item (this way I can just go in and change the quantity) or something similar.



    I tried looking around the forums, but didn't find anything that I thought would apply to me. Any help or pointers would be greatly appreciated.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    Could try setting the 3 fields as a compound key, with Version as Indexed: Yes(Duplicates okay)
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    dmon2010 is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Sep 2013
    Posts
    39
    OK thanks. How would I do that?

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    Table in design view, select the 3 fields simultaneously by holding the shift key and right click, 'Primary key'. Then select each field individually and set its properties at the bottom.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    dmon2010 is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Sep 2013
    Posts
    39
    Sorry it took so long for me to reply. I tried what you suggested, but since I don't always have a 'version' for my items, the field is mostly blank so I get an error about not being able to have a null value in a primary key field.

  6. #6
    mrojas is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Sep 2013
    Location
    Concord California
    Posts
    72
    Will there ever be a time when a record is missing the Item or PN fields?
    If these will always be provided, simply check if the Version is blank or null.
    You could do this with a simple If statement:
    If IsNull([Version]) or ([Version]="") then
    ' Use the DCount function to determine if there is a duplicate record
    If DCount(Insert criteria leaving out the Version field)>0 then ' There's a duplicate
    ' Display a message, then exit routine
    Exit Sub
    End If
    else
    ' Do same as above but include the Version
    If count is>0 you have a duplicate, else add a record
    End If

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    That or have the Version field default to 0 or x or None.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    dmon2010 is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Sep 2013
    Posts
    39
    Quote Originally Posted by mrojas View Post
    Will there ever be a time when a record is missing the Item or PN fields?
    If these will always be provided, simply check if the Version is blank or null.
    You could do this with a simple If statement:
    If IsNull([Version]) or ([Version]="") then
    ' Use the DCount function to determine if there is a duplicate record
    If DCount(Insert criteria leaving out the Version field)>0 then ' There's a duplicate
    ' Display a message, then exit routine
    Exit Sub
    End If
    else
    ' Do same as above but include the Version
    If count is>0 you have a duplicate, else add a record
    End If
    OK thanks. Where would I put this? Sorry if that seems like a dumb question, but like I said, I'm new at this.

  9. #9
    mrojas is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Sep 2013
    Location
    Concord California
    Posts
    72
    Not know exactly what input form you're using and its look, I will assume that whatever form you're using has some sort of button the user clicks when done, or when going to next record. I'd put the code behind this button.
    Maybe you could send some screenshots of your input forms.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Preventing duplicate record
    By wpryan in forum Forms
    Replies: 2
    Last Post: 03-30-2013, 09:43 AM
  2. Replies: 2
    Last Post: 02-28-2013, 10:06 AM
  3. Replies: 1
    Last Post: 04-25-2012, 01:57 PM
  4. preventing duplicate records
    By Alliana Gray in forum Access
    Replies: 6
    Last Post: 08-11-2011, 01:18 PM
  5. Replies: 1
    Last Post: 02-03-2010, 08:27 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums