order processing

  • Thread starter Thread starter doob
  • Start date Start date
D

doob

i want to create a form for order processing where i select a product or
enter a product number then add this product to an order ... there can
be multiple products on each order to add ...

not sure where to start ... so far ive got a table of products and table
of orders and a form for order processing ... id think ive got to have
another table so that i can have multiple items added for an order
number or would i use a subform within the form for this?
 
TblProducts
ProductID
ProductName
Price

TblOrder
OrderID
OrderDate
CustomerID
etc

TblOrderLineItem
OrderLineItemID
OrderID
ProductID
Quantity
Etc

Make a form/subform from TblOrder and TblOrderLineItem and use a combobox to add
products to the subform.
 
Take a look at the Northwind.mdb sample database that ships with Access.
While it may not be the "be-all and end-all" for an order processing
database, you may get some ideas if you look at how they handle line items.

hth,
 
i want to create a form for order processing where i select a product or
enter a product number then add this product to an order ... there can
be multiple products on each order to add ...

not sure where to start ... so far ive got a table of products and table
of orders and a form for order processing ... id think ive got to have
another table so that i can have multiple items added for an order
number or would i use a subform within the form for this?
Click to expand...

Excellent ideas. Take a look at the Orders form which comes in the
Northwind sample database - it has an OrderDetails table listing the
items in an order, and uses a Subform to enter values into that table.
 
I'd think ive got to have
another table so that i can have multiple items added for an order
number or would i use a subform within the form for this?
Click to expand...

Yes. Many products : one order. You need a tblOrder-Product which will list
all products on all orders. This table is maintained via a subformm that is
probably contained wiithin a tblCustomer. It would let you select available
products from tblProducts and save the ProductID (PK) into tblOrder-Product
(FK) along with unit price, quantity ordered, etc. Choose each product
using a combo box on the order entry subform that looks up the ProductID
from tblProducts.

You have (entity tables):

Many customers : many orders
Many orders : many products.

To resolve each many : many relationship you need a relationship table:

tblCustomer-Order
tblOrder-Product

Once you understand the mechanics you'll be able to add as many layers to
the schema as you want.
 
I'd think ive got to have
Yes. Many products : one order. You need a tblOrder-Product which will
list
all products on all orders. This table is maintained via a subform that is
probably contained within a (form not table =>) frmCustomer. It would let
you select available
products from tblProducts and save the ProductID (PK) into tblOrder-Product
(FK) along with unit price, quantity ordered, etc. Choose each product
using a combo box on the order entry subform that looks up the ProductID
from tblProducts.

You have (entity tables):

Many customers : many orders
Many orders : many products.

To resolve each many : many relationship you need a relationship table:

tblCustomer-Order
tblOrder-Product

Once you understand the mechanics you'll be able to add as many layers to
the schema as you want, and expand the database to be as inclusive as
possible.
 
TblProducts
ProductID
ProductName
Price

TblOrder
OrderID
OrderDate
CustomerID
etc

TblOrderLineItem
OrderLineItemID
OrderID
ProductID
Quantity
Etc

Make a form/subform from TblOrder and TblOrderLineItem and use a combobox to add
products to the subform.
Click to expand...

ok .. thanks for that ... i have the above tables and would like a box
on the order form that i could type in the product id and as im typing
it in have this lookup the description of the product im typing in and
show it .... once satisfied its the correct item then click an add
button which would add this to my sub form as an item to order

also, how would i increment the OrderLineItemID automatically for each
subform on each seperate order so that it starts from 1 again

in this database there is only 1 supplier and 1 customer, we are the
customer ordering these items from this single supplier - there are no
costs involved as its all based on a contract to supply these items so
what i want to get to is adding these items to the sub form then once
satisfied the order is ready click a process button which will email
this order direct to the supplier ... havent the faintest idea how to
get to that point but thats where im hoping to be heading ....
 
<< havent the faintest idea how to get to that point but thats where im hoping
to be heading ....

I suggested these tables to you. If you would like my help getting to the point
you want to be at, contact me at the email address below.
 
<< havent the faintest idea how to get to that point but thats where im hoping
to be heading ....

I suggested these tables to you. If you would like my help getting to the point
you want to be at, contact me at the email address below.


--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com
Click to expand...

yes, and i appreciate your help and it would be much easier to pay
someone for their time to help me but im not quite after the easy
solution ....

its the getting started pain im hoping to get through using the
newsgroup ... with each suggestion im learning just that little bit more
and moving forward.

unfortunately access is like walking uphill in sand ... a step forward,
half a step back, a side step to regain balance and on we go ;)

in 6 months time i'll be wondering what all the fuss was about but for
now i'll stick with it .. but thanks for the offer.
 
Take a look at the Northwind Sample database and the Solutions database that
came packaged with your MS Office. Goto C:\, Program Files, Microsoft Office,
Office, Samples. A lot of what you need can be found there. Also go to
www.mvps.org/access.
 
Back
Top