Joe Morgan
asked on
Question on appending a query that has a key violation?
I need help with an append query that has a key violation but I don't understand why. The destination table has an autonumber primary key, that is the only key, and I'm not trying to append anything into that field.
I assumed Access would generate an autonumber when I appended the rest of the fields but I don't think that's the case. I am using MS Access 2016.
I assumed Access would generate an autonumber when I appended the rest of the fields but I don't think that's the case. I am using MS Access 2016.
There might also be a unique index on a field in the table, other than the PK.
ASKER
Hi Crystal,
I tried what you suggested and the problem remains. Creating a record manually does get the next autonumber value, but I noticed that running the append query does generate an autonumber but something else is wrong and still giving me a key violation. The previous autonumber was 8 and when I manually input a record the autonumber was 42, meaning that all the failed attempts at running the append query were still generating an autonumber. Using an append query did the same thing and I still get a key violation.
And @Dale Fye, the only unique index is the autonumber. All other fields in the table are "Yes (Duplicates OK)".
I tried what you suggested and the problem remains. Creating a record manually does get the next autonumber value, but I noticed that running the append query does generate an autonumber but something else is wrong and still giving me a key violation. The previous autonumber was 8 and when I manually input a record the autonumber was 42, meaning that all the failed attempts at running the append query were still generating an autonumber. Using an append query did the same thing and I still get a key violation.
And @Dale Fye, the only unique index is the autonumber. All other fields in the table are "Yes (Duplicates OK)".
hi Joe,
re: 8, 42
when you compact/repair, the seed will go back.
the autonumber is not the only thing referred to as "key" ... repeating: you might see this error message if there is a Required* field that is not filled, or if there is a foreign key** value that does not have the corresponding primary key value in the main table.
*do any of the fields have the Required property set to True?
**are any of the fields foreign keys?
have an awesome day,
crystal
re: 8, 42
when you compact/repair, the seed will go back.
the autonumber is not the only thing referred to as "key" ... repeating: you might see this error message if there is a Required* field that is not filled, or if there is a foreign key** value that does not have the corresponding primary key value in the main table.
*do any of the fields have the Required property set to True?
**are any of the fields foreign keys?
have an awesome day,
crystal
when you compact/repair, the seed will go back.Not in all versions and not now that 42 has been added. There is now a gap in the numbers, which is of course fine since autonumbers don't need to be consecutive. They just need to be ascending.
I suggest you recreate the problem with a new table with 2 fields (autonumber field and other integer field).
Create an append query and try.
Doing so, you might solve your problem.
If you manage to reproduce the issue, upload for inspection.
Create an append query and try.
Doing so, you might solve your problem.
If you manage to reproduce the issue, upload for inspection.
This question needs an answer!
Become an EE member today
7 DAY FREE TRIALMembers can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
you might see this error message if there is a required field that is not filled, or if there is a foreign key value that does not have the corresponding primary key value in the main table.
try creating a record manually. Does it get the next AutoNumber value? Sometimes the correct place to start has to be re-seeded -- do this by appending one record to that table and assigning the AutoNumber value yourself. Then compact/repair. Usually CR reseeds the AutoNumber ... but not always, which is why I suggested an Append Query to do it.
have an awesome day,
crystal