help! cannot add repeatly the same records to subform

[Paul T. Rong]

Hi everybody,

I can not add into the "order_deta il" 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_deta il" table and the
"product" table. The "order_deta il" table has two primary keys: "OrderID"
and "ProductID" . A typical Northwind type db.

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

Should I cancel the double primary keys in table "order_deta il" 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

[Jeff Smith]

Re: help! cannot add repeatly the same records to subform

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" <etjsk@hotmail. com> wrote in message
news:vMD8d.3503 52$vG5.211871@n ews.chello.at.. .[color=blue]
> Hi everybody,
>
> I can not add into the "order_deta il" subform a product more than once.[/color]
The[color=blue]
> subform just refuses accepting any repeated items. The subform's source is[/color]
a[color=blue]
> "expanded_order _detail" query which is based on "order_deta il" table and[/color]
the[color=blue]
> "product" table. The "order_deta il" table has two primary keys: "OrderID"
> and "ProductID" . A typical Northwind type db.
>
> My clients demand that the "order_deta il" subform be able to accept[/color]
repeated[color=blue]
> items.
>
> Should I cancel the double primary keys in table "order_deta il" in order[/color]
to[color=blue]
> 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
>
>[/color]

[Paul T. Rong]

Re: help! cannot add repeatly the same records to subform

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_deta il" table. The one to many relationship
between "Product"-"order_deta il" and between "order"-"order_deta il" remains
unchanged. And the "order_deta il" 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" <NoWay@Not.This .Address> дÈëÏûÏ¢ÐÂÎÅ
:cjv245$rvt$1@l ust.ihug.co.nz. ..[color=blue]
> 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[/color]
=[color=blue]
> 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" <etjsk@hotmail. com> wrote in message
> news:vMD8d.3503 52$vG5.211871@n ews.chello.at.. .[color=green]
> > Hi everybody,
> >
> > I can not add into the "order_deta il" subform a product more than once.[/color]
> The[color=green]
> > subform just refuses accepting any repeated items. The subform's source[/color][/color]
is[color=blue]
> a[color=green]
> > "expanded_order _detail" query which is based on "order_deta il" table and[/color]
> the[color=green]
> > "product" table. The "order_deta il" table has two primary keys:[/color][/color]
"OrderID"[color=blue][color=green]
> > and "ProductID" . A typical Northwind type db.
> >
> > My clients demand that the "order_deta il" subform be able to accept[/color]
> repeated[color=green]
> > items.
> >
> > Should I cancel the double primary keys in table "order_deta il" in order[/color]
> to[color=green]
> > fullfil the demands of my clients?
> >
> > It drives me nuts....for in the MS Access help it says that table[/color][/color]
without[color=blue][color=green]
> > primary key can not be used to set up relationship, what should I do?
> >
> > Thank you very much,
> >
> > Paul
> >
> >[/color]
>
>[/color]

[Jeff Smith]

Re: help! cannot add repeatly the same records to subform

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" <etjsk@hotmail. com> wrote in message
news:QvE8d.3503 91$vG5.92491@ne ws.chello.at...[color=blue]
> 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_deta il" table. The one to many relationship
> between "Product"-"order_deta il" and between "order"-"order_deta il"[/color]
remains[color=blue]
> unchanged. And the "order_deta il" subform started to accept repeated
> records.
>
> Why? HOw to explain the MS Access help comment on "table without primary[/color]
key[color=blue]
> can not be established relationship"?
>
> Paul
>
>
>
>
> "Jeff Smith" <NoWay@Not.This .Address> дÈëÏûÏ¢ÐÂÎÅ
> :cjv245$rvt$1@l ust.ihug.co.nz. ..[color=green]
> > 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[/color][/color]
ProdID[color=blue]
> =[color=green]
> > 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" <etjsk@hotmail. com> wrote in message
> > news:vMD8d.3503 52$vG5.211871@n ews.chello.at.. .[color=darkred]
> > > Hi everybody,
> > >
> > > I can not add into the "order_deta il" subform a product more than[/color][/color][/color]
once.[color=blue][color=green]
> > The[color=darkred]
> > > subform just refuses accepting any repeated items. The subform's[/color][/color][/color]
source[color=blue]
> is[color=green]
> > a[color=darkred]
> > > "expanded_order _detail" query which is based on "order_deta il" table[/color][/color][/color]
and[color=blue][color=green]
> > the[color=darkred]
> > > "product" table. The "order_deta il" table has two primary keys:[/color][/color]
> "OrderID"[color=green][color=darkred]
> > > and "ProductID" . A typical Northwind type db.
> > >
> > > My clients demand that the "order_deta il" subform be able to accept[/color]
> > repeated[color=darkred]
> > > items.
> > >
> > > Should I cancel the double primary keys in table "order_deta il" in[/color][/color][/color]
order[color=blue][color=green]
> > to[color=darkred]
> > > fullfil the demands of my clients?
> > >
> > > It drives me nuts....for in the MS Access help it says that table[/color][/color]
> without[color=green][color=darkred]
> > > primary key can not be used to set up relationship, what should I do?
> > >
> > > Thank you very much,
> > >
> > > Paul
> > >
> > >[/color]
> >
> >[/color]
>
>[/color]

