I am about to begin designing a new database, but...

R

Robert

I need some advice. This is a database that I have been struggling with for
a long, long time. Here is the concept:

I want a main form in the database that the user inputs order data into.

The form has a subform in it. The main form being for header info, the
subform for order details.

My question is with the tables. There should only be one set of header info
per order, but there can be an unlimited number of line items on the subform
that goes with this header info. I cannot figure out how to set up the
tables.

I have tried creating one table for all of the header info, and one for all
of the order details. I don't know how to tie both of these tables together
under one data entry form. Does anyone have any experience withthis? I'm
desperate. Thanks.
 
R

Rick Brandt

Robert said:
I need some advice. This is a database that I have been struggling
with for a long, long time. Here is the concept:

I want a main form in the database that the user inputs order data
into.

The form has a subform in it. The main form being for header info,
the subform for order details.

My question is with the tables. There should only be one set of
header info per order, but there can be an unlimited number of line
items on the subform that goes with this header info. I cannot
figure out how to set up the tables.

I have tried creating one table for all of the header info, and one
for all of the order details. I don't know how to tie both of these
tables together under one data entry form. Does anyone have any
experience withthis? I'm desperate. Thanks.
Click to expand...

VERY simplified example...

Table Orders
Fields: OrderID, CustomerID, OrderDate
Primary Key: OrderID

Table OrderItems
Fields: OrderID, ItemID, Qty, Price
Primary Key: OrderID, ItemID

The subform control would have the MasterLink/ChildLink properties both set
to "OrderID".

As you navigate in the main orders form you will automatically see the items
on that order in the subform. Also when you enter new items in the subform
the OrderID will automatically be inherited from the main record. It is the
MasterLink and ChildLink properties that trigger these behaviors.
 
R

Rick B

Have you tried looking at the sample database (Northwind) that comes with
Access. It already does everything you mention. Why reinvent the wheel?
Take it and use it as a starting point. Modify it to meet your needs.
 
R

Robert

Rick Brandt, your example works beautifully. If I may, let me ask one more
question. Let's say I insert a line number column in the details table, and
I want the first line of each "order details section" to begin with 1, then
the next line 2, etc. Do I simply put autonumber for this number column?
Thanks.
 
Top