Order Entry type form to add to three tables

Thanks Jeff.
Agreed that the form should be based only on the tblOrders table, with a
combo box for Customer. But I couldn't figure out how to add new customer if
one didn't exist.

"Jeff Boyce" wrote:

These newsgroups are "staffed" by (mostly) volunteers. The Code of Conduct
precludes soliciting paid work.

Before you sign any contracts, you might want to consider whether you want
to hire someone who doesn't feel it necessary to follow the rules.

Why do you have a main form with Customer and Order joined? If the main
form is your Order form, you can use a combobox to list possible customers
and select which one it is, to add to your Order record (customerID).

If you'd rather have Customer as your main form, your Order is your subform
and the OrderDetail is the subsubform.

Using subforms and the Parent/Child joins between main and sub (and between
sub and subsub) will allow Access to properly record the "parent" to which a
particular Order Detail or Order belongs

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"CuriousMark" wrote in message
...
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 can't 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.



.