Primary Key to Foreign Key

[David M C]

I have an order table with a one to many relationship to an order details
table. The order table contains general order information, whilst the order
details table contains the individual items in the order.

The OrderID field in the order table is the primary key, whilst OrderID in
the order details table is the foreign key. When adding an order using a form
I designed, how do you get the Primary key OrderID to propogate to the
foreign key OrderID. Currently the foreign key OrderID remains blank.

Thanks

Dave

 

[David M C]

A bit more information. Entering data in the Order table works fine (opening
a specific OrderID with the "+" allows you to add to the Order Details
table), but I cannpt get the same to work in a form.

Ideas?

Dave

 

[TedMi]

On the order form, create a sub-form to hold order details. Set the child
link field property to the FK of the detail table. Set the master link field
to the PK of the main order table.

 

[Jeff Boyce]

David

TedMi's response holds the clue...

You are describing working directly in the tables ... stop! Step away from
the keyboard...

In Access, tables store data, forms display it (and queries retrieve it).

Create a query that retrieves the Order info... and create a form for
displaying that.

Now create a query that retrieves OrderDetail info... and create a form for
displaying that.

Open the Order form in design mode and add a subform ... you guessed it!
Use the OrderDetail form as a subform. In the main form (Order form), for
the subform control (not the form, the control that holds it), set the
Parent/Child properties so that the subform "knows" which Parent row (Order
ID) to use.

 

[TC]

Make sure that the PK of the Order Details table, is the /two/ fields:
OrderID and XXX, where XXX is the detail line number, detail product
code, or whatever other field, that uniquely identifies each detail
record within a given order.

IOW, the details table has a "composite" primary key. This is a PK
comprising more than one field. Not several independent PKs - one PK
comprising several fields.

HTH,
TC

 

[David M C]

Thanks Ted and Jeff.

I'd already done most of what you describe except the Parent/Child properties.

 

[David M C]

My forms currently work as expected for existing records but cannot create
new Order Details records (Order records are created with blank Order
Details).

My Order details subform is based on a query that inludes OrderID.

When setting the Parent/Child properties, both display OrderID. Should you
use SQL statements such as "OrderID FROM OrderDetails"?

 

[David M C]

Got it working. Thanks for all the help,

Dave

My forms currently work as expected for existing records but cannot create
new Order Details records (Order records are created with blank Order
Details).

My Order details subform is based on a query that inludes OrderID.

When setting the Parent/Child properties, both display OrderID. Should you
use SQL statements such as "OrderID FROM OrderDetails"?

 

[John Vinson]

I have an order table with a one to many relationship to an order details
table. The order table contains general order information, whilst the order
details table contains the individual items in the order.

The OrderID field in the order table is the primary key, whilst OrderID in
the order details table is the foreign key. When adding an order using a form
I designed, how do you get the Primary key OrderID to propogate to the
foreign key OrderID. Currently the foreign key OrderID remains blank.

Typically this is done by basing the main form on the Order table, and
a Subform on the OrderDetails table, using the OrderID as the
master/child link field. See the Orders application in the Northwind
sample database.

If you have designed the form in a different manner (a single form
based on a query joining the two tables perhaps...?) you'll need to
explain how... and perhaps why. Subforms make it easy.

John W. Vinson[MVP]