Referential Integrity

[jbarrum]

I have a small database that tracks inventory of pipe brough into our yard by BOL (Bill of Ladding). I want to change the format so that it tracks the inventory by Size.

Every order can have multiple BOL’s and each BOL can have multiple PO’s (purchase orders)



My current design is as follows: (and works)

Table 1 [Orders]
Table 2 [ OrderDetails] – each record in this table is unique by OrderDetailID and holds the details of each order (ie. BOL, PO, Size and Piece Count)
Table 3 [Inventory] – this holds the inventory of pipe by BOL.

Table 4 [Bridge] – This table only holds two fields “OderID” from the table [Orders] and “BOL” from [OderDetails]

[Orders] relates to [Bridge] by way of OrderID in a One[Orders] to Many[Bridge] relationship.
[Bridge] Relates to [OrderDetails] by way of BOL in a One[Bridge] to Many [OrderDetails] Relationsip
[Bridge] relates to [Inventory] in a one[Bridge] to many[Inventory] relationship

I have two quires built. The first query holds the information for Order details. (BOL, PO, Customer, and Size)
The second query holds the inventory detail. (BOL, date, truck #, number of pieces, and weight)

My main from is connected to the First Query where I have a combo box that looks up the BOL and returns the PO,
Customer, size and type.

The subform(in the mainform) is the inventory form and is linked to the second query that updates the Inventory Table.

It works fine, but I want to change my design so that the inventory is linked to Order Detail ID and not BOL.
I have added the OrderID field to my Iventory Table as well as the query and and re-defined by relationships so that
[OrderDetails] is now linked to [Inventory] by OrderID in a ONE to MANY relationship. I have also manually updated every record (about 75 of them)
However, when I try and add an inventory record now it is telling me “the record can not be changed or updated because a related record is required in tbl Order Details.

I can not figure out why. I need some guidance.

Thanks, James

[jbarrum]

By the grace of God I firgured out my problem. I needed to change my Master Link/Child Link relationship in the properties of my subform. Thanks to Rural Guy. I found his answer to antoher similar question?

Thanks, RG.

[RuralGuy]

Glad to be of assistance.

[jbarrum]

Glad to be of assistance.

Thanks!

I have been teaching myself Access, so although I am far from an expert I feel I have come a long ways is a short amount of time. I am grateful for people like your self and websites like this one.

Have a wonderful day and keep up the good work.


James

[mikel]

This is the same problem I am having. What was the solution? Do you know what other question helped you out?

[jbarrum]

hello Mikel,

I have just reviews your response. I am sorry that I did not see it earlier. However, if you are still in need of assistance let me know. I will gladly help.