How to create forms to deal with composite primary key

[gunitinug]

I made a blank form with Record Source set to OrderDetails.

I need to add two comboboxes.

First I set Control Source as UPC; Raw Source as SELECT DISTINCTROW Products.prodName, Products.UPC FROM Products; Bound Column as 2. This works. This allows me to set value of UPC when adding a new record in OrderDetails table using form element (in this case combobox).

Secondly I want to add custID and orderDate as part of new record in OrderDetails table. But I can't set more than one Control Source and in Orders table I've set a composite primary key (custID and orderDate). How to edit the same form to extract unique custID, orderDate from Orders table and save to OrderDetails along with UPC?

Thx.

I'm thinking adding orderID to Orders table, since every custID+orderDate entry in that table is already unique.

[John_G]

You need a 3-level form (but see last comment below). The main form is Customer, the Sub-form is customer-order, and the sub-sub-form is order details.

You would select a customer on the main form, then select or enter an order for the customer, and then enter the details for the order.

If you link the forms and subforms together correctly (i.e. the master - child fields), the key values wil propagate automatically.

The main form would link to the subform on the CustID field, and the subform would link to the sub-subform on CustID AND OrderDate.

I would strongly advise that you NOT use Order Date as part of the Composite Key - what happens if a customer places two orders on the same day (and that is almost certainly going to happen at some point)? If you generate a unique number for each order, then the orders table would need only the Order_Number as its PK, and CustomerID would only need OrderNumber and UPC in its PK.

Your form would also be reduced to two levels - Order and Order details (Customer would be a field in the orders table, and could be selected from a dropdown if there were not too many)

[June7]

I avoid compound PK whenever possible - only used 1 once. Generate an autonumber PK in Orders and save that as FK in OrderDetails.

[ArviLaanemets]

Whenever a new record is added on form, all controls except ones needed to create primary key are disabled. After all required data is entered, a button for saving the record and enabling disabled controls is made visible. (The button is made not visible again after the record is saved or after new record is canceled.). Whenever you attempt to leave the new record without saving it properly, the new record is canceled before moving to another one.

[June7]

I avoid compound PK whenever possible - only used 1 once. Generate an autonumber PK in Orders and save that as FK in OrderDetails.

And couldn't a customer place multiple orders for the same item in the same day - maybe not likely but not impossible.