By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,846 Members | 1,646 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,846 IT Pros & Developers. It's quick & easy.

help! cannot add repeatly the same records to subform

P: n/a
Hi everybody,

I can not add into the "order_detail" subform a product more than once. The
subform just refuses accepting any repeated items. The subform's source is a
"expanded_order_detail" query which is based on "order_detail" table and the
"product" table. The "order_detail" table has two primary keys: "OrderID"
and "ProductID". A typical Northwind type db.

My clients demand that the "order_detail" subform be able to accept repeated
items.

Should I cancel the double primary keys in table "order_detail" in order to
fullfil the demands of my clients?

It drives me nuts....for in the MS Access help it says that table without
primary key can not be used to set up relationship, what should I do?

Thank you very much,

Paul
Nov 13 '05 #1
Share this Question
Share on Google+
7 Replies


P: n/a
The 2 primary keys combined makes a compound primary key where there can
only be one combination of the two per table. i.e. OrderID = 3 and ProdID =
4 this combination can only happen once with the way you have it set up.

Solution. Remove the 2 primary keys on the OrderID and ProdID and add
another field OrderDetailID (Autonumber) and make this the primary key.

Jeff
"Paul T. Rong" <et***@hotmail.com> wrote in message
news:vM*********************@news.chello.at...
Hi everybody,

I can not add into the "order_detail" subform a product more than once. The subform just refuses accepting any repeated items. The subform's source is a "expanded_order_detail" query which is based on "order_detail" table and the "product" table. The "order_detail" table has two primary keys: "OrderID"
and "ProductID". A typical Northwind type db.

My clients demand that the "order_detail" subform be able to accept repeated items.

Should I cancel the double primary keys in table "order_detail" in order to fullfil the demands of my clients?

It drives me nuts....for in the MS Access help it says that table without
primary key can not be used to set up relationship, what should I do?

Thank you very much,

Paul

Nov 13 '05 #2

P: n/a
Jeff,

Thank you very much, your idea is very good. I'll use that. Thanks.

But, strangely, when I removed the primary keys from both "OrderID" and
"ProductID" of the "order_detail" table. The one to many relationship
between "Product"-"order_detail" and between "order"-"order_detail" remains
unchanged. And the "order_detail" subform started to accept repeated
records.

Why? HOw to explain the MS Access help comment on "table without primary key
can not be established relationship"?

Paul


"Jeff Smith" <No***@Not.This.Address> 写入消息新闻
:cj**********@lust.ihug.co.nz...
The 2 primary keys combined makes a compound primary key where there can
only be one combination of the two per table. i.e. OrderID = 3 and ProdID = 4 this combination can only happen once with the way you have it set up.

Solution. Remove the 2 primary keys on the OrderID and ProdID and add
another field OrderDetailID (Autonumber) and make this the primary key.

Jeff
"Paul T. Rong" <et***@hotmail.com> wrote in message
news:vM*********************@news.chello.at...
Hi everybody,

I can not add into the "order_detail" subform a product more than once. The
subform just refuses accepting any repeated items. The subform's source is a
"expanded_order_detail" query which is based on "order_detail" table and

the
"product" table. The "order_detail" table has two primary keys:

"OrderID" and "ProductID". A typical Northwind type db.

My clients demand that the "order_detail" subform be able to accept

repeated
items.

Should I cancel the double primary keys in table "order_detail" in order

to
fullfil the demands of my clients?

It drives me nuts....for in the MS Access help it says that table without primary key can not be used to set up relationship, what should I do?

Thank you very much,

Paul


Nov 13 '05 #3

P: n/a
The changed order_detail table's structure should be
OrderDetailID (PK) Autonumber
OrderID (FK)
ProductID (FK)
Quantity
Price
Any Other Fields

The relationships should be
tblProducts![ProductID] 1>M tblOrderDetails![ProductID]
tblOrders![OrderID] 1>M tblOrderDetails![OrderID]

Jeff
"Paul T. Rong" <et***@hotmail.com> wrote in message
news:Qv********************@news.chello.at...
Jeff,

Thank you very much, your idea is very good. I'll use that. Thanks.

But, strangely, when I removed the primary keys from both "OrderID" and
"ProductID" of the "order_detail" table. The one to many relationship
between "Product"-"order_detail" and between "order"-"order_detail" remains unchanged. And the "order_detail" subform started to accept repeated
records.

Why? HOw to explain the MS Access help comment on "table without primary key can not be established relationship"?

Paul


"Jeff Smith" <No***@Not.This.Address> 写入消息新闻
:cj**********@lust.ihug.co.nz...
The 2 primary keys combined makes a compound primary key where there can
only be one combination of the two per table. i.e. OrderID = 3 and ProdID
=
4 this combination can only happen once with the way you have it set up.

Solution. Remove the 2 primary keys on the OrderID and ProdID and add
another field OrderDetailID (Autonumber) and make this the primary key.

Jeff
"Paul T. Rong" <et***@hotmail.com> wrote in message
news:vM*********************@news.chello.at...
Hi everybody,

I can not add into the "order_detail" subform a product more than
once. The
subform just refuses accepting any repeated items. The subform's
source
is
a
"expanded_order_detail" query which is based on "order_detail" table

and the
"product" table. The "order_detail" table has two primary keys: "OrderID" and "ProductID". A typical Northwind type db.

My clients demand that the "order_detail" subform be able to accept

