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?

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.

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

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

 

[John Vinson]

--------------------------------------------------------------------------------

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?

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.

If you have an Autonumber it's ALREADY unique - why are you combining
a unique ID with an RMAType to make a primary key? Normally a
two-field PK combines two values, each nonunique, to get a unique
combination. How is the text value being generated? Might it not be
safer to have an autonumber, numeric, unique ID, and treat the RMAType
as data? It can always be concatenated with the number for display.

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

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

All I can suggest is that you create a Query joining tble2 to
Custoemr_Order by joining OrderID to ID; this will find any existing
records. Your Append query will fail for these since they would be
creating a duplicate. Are any of the other ID's defined with Unique
indexes?

John W. Vinson[MVP]

 

[jbphoenix]

I created a query to join the 2 tables but when I ran the query I didn't get
any results. So that tells me that none of the ID's are duplicated. The
only other fields I'm trying to append are a customerID, buy_rate, sell_rate
and an entityID. Since customers can have more than 1 RMA, duplicates are
allowed. The other 3 fields won't accept a null so they are popluated with
default values. So now I'm stumped cause I'm still getting key violations.

I changed the from an autonumber to using the Dmax function to create the
RMAID. I tried appending just the RMAID, customerID, and the other 3 fields
but still getting key violation.