Creating a simple sales order entry application

[Guest]

Hi, I am using a wet and cold holiday weekend to learn about Access. I want
to create a simple sales order entry function.

I have set up 2 tables Orders and Order Details. The Orders table holds
order number, customer, due date, etc and the Order Details table is to hold
the line items (product, quantity, price, etc). So far, so good.

I have created 2 forms and made the Order Details form a subform of the
Order Header form. So far, so good.

I have 2 questions arising from this.
1. How do I get the cursor to tab from the last field of the header form to
the first field of the details subform? When I get to the last field of the
header form I get an error message saying "You cannot add or change a record
because a related record is required in table "Order Details".

I want to get into the subform to create that related record!

2. Is it possible, and if so where do I get info on how, to enter (and show)
many detail lines for the one header. This is a normal sales order situation,
customer stuff followed by 5 or 6 lines of products on the order.

I have two old (Windows 95) MS Access tutorial manuals, beginners and
advanced but can't find the answers there. I am using MS Access 2000.

Any help or pointers to where I can get the help would be appreciated.

BD

 

[Dirk Goldgar]

See my comments inline.

Hi, I am using a wet and cold holiday weekend to learn about Access.
I want to create a simple sales order entry function.

I have set up 2 tables Orders and Order Details. The Orders table
holds order number, customer, due date, etc and the Order Details
table is to hold the line items (product, quantity, price, etc). So
far, so good.

At some point you may well decide you want a Customers table, too, so
that you don't have the same customer details stored over and over again
in your Orders table.

I have created 2 forms and made the Order Details form a subform of
the Order Header form. So far, so good.

I have 2 questions arising from this.
1. How do I get the cursor to tab from the last field of the header
form to the first field of the details subform? When I get to the
last field of the header form I get an error message saying "You
cannot add or change a record because a related record is required in
table "Order Details".

I want to get into the subform to create that related record!

That sounds to me like you have the relationship between the Orders and
Order Details tables reversed. It should be a one-to-many relationship,
with Orders on the "one" side" and Order Details on the "many" side. As
such, there should be no problem creating an Orders record without
Details (as you have to create the "one" record before you can create
any "many" records).

Presumably OrderNumber is the primary key of the Orders table. It must
also be present in the Order Details table, but must not be the primary
key of that table.

2. Is it possible, and if so where do I get info on how, to enter
(and show) many detail lines for the one header. This is a normal
sales order situation, customer stuff followed by 5 or 6 lines of
products on the order.

Normally you would design the subform as a continuous form, so that it
automatically shows multiple detail records for each Order header.

 

[Guest]

Thank you for answering Dirk.
You are right about the customer table, but first things first. I will add
the customer table later.

You are also right about the 1 to many relationship. I have deleted the
relationship and tried to set it up again but it always sets up 1 line to
many orders. How can I reverse that?

Order number is in both tables and is the primary key in the orders table.
Line number is the primary key of the Order Details table.

You said "Normally you would design the subform as a continuous form, so
that it
automatically shows multiple detail records for each Order header." - How do
you mean. My Order Details record contains Line number, Product, Quantity and
Price.

Without speicifying a set number of order lines within each record, how do I
make it a continmuos form?

Cheers,
BigDaddy

 

[Dirk Goldgar]

Thank you for answering Dirk.
You are right about the customer table, but first things first. I
will add the customer table later.

You are also right about the 1 to many relationship. I have deleted
the relationship and tried to set it up again but it always sets up 1
line to many orders. How can I reverse that?

Order number is in both tables and is the primary key in the orders
table. Line number is the primary key of the Order Details table.

Make sure the OrderNumber field in the Order Details table doesn't have
its Indexed property set to "Indexed (No Duplicates)". It should either
be set to "None" (which works because a suitable index will be created
when you create the relationship), or to "Yes (Duplicates OK)".

You said "Normally you would design the subform as a continuous form,
so that it
automatically shows multiple detail records for each Order header." -
How do you mean. My Order Details record contains Line number,
Product, Quantity and Price.

Without speicifying a set number of order lines within each record,
how do I make it a continmuos form?

To do this with the existing form you created to serve as the subform:

(1) Open the subform in design view.
(2) Open the property sheet of that form.
(3) Go to the Format tab on the property sheet.
(4) Change the Default View property to "Continuous Forms".
(5) Save the form.

You can open the form now to see how it looks. You may want to go back
to design view to move controls around and resize the form's detail
section, and you may or may not want to set the form's Navigation
Buttons property (also on the Format tab of its property sheet) to No.