H E L P - Problem with 'related' table - no new record is created - why ?

[Will]

I've lost a half a day on this and maybe someone will be kind enough to
grind through my question and give me some help.

My 5 tables are...

1 Order Table
idOrder
Customer
Order Date

2 Order_Item Table
idOrder
idProduct
idShipment
Qty

3 Produts Table
idProduct
Product Name

4 Shipment Table
idShipment
idShipper
Ship Date
Container Number

5 Shipper Table
idShipper
Shipping Company

- the Orders_Form is built on an Orders_Query which is built on table 1
above
- the Order_Item_Query has all fields of tables 2, 3,4 & 5 above
- the Order_Item_Subform is built on the above query and contains all the
fields for which we must enter data...
(Qty, Proeduct Name, Ship Date, Container Number, & Shipping Company)

- When I create a new order and add a new "item" and enter each field in
order everything works fine
- BUT... when I add a new "item" to the Order_Item_Subform... I start the
new item by entering a Qty... but then if I click outside the new record...
say to copy the Ship Date from the previous item... I can no longer enter
Ship Date or Container Number or Shipping Company into the new item
record... but I can add a product... ???

I can see that no new Shipment Record is being created in table-4... and
thus no idShipment value is being entered into table-2

But why? And How can I fix this?

thanks for any help on this.

 

[German Saer]

I think there is a functionality problem here. Do you select different ways
of shipment per roduct in the same order? Is this correct? Or should it be
one shipment per order? If so, idShipment should be on Order instead
Order_Item. Additionally, when adding a shipment per order (per item in
order) you basically select among the list of pre-entered or pre-established
shipments. The way you have it structured now looks like when working on
Orders you are not working on shipments at all? Let us know how the process
should be and we help you achieve this.

--
___________
Thanks,

German Saer
Orlando, FL

 

[tina]

well, i don't think i'd base the subform on a query containing tables 2 - 5.
assuming that each item can have only one shipping record, you might be able
to base the subform on the Order_Item table and the Shipment table, if you
create a LEFT JOIN between Order_Item.idShipment and Shipment.idShipment. (i
can't be sure without testing it, which i didn't do.)
you *don't* need the Products table in the subform query. instead create a
combo box in the subform to enter the Order_Item.idProduct value, and base
the combo box on the Products table.
ditto the above, re the Shipper table. use a combo box based on the Shipper
table, to enter a value in the Shipment.idShipper field.

hth