Can't append all the records in the append query

[Mr_P]

I keep getting the above error in access 2010 and for the life of me cannot see where the error is. I have checked that datatypes are the same. It keeps referring to type conversion failure and didn't add (1) record due to a key violation.

The problem is that it doesn't tell you where the error(s) are, so makes debugging really difficult. How do I go about solving errors like this. Many thanks

INSERT INTO Requests ( [Request no], [Date-time received], Customer, Dept, Type, [Service level], [Received by], [Date-time due], Quantity, [Cust requestor], Status )
VALUES (9896, NOW(), 'DEMO', 'Demo Ltd', 'I', 'S', 'LT', NOW(), 1, 'LT', 'O');

 

[Mihail]

Remove all "required" properties from tables fields. (use a copy, of course) then, step by step, add a field and a value at a time.

INSERT INTO Requests ([Request no])
VALUES (9896);

INSERT INTO Requests ([Request no], [Date-time received])
VALUES (9896, NOW());

etc

 

[Mr_P]

Hi
Now I am getting error like in the screenshot. What do I do now. I have checked for dupes and there are none. Thanks

 

[Mihail]

Hi
Now I am getting error like in the screenshot. What do I do now. I have checked for dupes and there are none. Thanks

View attachment 51743

Should be. Access know better.
In fact, there are not. Yet.
The error message say that the next record (not appended yet) will create the duplicate.

 

[Mr_P]

How would I know what the dupe is? It certainly isn't the primary key which set to number not auto. I have checked required in fields and they are no and allow zero length. Where do I go from here. Thanks

 

[Mihail]

You can remove all indexes (from the table), then empty the table, then append all records and, finally, run a Find Duplicates Query.

Hope this will work.
It is easier to do than to explain, so, if you will upload your DB (2003 version) maybe I'll be able to debug.