I have a Customer
table, an Order
table, an Orderline
table and a Product
table. All of them have an Autonumber
field as their primary key, and Orderline
has a foreign key reference to Order ID
on Order
table:
ORDER
-----
Order ID - Autonumber
Customer ID - Number
...
ORDERLINE
---------
OrderLine ID - Autonumber
Order ID - FK to Order
Product ID - FK to Product
Quantity
PRODUCT
-------
Product ID - Autonumber
Product details...
I have a form where I can choose a customer, and then a list of records from the Orderline
table, and a query which I reference from this sub-form which lists the Order ID
, Orderline ID
, Product ID
, Product details
...
I have 2 problems.
All the orders appear, and I only want the ones associated with this order, (which should be none when the form first loads).
When I enter a
Product ID
that I want to add to a new order, I am expecting a newOrder ID
to appear, (Autoincremented) AND a new Orderline ID, (Autoincremented) and the details of the product that I have selected, corresponding to the
Product ID` I have entered, but instead I get this error message:
The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again
The thing is, the tables should create me unique keys when I try to create the new record, and when I go into each table directly and enter a new record, the autonumber does work and does create a unique key - it is just when it is trying to create both the Order ID
and the Orderline ID
at the same time that it seems to be failing.
I should say, I have spent days on this, searched countless search engines, watched whole series of YouTube videos on creating Order forms but to no avail. Anyone who understands Access I am sure would be able to help me, as I would be able to help anyone in a similar circumstance in a matter of minutes if this was a problem in SQL.
See Question&Answers more detail:os