Problem with duplicate values

[darrrellx]

I have created a very simple inventory program just to keep track of items in stock. I created a subform for inventory transactions within a product form. When I try to input a second transaction for a particular product id I get the following message:

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.

Any ideas as to how to fix this or can I even do what I am trying to do.

A copy of the file is attached.

Thanks

Darrellx

 

[whatever]

Darrellx, The problem appears to be the recordsource of the subform includes fields from the product table.

I would:

1. Change the row source of the subform to be just from the inventory table.
2. Link the subform and main form on product ID.
3. Add a command button to the main form that opens a new form which adds a new inventory transaction.

This new form could then :

1. Autopopulate the Product ID of the new inventory record based on the Product Id being displayed, and

2. Run an update query to recalculate the 'units in stock' value in the appropriate products record.

NB As the new form could autopoulate the Product ID and you are holding product ID in the transaction table you can also ditch the linkage table 'tblREL' as you can link Product to 'inventory detail' directly on ProductID = Product ID

HTH



Kevin