Can't create a new record

[jzacharias]

I am unable to create a new record due the the following error "The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. " I have looked everything over and can't find how the duplication is occurring. I am pretty sure it is the primary key of a table called Squad, but it's set to autonumber. I have uploaded the database hoping an experienced or fresh set of eyes could spot the problem. The goal of the database is to track squad inventory along with officers assigned to the squads. The main form is generically called "unit_Numbers" which uses "Squad Subform". Everything else seems to work correctly except for creating another squad.

 

[RainLover]

Relook at your Table.

I would think it has a field other than the Primary Key set to Allow Duplicates (No)

 

[jzacharias]

I looked at every table and can't find any other fields set to no duplicates. I am really puzzled.

 

[RainLover]

Open the Table.
Reenter the last record.
It should cause an error somewhere.

 

[jzacharias]

I can enter a new record on in the table and it won't error, but it also won't appear as a new record in the form.

 

[RainLover]

There is something you have not explained fully. So I can't help much more.

If you post a copy of your Database in Access 2003 I will have another look.

For now I am logging off. Back tomorrow if someone else has not jumped in.

 

[Khalid_Afridi]

I can enter a new record on in the table and it won't error, but it also won't appear as a new record in the form.

Are you using Ms-Access 2010? did you compact and repair the database?
sometime it happened when you converted 2003 database to 2010 and if you compact and repair the database the Autonumber seeding get corrupted. This happens in Access 2010.

I am not sure if you have the same problem. Just try the following and let me know:

- Check the Last AutoNumber before sorting
- Sort the specified table by AutoNumber Field and again note the Last Autonumber
- Now try to enter a new record and notice the new Autonumber. Is it already used before??

if YES then I have the solution for you.

 

[jzacharias]

I am using 2010. I have tried compacting and repairing and it still throws an error. I will have to look at it when I get home and get back to you. Thanks for the reply

 

[jzacharias]

I ended up deleting the Key ID field and re-creating it and now it works

 

[RainLover]

I am pleased that I have not made the move past Access 2003.

A 97 and A 2003 are in my opinion the best versions.

 

[jzacharias]

Maybe I'll revert back. Thanks again

 

[Khalid_Afridi]

I ended up deleting the Key ID field and re-creating it and now it works

Deleting Key ID Autonumber is not always a recommended idea, doing so will lead to BIG MESS of records integrity when ID is used as a Primary & Foreign Key constraint in related tables (Relationship with same IDs to other tables).

If you do so you will regenerate all the IDs Autonumbers different way, for example Record No. 10 whose ID is 10 will have now new Key either 11 or 12, and it will effect all the relationship constraint with other tables. The other tables will wrongly represent wrong records and relationship.

The recommended way is to re-seed the Autonumber key through VBA coding and Introduce Primary Key to all the tables related to each other so this problem will not happen again in future (my personal experience)

 

[jzacharias]

I would have no idea how to do that.

 

[jdraw]

For reference, here are some Tips form Allen Browne re Autonumber
http://allenbrowne.com/ser-40.html
http://allenbrowne.com/ser-26.html

 

[RainLover]

I ended up deleting the Key ID field and re-creating it and now it works

Has this been totally successful or do you need a simple way to create a new Autonumber Field with the same Numbers that you currently have.

 

[jzacharias]

That would be nice too. I have received a lot of great help with this post. How could you make it easier?

 

[RainLover]

I have not tested this however I believe it will work. Just in case BACK UP first.

With the Table in question create a new field with Number as the Data type.
Create a query and update this field with the Numbers from the Autonumber Field.
Change the Data Type of the original Autonumber field to Number.
Change the Number Field to Autonumber.

You may have to save the table more than once along the way.
Once you have done this change the Relationship links to the new Autonumber.
Finally Rename the Original by placing a "Z" in front of the name. This way you keep it but it is on the bottom of the list and out of the way. You can delete it when you have completed testing.
Change the new Autonumber to Primary Key. Rename it to the original name.

As I said this is untested, however in theory it should work.

 

[jzacharias]

Sounds like a good idea.... I will try that on the copy that is still messed up.

 

[gemma-the-husky]

if the autonumber gets corrupted, you may find a table tries to re-insert autonumbers already in the file - hence the duplicate key error. you can solve this by "forcing" a new seed

if you delete all the keys, and make new ones - you have real problems if these are foreign keys in another table. if not, you won;t have a problem though.

 

[RainLover]

if the autonumber gets corrupted, you may find a table tries to re-insert autonumbers already in the file - hence the duplicate key error. you can solve this by "forcing" a new seed

if you delete all the keys, and make new ones - you have real problems if these are foreign keys in another table. if not, you won;t have a problem though.

Dave
What I suggested would use the same numbers then Autonumber should follow on. So the Foreign keys would not be a problem.

At least that is what I believe will happen. If not then off to Allen Broun's solution.