Fun with error 3022 ("Duplicate" values)

[Nick via AccessMonster.com]

Hello,

I've been getting runtime error 3022 lately (Changes not successful because
they would create duplicate values in the index, primary key, or relationship.
...)

My SQL string for my append query is as follows:

INSERT INTO [Tbl - MKC BOM] SELECT [Tbl - New BOM].* FROM [Tbl - New BOM]
WHERE (([Tbl - New BOM].Unicode = 'AAF00087') AND ([Tbl - New BOM].Category =
'Parts'));

[Tbl - MKC BOM] and [Tbl - New BOM] have the same field names, which are:
- ID (autonumber, PK)
- Unicode (Indexed, duplicates OK)
- Part Number (Not indexed)
- Quantity (Not indexed)
- Description (Not indexed)
- Category (Not indexed)

The fact that I am trying to "insert a duplicate value" leads me to wonder
how Access handles the autonumber in the ID step; will it try to copy over
the autonumber, causing an error? I have no other fields that do not allow
duplicates, so I really don't know where to start to solve this problem.
I've compacted and repaired, but nothing was fixed.

Also, the error includes "duplicate values in the relationship", but the
Access help leaves that part out; could a table relationship somehow be
causing this?

Thanks in advance,
Nick

 

[John Vinson]

The fact that I am trying to "insert a duplicate value" leads me to wonder
how Access handles the autonumber in the ID step; will it try to copy over
the autonumber, causing an error?

Yes, and that's your problem.

Rather than using SELECT *, explicitly select all feilds *except* the
autonumber.

John W. Vinson[MVP]