Key Violations

[jbphoenix]

I have a form with a command btn on it that runs an append query. I am appending data from tble2 to Customer_Order. Customer_Order is a linked table from our MRP application. I'm only trying to append 5 fields right now. They are the fields that are required and won't accept a null in my linked MRP table. However I am getting a Key Violation error. Below is table structure for each table. The OrderID is a combination of 2 fields from a form - would that cause any problems? Any ideas?

tble2
OrderID - text, required
CustomerID - text, required
EntityID - text, required
BuyRate - number, required
SellRate - number, required

Customer_Order
ID - text, required
Customer_ID - text, required
Entity_ID - text, required
Buy_Rate - number, required
Sell_Rate - number, required

 

[Moniker]

The key violation means you are trying to duplicate a primary key field, which I'm guessing is OrderID. If the field value in tble2 for OrderID already exists in the Customer_Order table's OrderID field, that is the key violation and it will not write the value. Each primary key must be a unique value.

 

[jbphoenix]

You are correct - the OrderID is the primary key in the Customer_Order tble. The OrderID field in tble2 is a combination of an autonumber (RMAID) and an RMAType (A,C,or R). The new OrderID for example - A4008 is not in the Customer_Order tble. I searched the Customer_Order tble and wasn't able to find a match. So that is why I am puzzled as to why I still have a key violation.

 

[jbphoenix]

Ok I have changed things a bit. My RMA# is now a number generated using the Dmax function. I was using an autonumber but after doing more research it sounded like the autonumber wasn't the way to go. I thought maybe the autonumber may have been causing issues. So I tried to append the data again to the Customer_Order table from our MRP and I still get a key violation. The RMA# is no longer a combined field just the number generated by using the Dmax function. Any suggestions about the key violation? I'm stumped.