repeated
items.

Should I cancel the double primary keys in table "order_detail" in
order to
fullfil the demands of my clients?

It drives me nuts....for in the MS Access help it says that table

without primary key can not be used to set up relationship, what should I do?

Thank you very much,

Paul



Nov 13 '05 #4

P: n/a
Jeff,

It works like charm. Thank you very very much.

But what is FK?
Does it mean the field is required or the Null is allowed?
I am using Access in versions other than English, so I am not very familiar
with the English term?

Paul

"Jeff Smith" <No***@Not.This.Address> 写入消息新闻
:cj**********@lust.ihug.co.nz...
The changed order_detail table's structure should be
OrderDetailID (PK) Autonumber
OrderID (FK)
ProductID (FK)
Quantity
Price
Any Other Fields

The relationships should be
tblProducts![ProductID] 1>M tblOrderDetails![ProductID]
tblOrders![OrderID] 1>M tblOrderDetails![OrderID]

Jeff
"Paul T. Rong" <et***@hotmail.com> wrote in message
news:Qv********************@news.chello.at...
Jeff,

Thank you very much, your idea is very good. I'll use that. Thanks.

But, strangely, when I removed the primary keys from both "OrderID" and
"ProductID" of the "order_detail" table. The one to many relationship
between "Product"-"order_detail" and between "order"-"order_detail"

remains
unchanged. And the "order_detail" subform started to accept repeated
records.

Why? HOw to explain the MS Access help comment on "table without primary

key
can not be established relationship"?

Paul


"Jeff Smith" <No***@Not.This.Address> 写入消息新闻
:cj**********@lust.ihug.co.nz...
The 2 primary keys combined makes a compound primary key where there can only be one combination of the two per table. i.e. OrderID = 3 and ProdID
=
4 this combination can only happen once with the way you have it set up.
Solution. Remove the 2 primary keys on the OrderID and ProdID and add
another field OrderDetailID (Autonumber) and make this the primary key.
Jeff
"Paul T. Rong" <et***@hotmail.com> wrote in message
news:vM*********************@news.chello.at...
> Hi everybody,
>
> I can not add into the "order_detail" subform a product more than

once. The
> subform just refuses accepting any repeated items. The subform's source
is
a
> "expanded_order_detail" query which is based on "order_detail" table

and the
> "product" table. The "order_detail" table has two primary keys:

"OrderID"
> and "ProductID". A typical Northwind type db.
>
> My clients demand that the "order_detail" subform be able to accept
repeated
> items.
>
> Should I cancel the double primary keys in table "order_detail" in order to
> fullfil the demands of my clients?
>
> It drives me nuts....for in the MS Access help it says that table

without
> primary key can not be used to set up relationship, what should I do? >
> Thank you very much,
>
> Paul
>
>



Nov 13 '05 #5

P: n/a
"Paul T. Rong" <et***@hotmail.com> wrote in message news:<Qv********************@news.chello.at>...
Jeff,

Thank you very much, your idea is very good. I'll use that. Thanks.

But, strangely, when I removed the primary keys from both "OrderID" and
"ProductID" of the "order_detail" table. The one to many relationship
between "Product"-"order_detail" and between "order"-"order_detail" remains
unchanged. And the "order_detail" subform started to accept repeated
records.

Why? HOw to explain the MS Access help comment on "table without primary key
can not be established relationship"?

Paul

Okay, in simple terms and paraphrasing,

Relationships go "from" a table, "to" another table. The column(s)
that you are going from, in the "from" table does not have to be a
Primary Key (or one part of a primary key), but the column(s) that you
are going to, in the "to" table MUST be the primary key.

In your example, the order_detail table is your "from" table, and the
product table is your "to" table.
Nov 13 '05 #6

P: n/a
Damien,

So it means in this case the "product" table which is the "one" side is a
"to" table, and the "order_detail" table which is the "many" side is a
"from" table?

Then the relationship direction is from a "many" side table to a "one" side
table?

Thanks, but I am totally confused.

Paul


"Damien" <Da*******************@hotmail.com>
??????:ac**************************@posting.google .com...
"Paul T. Rong" <et***@hotmail.com> wrote in message

news:<Qv********************@news.chello.at>...
Jeff,

Thank you very much, your idea is very good. I'll use that. Thanks.

But, strangely, when I removed the primary keys from both "OrderID" and
"ProductID" of the "order_detail" table. The one to many relationship
between "Product"-"order_detail" and between "order"-"order_detail" remains unchanged. And the "order_detail" subform started to accept repeated
records.

Why? HOw to explain the MS Access help comment on "table without primary key can not be established relationship"?

Paul

Okay, in simple terms and paraphrasing,

Relationships go "from" a table, "to" another table. The column(s)
that you are going from, in the "from" table does not have to be a
Primary Key (or one part of a primary key), but the column(s) that you
are going to, in the "to" table MUST be the primary key.

In your example, the order_detail table is your "from" table, and the
product table is your "to" table.

Nov 13 '05 #7

P: n/a
FK = Foreign Key. It's the field which stores the linking value in the child
table from the parent table.

"Paul T. Rong" <et***@hotmail.com> wrote in message
news:T%*********************@news.chello.at...
Jeff,

It works like charm. Thank you very very much.

But what is FK?
Does it mean the field is required or the Null is allowed?
I am using Access in versions other than English, so I am not very familiar with the English term?

Paul

Nov 13 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.