C
CuriousMark
Sorry if this is a duplicate. I thought I posted it yesterday but couldn't
find it today...
I need help figuring out the best way to design a form for data entry in the
"Order Entry" style, adding data to three related tables. For clarity I have
eliminated several fields. Here are the three tables, plus the fourth that is
a reference table:
tblCustomers: CustID (pk), CustName
tblOrders: OrderID (pk), OrderDate, CustID (fk)
tblOrderProd: OrderProdID (pk), OrderID (fk), ProdID (fk)
tblProducts: ProdID (pk), ProdName
Each Customer is associated with one or more Orders, and each order has a
date, a Customer and one or more Products. The current design uses a main
form for the Date and Customer, and a subform for the Products. The main form
is bound to this query:
SELECT tblOrders.OrderDate, tblCustmers.CustName
FROM tblCustomers INNER JOIN tblOrders
ON tblCustomers.CustID = tblOrders.CustID
The subform is bound to this query:
SELECT tblOrderProd.ProdID, tblProducts.ProdName, tblOrderProd.OrderID
FROM tblProducts INNER JOIN tblOrderProd
ON tblProducts.ProdID = tblOrderProd.ProdID
This works for entering orders, and the subform works to add the Product ID
and display the Product Name. But it does not allow me to use and existing
customer...this design means that each order must add a customer.
I want to program the form so that as I enter the Customer information it
will search to see if the Customer exists, ask me if I want to use that
customer or add a new one. When posting bits of this problem here before the
advice I get is to use an unbound form, but I can't quite figure out the
global picture. Should both the main and subform be unbound, and then use
Append queries to add data to the three tables? If so, if it is a new
Customer, how do I get the CustID (an Autonumber field) to use when appending
to the tblOrder table. And how do I get the OrderID (also an Autonumber
field) to add to the tblOrderProd table? Then, how do I progress through each
entry from the subform when appending to the tblOrderProd table? Do I store
the ProdIDs in a Recordset or in a temporary table? Or do I keep the forms
bound and use another technique?
Thanks very much.
find it today...
I need help figuring out the best way to design a form for data entry in the
"Order Entry" style, adding data to three related tables. For clarity I have
eliminated several fields. Here are the three tables, plus the fourth that is
a reference table:
tblCustomers: CustID (pk), CustName
tblOrders: OrderID (pk), OrderDate, CustID (fk)
tblOrderProd: OrderProdID (pk), OrderID (fk), ProdID (fk)
tblProducts: ProdID (pk), ProdName
Each Customer is associated with one or more Orders, and each order has a
date, a Customer and one or more Products. The current design uses a main
form for the Date and Customer, and a subform for the Products. The main form
is bound to this query:
SELECT tblOrders.OrderDate, tblCustmers.CustName
FROM tblCustomers INNER JOIN tblOrders
ON tblCustomers.CustID = tblOrders.CustID
The subform is bound to this query:
SELECT tblOrderProd.ProdID, tblProducts.ProdName, tblOrderProd.OrderID
FROM tblProducts INNER JOIN tblOrderProd
ON tblProducts.ProdID = tblOrderProd.ProdID
This works for entering orders, and the subform works to add the Product ID
and display the Product Name. But it does not allow me to use and existing
customer...this design means that each order must add a customer.
I want to program the form so that as I enter the Customer information it
will search to see if the Customer exists, ask me if I want to use that
customer or add a new one. When posting bits of this problem here before the
advice I get is to use an unbound form, but I can't quite figure out the
global picture. Should both the main and subform be unbound, and then use
Append queries to add data to the three tables? If so, if it is a new
Customer, how do I get the CustID (an Autonumber field) to use when appending
to the tblOrder table. And how do I get the OrderID (also an Autonumber
field) to add to the tblOrderProd table? Then, how do I progress through each
entry from the subform when appending to the tblOrderProd table? Do I store
the ProdIDs in a Recordset or in a temporary table? Or do I keep the forms
bound and use another technique?
Thanks very much.