duplicate record in forms

[Serogen]

I have created a database for a web order company. I have a very simple form
with 2 pages (tabs). Each page contains 1 "sub-form" (which is actually a
table). The first page contains Customer Information with a "Notes" sub-form
for notes on that particular customer. The second page contains that
particular customer's Order History.
I am having 2 problems. When I enter the customer's initial order, I am
able to enter it directly onto the form. However, when I enter subsequent
orders, I am forced to enter them into the sub-form.
Secondly, when I do enter more than 1 order for each customer, the form
automatically creates another entry for that customer. All the information
is correct, however, when I scroll through the form entries, I will have 1
entry for each order the customer has placed. However, if I go to the
Customer Information table, there remains only 1 entry per customer (which
obviously is what I want). Also, the sub-table (Order History) is accurate.
I am convinced this is a very simple problem, however I just can not wrap my
head around it to figure it out.
Thank you in advance!

 

[Wayne-I-M]

Hi

I think (may be wrong) that this is a problem with the way you have set up
the database.

I think you have a customer information table that also contains so order
information. If this is this case you need to re-do the table structure.

Your customer information table should have "only" etails about the customer
not about thier orders.

So....
CustomerID
Address1stLine
Address2ndLine
Address3rdLine
Telephone
etc
etc
etc

Item table should be
ItemID
ItemDiscription
ItemCost
etc
etc
etc

Link these 2 tables with an other table

OrderID
CustomerID
ItemID
OrderDate
etc
etc
etc

Now create a query based on these tables and then a form (use a query rather
than the tables so you can do tax, deliery costs, etc calculations).

Base your form on this query

With custormers as the main form
Order details as the 1st tab
Linked to Items on the 2nd tab - this is for returns, post date, etc

good luck

 

[Serogen]

Wayne,
I thank you for your help. I have not responded sooner, because I have been
trying everything I can imagine to do what you have suggested. I'm just not
getting this. I do not actually have order information in my customer table.
The only duplicating information I have is the CustomerID. I have this in
both my Customers table and Orders table (and they are linked). However, I
do believe you are correct in that there is something wrong with my database
design. I discovered I did not "enforce referential integrity". Because I
CAN'T!! uh-oh

Also, in an attempt to implement your suggestions I have created a simple
database with only 3 tables (each table containing 3 fields). I discovered
part of my problem is that I had all my information on the tabs (nothing on
the "main form"). However, even with this extremely simplified db I still
can not get this to work.
I have a Customer table with CustomerID, FirstName, and LastName
Item table: ItemID, ItemDescription
Order table: OrderID, CustomerID, ItemID, OrderDate

I have linked the 3 tables and again, the information shows correctly in the
tables. Then, I created a query based on these tables, and a form based on
the query. There is obviously something I'm missing. No matter what I do, I
can not figure this out! Could it be the way I'm creating the command button
to create a new record?