[Paul T. Rong]

Re: help! cannot add repeatly the same records to subform

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" <NoWay@Not.This .Address> дÈëÏûÏ¢ÐÂÎÅ
:cjv8mk$1cr$1@l ust.ihug.co.nz. ..[color=blue]
> 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" <etjsk@hotmail. com> wrote in message
> news:QvE8d.3503 91$vG5.92491@ne ws.chello.at...[color=green]
> > 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_deta il" table. The one to many relationship
> > between "Product"-"order_deta il" and between "order"-"order_deta il"[/color]
> remains[color=green]
> > unchanged. And the "order_deta il" subform started to accept repeated
> > records.
> >
> > Why? HOw to explain the MS Access help comment on "table without primary[/color]
> key[color=green]
> > can not be established relationship"?
> >
> > Paul
> >
> >
> >
> >
> > "Jeff Smith" <NoWay@Not.This .Address> дÈëÏûÏ¢ÐÂÎÅ
> > :cjv245$rvt$1@l ust.ihug.co.nz. ..[color=darkred]
> > > The 2 primary keys combined makes a compound primary key where there[/color][/color][/color]
can[color=blue][color=green][color=darkred]
> > > only be one combination of the two per table. i.e. OrderID = 3 and[/color][/color]
> ProdID[color=green]
> > =[color=darkred]
> > > 4 this combination can only happen once with the way you have it set[/color][/color][/color]
up.[color=blue][color=green][color=darkred]
> > >
> > > Solution. Remove the 2 primary keys on the OrderID and ProdID and add
> > > another field OrderDetailID (Autonumber) and make this the primary[/color][/color][/color]
key.[color=blue][color=green][color=darkred]
> > >
> > > Jeff
> > >
> > >
> > > "Paul T. Rong" <etjsk@hotmail. com> wrote in message
> > > news:vMD8d.3503 52$vG5.211871@n ews.chello.at.. .
> > > > Hi everybody,
> > > >
> > > > I can not add into the "order_deta il" subform a product more than[/color][/color]
> once.[color=green][color=darkred]
> > > The
> > > > subform just refuses accepting any repeated items. The subform's[/color][/color]
> source[color=green]
> > is[color=darkred]
> > > a
> > > > "expanded_order _detail" query which is based on "order_deta il" table[/color][/color]
> and[color=green][color=darkred]
> > > the
> > > > "product" table. The "order_deta il" table has two primary keys:[/color]
> > "OrderID"[color=darkred]
> > > > and "ProductID" . A typical Northwind type db.
> > > >
> > > > My clients demand that the "order_deta il" subform be able to accept
> > > repeated
> > > > items.
> > > >
> > > > Should I cancel the double primary keys in table "order_deta il" in[/color][/color]
> order[color=green][color=darkred]
> > > to
> > > > fullfil the demands of my clients?
> > > >
> > > > It drives me nuts....for in the MS Access help it says that table[/color]
> > without[color=darkred]
> > > > primary key can not be used to set up relationship, what should I[/color][/color][/color]
do?[color=blue][color=green][color=darkred]
> > > >
> > > > Thank you very much,
> > > >
> > > > Paul
> > > >
> > > >
> > >
> > >[/color]
> >
> >[/color]
>
>[/color]

[Damien]

Re: help! cannot add repeatly the same records to subform

"Paul T. Rong" <etjsk@hotmail. com> wrote in message news:<QvE8d.350 391$vG5.92491@n ews.chello.at>. ..[color=blue]
> 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_deta il" table. The one to many relationship
> between "Product"-"order_deta il" and between "order"-"order_deta il" remains
> unchanged. And the "order_deta il" 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
>
>[/color]
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.

[Paul T. Rong]

Re: help! cannot add repeatly the same records to subform

Damien,

So it means in this case the "product" table which is the "one" side is a
"to" table, and the "order_deta il" 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" <Damien_The_Unb eliever@hotmail .com>
??????:ac70a9d9 .0410052338.1d7 1258c@posting.g oogle.com...[color=blue]
> "Paul T. Rong" <etjsk@hotmail. com> wrote in message[/color]
news:<QvE8d.350 391$vG5.92491@n ews.chello.at>. ..[color=blue][color=green]
> > 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_deta il" table. The one to many relationship
> > between "Product"-"order_deta il" and between "order"-"order_deta il"[/color][/color]
remains[color=blue][color=green]
> > unchanged. And the "order_deta il" subform started to accept repeated
> > records.
> >
> > Why? HOw to explain the MS Access help comment on "table without primary[/color][/color]
key[color=blue][color=green]
> > can not be established relationship"?
> >
> > Paul
> >
> >[/color]
> 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.[/color]

[Jeff Smith]

Re: help! cannot add repeatly the same records to subform

FK = Foreign Key. It's the field which stores the linking value in the child
table from the parent table.

"Paul T. Rong" <etjsk@hotmail. com> wrote in message
news:T%M8d.3508 87$vG5.250272@n ews.chello.at.. .[color=blue]
> 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[/color]
familiar[color=blue]
> with the English term?
>
> Paul
>[/color]