Data or Index Value Problem 1

[ecpubs]

Very seldom do I cross-post a message, but I think I previously put this question in the wrong forum.

I'm building a simple order management system using MS Access 97. The design uses several tables, but the three involved with this problem are: Customer, Orders, Order Details. There is a one-to-many relationship between Customer and Orders using CustomerID. There is also a one-to-many relationship between the Orders and Order Details, and a relationship between Orders and Payments. I use a Customer Form as the main form, with a nested Orders subform which summarizes relevant order information. The Orders form has a nested Orders Detail form used to collect order details.

After putting the thing together, I added some test data using the Customer form. It worked fine for the customer information. Then I added test data using the Order Details form. For the first three test customers, the system worked fine. But, when I attempted to enter order detail data for the fourth customer I received the following message when I tried to exit the form: "The changes you requested to the table were not successful because they would create duplicate values in the index, primary key or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again."

None of the data field properties are set to prevent duplicates. The OrderID is Autonumber and a Primary Key. The index property of Orders and Order Details is set to Yes(No Duplicates).

I deleted the relationships. No help. I reinstalled the OrderID. Zilch. I redefined the index properties to permit dupes. Didn't help.

I cannot figure out why the first three sets of customer data and order data worked fine, but the fourth entry wasn't accepted. I don't understand whether the problem is originating in table data values, in the control properties, or in the index settings -- or somewhere else.

Any recommendations and suggestions to help me solve this problem is appreciated.

Thanks.

 

[Maquis]

What is the primary key of your Order details table? It sounds like you tried to enter a dup primary key there.

I'm thinking that the Order Details table should have a combined Primary key of Order Number + Product Id. Maq B-)
<insert witty signature here>

 

[ecpubs]

The Order table uses OrderID as the Primary Key and is autonumber, long integer and indexed as Yes(No Duplicates). The OrderID has a one-to-many relationship to the Order Details table and the Payment Table.

The Order Details table uses OrderDetailID as the Primary Key and is number, long integer and indexed as Yes(No Duplicates). I also tried setting the OrderDetailID to autonumber and text, but neither setting worked.

The Products table uses ProductID as the Primary Key and has a one-to-many relationship to the Order Detail table. The ProductID is autonumber set to long integer and indexed as Yes(No Duplicates).

I agree with you that the Order Details table should have a combined Primary key of Order Number + Product ID and thought I accomplished that using the approach outlined above. Maybe I didn't get it right.

 

[ecpubs]

Maq,

Update. It looks like I was able to solve the problem.

Here's what seems to have worked. I deleted all of the relationship links between the tables. I eliminated all Primary Keys. I deleted the ID entries for the tables involved. I closed MS Access completely. Restarted Access. Brought up each table, reinserted id information, reset all Primary Keys to Autonumber and re-established all relationships as before. The entire system now works correctly.

I am only guessing, but it appears that Access either allowed multiple relationships between the tables or somehow had corrupted the id control information. No multiple relationship link lines appeared on the screen, all parent-child links were correct and no corrupt control information was obvious. However, when all relationship links, Primary Keys and controls were re-establish from a clean startup, everything worked. I don't have a clue.

Thanks for your help.

Ed