Results 1 to 10 of 10
  1. #1
    TWD48 is offline Novice
    Windows 10 Office 365
    Join Date
    Sep 2021
    Posts
    8

    Post Unable to Set Field to Not Allow Duplicates

    Hi everyone,



    I designed an Access database with forms so that our HR depts can enter and track covid records on employees. I was just getting to roll this out in live when a user reported that their entries were creating duplicate records in the table.

    I never could figure out why it would randomly duplicate a record, but I did find a fix by setting the report date indexed setting to Yes (No Duplicates). I should have turn this setting on in the beginning since employees would never have multiple records with the same report date. Unfortunately, Access won't let me save this setting with records currently in the table. I get the message:

    "the changes you requested were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again."


    The only way Access lets me turn this setting on is if I delete all the records in the table. Since I don't really have a field to uniquely identity each row (employees can have multiple covid records), I just used an ID field as a primary key with autoincrement number. I tried deleting all the reports and importing them back in, but I get the same error. I remove all duplicate records and still have the error.

    I don't understand why it would complain about duplicate records or the index when the table has been flushed out. I tried just deleting the ID field completely with no PK and that didn't work either. At this point, I don't know what to do beyond just manually rekeying all the records and I'm not sure that would work either.

    Is my only option to just create a brand new table? Thankfully my table only has 50 records so it's not too late to do a redesign here.

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    What you think of as not being a duplicate in a field may actually be just that because of how you may have applied formatting (esp. with dates). You have to examine all 3 possibilities that are listed in that error message. It may be that you've indexed a field and set it to No Dupes but it could also be one of the other 2 reasons. I can't resolve these seemingly contradictory statements
    I don't understand why it would complain about duplicate records or the index when the table has been flushed out.
    The only way Access lets me turn this setting on is if I delete all the records in the table
    You could use the find dupes query wizard to see if it will show you where the issue lies, or you could change your index(es) to allow dupes.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Can't multiple employees have the same report date? That makes report date unsuitable for your need.
    You need an unique key. How about a compound key of the employeeID and report date, no duplicates?
    Last edited by davegri; 10-12-2021 at 10:31 AM. Reason: clarif, sp

  4. #4
    TWD48 is offline Novice
    Windows 10 Office 365
    Join Date
    Sep 2021
    Posts
    8
    Quote Originally Posted by davegri View Post
    Can't multiple employees have the same report date? That makes report date unsuitable for your need.
    You need an unique key. How about a compound key of the employeeID and report date, no duplicates?

    Yes, multiple employees can have the same date, but the same employee can't have the same report date more than once. I tried deleting all the indexes and then making a compound index like you suggested but Access is still giving me the error when I try to paste the data back in. Seems like my only option is to just manually remove duplicates when they happen since Access is just making it impossible for me to change this setting.

  5. #5
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Quote Originally Posted by TWD48 View Post
    Yes, multiple employees can have the same date, but the same employee can't have the same report date more than once. I tried deleting all the indexes and then making a compound index like you suggested but Access is still giving me the error when I try to paste the data back in. Seems like my only option is to just manually remove duplicates when they happen since Access is just making it impossible for me to change this setting.
    Can you post your DB with just a few records (you can change the employee name to fictitious) that replicates the problem for us to examine?

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Believe me when I tell you that the likelihood of the problem being Access is very remote. I second the motion, even if you have to post only 1 copy of this table. If you need to obscure data that has to remain, consider https://www.accessforums.net/showthread.php?t=77482
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    TWD48 is offline Novice
    Windows 10 Office 365
    Join Date
    Sep 2021
    Posts
    8
    Quote Originally Posted by Micron View Post
    Believe me when I tell you that the likelihood of the problem being Access is very remote. I second the motion, even if you have to post only 1 copy of this table. If you need to obscure data that has to remain, consider https://www.accessforums.net/showthread.php?t=77482

    Thank you. Let me see if I can upload a copy. The data is very sensitive so I have to be careful what I share. I did some more testing and noticed something bizarre. When I try to paste back in the records, it gives me the errors but it does end up pasting about half of them. I tried pasting one that failed with a brand new index number and still wouldn't paste.

    So I went back to my form and tried add a new dummy record. It saved. Then I tried to add one that had failed to paste and I got the duplicate error message. I discovered that it generates an error if I use the original report date of 8/10/2021 but it will let me save the record if I change the report date to today's date. So there is some kind of index reference to that original report date that Access doesn't like.

  8. #8
    Minty is online now VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    It sounds as if you still have a unique index set on just the date, regardless of the employee ID, rather than a compound index.

    A quick check would be to simply open the table, sort by date and see if there are any dates duplicated.
    If not then I suspect that my guess is correct.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  9. #9
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,859
    Look at the index tab for all tables?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  10. #10
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Not sure why everyone is focused on indexes. As I alluded to in my first post, it can also be due to relationships. At least isn't that one of the reasons given in the error message? By now we should know if any were created or not, but we still don't.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

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

Similar Threads

  1. Replies: 7
    Last Post: 01-22-2019, 06:16 PM
  2. Replies: 0
    Last Post: 06-17-2016, 08:56 AM
  3. Replies: 3
    Last Post: 05-31-2014, 11:37 AM
  4. Unable to sort field
    By Lookup in forum Access
    Replies: 5
    Last Post: 08-18-2013, 01:20 PM
  5. Replies: 6
    Last Post: 11-14-2012, 01:06 PM

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