How do I create mutiple orders in a subform linked to Customer ID?

  • Thread starter Thread starter Emma
  • Start date Start date
E

Emma

I have a customer database, with contact details as the main form, and orders
as the subform (on a tab). The customer ID is the primary key, and link
between the two forms, with the order ID on the order page as an auto number.

The problem I am having is that access won't let me add more than one order
to a customers contact details! I want to be able to open a customers
details, and see on the order tab the orders they have placed. When I try to
add a second order with the same customer ID, Access says it won't let me
duplicate the index/primary key.

I have allowed duplicates on the customer ID on the order form, what am I
doing wrong? I'm guessing I need the customer ID on the order form,
otherwise it won't link to the customer details. I had originally added the
Order form as a table at the end of the customer details form, but this
wasn't great, as it was difficult to view; however it did work!

Help!
 
Did you create a one-to-many relationship between customer and order tables
using CustomerID primary key field in customer table and foreign key field in
the order table?
 
Emma said:
I have a customer database, with contact details as the main form, and
orders
as the subform (on a tab). The customer ID is the primary key, and link
between the two forms, with the order ID on the order page as an auto
number.

The problem I am having is that access won't let me add more than one
order
to a customers contact details! I want to be able to open a customers
details, and see on the order tab the orders they have placed. When I try
to
add a second order with the same customer ID, Access says it won't let me
duplicate the index/primary key.
Click to expand...

Order ID should NOT be the primary key of the Order Details table. A primary
key is, by definition, unique, so there can only be one record in the order
details table with that Order ID.

The Order ID should be a non-unique foreign key in the Order Details table,
which should have its own separate OrderDetailID primary key field.

John Vinson/MVP



__________ Information from ESET NOD32 Antivirus, version of virus signature database 4702 (20091219) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com
 
Back
Top