Related Record Required in Customers Table: PLEASE HELP

[JGalt90]

Hi, I am trying to build a simple DB for a bread delivery route. I want to build a form from multiple tables, so when we get a call we enter all relevant data: [Customer Name Order Dates Quantity Product(s)] It is telling me related records are required in supplier and customer. I don't know what I am doing wrong. Is the design flawed? Thank you for any help, I greatly appreciate it. -J

[June7]

I am blocked from downloading image. If you attached directly to post, I could then view it.

A form can enter/edit data for only one table. You probably need to use form/subform arrangement. But why would there be a direct link between supplier and customer?

[ssanfu]

I would change the "OrderDetails" table.

OrderDetails
--------------
OrderDetailsID (PK) (Autonumber)
OrderID (FK) (Long)
ProductID (FK) (Long
Price
Quantity

Set an index on OrderID & ProductID to prevent duplicate entries.

[JGalt90]

Thank you,

I have attached the relationships.

In stupid terms I just want "Oh Jim wants 2 6 inch rolls on sunday"

[keviny04]

It is telling me related records are required in supplier and customer.

It's likely because you are entering an order with a CustomerID that has yet to exist in the Customers table, and you are entering order details with a ProductID that has yet to exist in the Products table (and/or whose SupplierID has yet to exist in the Suppliers table). The referential integrity set in your relationships is causing this. Either you remove the R.I., or set up SEPARATE data entry forms for Suppliers, Products, and Customers. Before you enter an order, you have to go to those forms and ensure the suppliers, products, and customers that you are going to put in your order already exist.

If you are good with form design and VBA, you can set up the order entry form so that if you try to enter a nonexistent supplier/product/customer, it will open up the necessary data entry form for you to enter it, and take you back to the order form.

[JGalt90]

I don't understand why it isn't cascading or automatically updating the information

[keviny04]

I don't understand why it isn't cascading or automatically updating the information

You can't use cascade if a key record doesn't already EXIST in the parent table. Cascade can only UPDATE EXISTING records; it can't enter nonexistent records for you. In a one-to-many relationship, the parent table is on the "one" side, and the child is on the "many" side. The parent can have what the child doesn't have, but the child MUST have what the parent already has. In your case, that's why your orders (the child) must contain customers that are already in the Customers table (parent).

That's why you need to enter new suppliers, new products, and new customers before you can enter orders. When you enter orders, you can only enter EXISTING suppliers, products, and customers. Unless, as I said, you are good with form design and VBA so you can create a way to process nonexistent records.

[June7]

It doesn't cascade or automatically update because there is no record to edit. Records must already exist in customers and products for selection in order record.

You show only one form in the image. There should be several. Review comments in post 2. https://support.office.com/en-us/art...a-1e74d6f5f06b

The customer and product tables records should not be editable from the order details form.