Asked By CuriousMark
08-Feb-10 06:04 PM
I have been working on this database for several years, learning as I go.
However I have been stuck on the problem of the best way to design a form for
data entry into three tables. I now give up and put the whole thing on the
Forum in the hope that someone can help me with the design. For simplicity, I
will illustrate using the "Order Entry" style database, and limit the fields
to only those necessary.
There are four tables with the following fields:
tblCustomers - CustomerID (pk), CustomerName
tblOrders - OrderID (pk), OrderDate, CustomerID (fk)
tblOrderProd - OrderProdID (pk), OrderID (fk), ProductID (fk)
tblProducts - ProductID (pk), ProductName
One customer is associated with one or more orders, and one order can have
one or more products. The user should be able to create a new order by
specifying the date and the customer, and then one or more products.
I have a main form with subform to do this. The main form is bound to a
query that joins the tblOrders and tblCustomers tables, and the subform is
bound to a query that joins the tblOrderProd and tblProducts tables. The
subform is linked to the main form through the OrderID field.
Main form query:
SELECT tblOrders.OrderDate, tblCustomers.CustomerName
FROM tblCustomers INNER JOIN tblOrders
ON tblCustomers.CustomerID = tblOrders.CustomerID
Subform query:
SELECT tblOrderProd.ProductID, tblProducts.ProductName, tblOrderProd.OrderID
FROM tblProducts INNER JOIN tblOrderProd
ON tblProducts.ProductID = tblOrderProd.ProductID
This setup works great for adding records, and when I enter a ProductID in
the subform it will show the ProductName but the problem is that this design
means a new customer is added for each new order: I cannot figure out how to
search based on the entry for an existing customer, use it if it exists, or
add it if it is new.
My hunch is that I should unbind both forms, using Append queries to enter
the data in the related three fields. If so, then how do I find out the new
OrderID to use to append to tblOrderProd? And how do I find out the
CustomerID to use to append to the tblOrders if it is a new Customer? Then
how do I progress through each ProductID entered in the subform when
appending to the tblOrderProd table?
Thanks very much.