Full Version: Error On Updating Subform
UtterAccess Forums > Microsoft® Access > Access Forms
I have a main form and a sub form in a datasheet view. They are linked through 2 ID fields which form the primary key for the table attached to the main form.

I call the main form the first time to insert records in to the DB and then call it later to update the records. I'm able to update the record on the main form but as I click on the subform I get an error saying "Cannot perform cascading fucntion. It would result in a duplicate key in table ''"

Curiously, I do not get this error when I try to update the data in the first record in the database.
It would help to know which tables are used as the record source for the main form and subform. The record sources could be queries based on those tables.

It would have helped also to know if any of the primary key (PK) fields are autonumber, but for one thing you should not have a relationship between PK fields. If the PK is autonumber the linking field needs to be Number (Long) since an autonumber value is a Long.

The relationship shows that one Incoming(?) could be associated with many Orders, and one Order with many Routings. It is not clear how Sizes fit into the picture, but the directional relationship (outer join) on two fields doesn't look quite right.

Note that you can have a unique index on a field or combination of fields without that field or those fields being the PK.

It might help also to understand a little more about the real-world situation behind the database.
Thank you so much for your reply!

So, I'm trying to get a software to track production in our factory. We get steel coils which is given a unique SMPL Number (alphanumeric). These coils have to be cut to size through various operations for our operations.

So, when a coil enters our premises an entry in Incoming table is taken. A coil could have one or more orders to it (but only one at a time) and this is tracked in the Order table (Order_Id is an Autonumber). The operations to be performed on the coil are entered in routing (entered in the routing table) and each operation could result in different sizes of the final product which is entered in the Sizes table. The Step_No and Size_Id are incremented in code to maintain uniqueness. The database works all fine till here.

Now, that I have entered the details. I would like to enter the start time and end time of the step, which is also accepted in the form. But when I want to mark a particular size complete, no matter where I click on the subform, I get the error message.

The error message does not show up when I pull up the first record from the Routing table in the form to update. I am able to make the update if I go to the table and change the fields.
I would start by clearing up the PK issues. You should not be joining one PK to another, as described in my previous posting. In the Routing table you can set a unique index on the combination of Order and Step, but you should give the table an autonumber PK as a separate field (I will call it RoutingID). The PK to PK joins are almost certainly the source of the error message.

As for the overall structure, a routing seems to be a series of steps needed to process the order. If so, one Order can have multiple routings (steps). Can each Step have multiple Sizes, or what exactly? If so, a simple relationship can be made with a Sizes table, with a simple join to the Routing table on RoutingID. However, if you choose to work with a compound (multi-field) PK, the linking fields in the Sizes table should not be part of that table's PK. I don't use multi-field PKs, and the little experimentation I did with them was some while ago, so I don't remember the details of how to work with them. I would encourage you to avoid them, and use multi-field indexes instead as needed.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.