A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Using Autonumber as PK, cascade update doesn't work?



 
 
Thread Tools Display Modes
  #11  
Old October 26th, 2008, 08:51 PM posted to microsoft.public.access.tablesdbdesign
Howard Maidman[_2_]
external usenet poster
 
Posts: 10
Default Using Autonumber as PK, cascade update doesn't work?

I too am using AutoNo. as primary key and cannot even get this to populate
itself in a subtable of a one-to-many even though I have successfully created
an accepted ref integrety with cascading updates etc!

Anybody got any ideas?

Howard

"KevinNGC" wrote:

Greetings all,

I've been searching and searching and trying different things and I finally
need to ask.

Is it possible to use an Autonumber PK to cascade update new records?

For example, I have three tables, let's call them Main, Sub1 and Sub2:

In Main I have an Autonumber PK field named MainID.
In Sub1 I have an Autonumber PK field named Sub1ID, and a Number field MainID.
In Sub2 I have an Autonumber PK field named Sub2ID, and a Number field MainID.

I have set up a one-to-many relationship between Main and Sub1, using the
MainID field. Ref. Integrity Enforced, with Cascade Updates checked.

I have set up a one-to-one relationship between Main and Sub2, using the
MainID field. Ref. Integrity Enforced, with Cascade Updates checked.

So when I enter a new record in the Main table, I want that MainID
Autonumber to cascade update into the MainID fields of Sub1 and Sub2. I
don't know if it's not working because it's a new record and Cascade Update
only works if you *change* an already existing record, or if it doesn't work
because it doesn't like my Autonumber.

If anyone has the key to this I would be eternally greatful. I've tried
everything I can think of on my own, and from browsing here, Access Help, etc.

Thanks very much!



  #12  
Old October 26th, 2008, 09:32 PM posted to microsoft.public.access.tablesdbdesign
Rick Brandt
external usenet poster
 
Posts: 4,354
Default Using Autonumber as PK, cascade update doesn't work?

Howard Maidman wrote:
I too am using AutoNo. as primary key and cannot even get this to
populate itself in a subtable of a one-to-many even though I have
successfully created an accepted ref integrety with cascading updates
etc!

Anybody got any ideas?


What do you mean by "populate itself"? Cascade update updates *existing*
children records when the key field of the parent is updated. Cascade
delete deletes *existing* children records when a parent record is deleted.
Nothing ever causes children record to automatically be created. Is that
what you are expecting?

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com


  #13  
Old October 26th, 2008, 10:15 PM posted to microsoft.public.access.tablesdbdesign
John W. Vinson
external usenet poster
 
Posts: 18,261
Default Using Autonumber as PK, cascade update doesn't work?

On Sun, 26 Oct 2008 12:47:00 -0700, Howard Maidman
wrote:



"KevinNGC" wrote:

Greetings all,

I've been searching and searching and trying different things and I finally
need to ask.

Is it possible to use an Autonumber PK to cascade update new records?


No. The question is meaningless. A *new record* does not EXIST so it cannot be
"updated", there's nothing there to update; and an Autonumber field cannot be
editied or updated once it's been set.

For example, I have three tables, let's call them Main, Sub1 and Sub2:

In Main I have an Autonumber PK field named MainID.
In Sub1 I have an Autonumber PK field named Sub1ID, and a Number field MainID.
In Sub2 I have an Autonumber PK field named Sub2ID, and a Number field MainID.

I have set up a one-to-many relationship between Main and Sub1, using the
MainID field. Ref. Integrity Enforced, with Cascade Updates checked.


There is no point to setting Cascade Updates. MainID as an autonumber cannot
ever be edited or changed, so there will never be any updates to be cascaded!
If MainID were a Long Integer instead, then CascadeUpdates would cause *any
existing values* of the MainID foreign key values in the subtables to change
to reflect the change in value of the MainID.

I have set up a one-to-one relationship between Main and Sub2, using the
MainID field. Ref. Integrity Enforced, with Cascade Updates checked.


Why? One to one relationships are VERY rare. Why not just incorporate the
fields in Sub2 into Main, if there will only ever be one value?


So when I enter a new record in the Main table, I want that MainID
Autonumber to cascade update into the MainID fields of Sub1 and Sub2. I
don't know if it's not working because it's a new record and Cascade Update
only works if you *change* an already existing record, or if it doesn't work
because it doesn't like my Autonumber.


Not needed. It's essentially NEVER either necessary or useful to create empty
"placeholder" records in a child table. Instead, just use a Subform, using
MainID as the master/child link. When (not before!!) you enter anything into
some *other* field on the subform, thereby dirtying the record, the MainID
will automatically fill in. You don't need to have the record pre-created.

--

John W. Vinson [MVP]
  #14  
Old October 27th, 2008, 03:18 AM posted to microsoft.public.access.tablesdbdesign
David W. Fenton
external usenet poster
 
Posts: 3,373
Default Using Autonumber as PK, cascade update doesn't work?

"Rick Brandt" wrote in
:

Howard Maidman wrote:
I too am using AutoNo. as primary key and cannot even get this to
populate itself in a subtable of a one-to-many even though I have
successfully created an accepted ref integrety with cascading
updates etc!

Anybody got any ideas?


What do you mean by "populate itself"? Cascade update updates
*existing* children records when the key field of the parent is
updated.


Except with an Autonumber, the parent value can never change
(Autonumbers are, by definition, not editable), so there's reason to
ever use CASCADE UPDATE on a relationship where the parent side is
an Autonumber PK.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
  #15  
Old November 1st, 2008, 06:13 PM posted to microsoft.public.access.tablesdbdesign
Howard Maidman[_2_]
external usenet poster
 
Posts: 10
Default Using Autonumber as PK, cascade update doesn't work?

Hi David,

Take it you mean 'never' use autonumber as a primary key? However, it is
the only unique field in my table so don't know what else to use. Must admit
I am new to Access and find it a real struggle so will probably stick to
Excel anyway.

Thanks

"David W. Fenton" wrote:

"Rick Brandt" wrote in
:

Howard Maidman wrote:
I too am using AutoNo. as primary key and cannot even get this to
populate itself in a subtable of a one-to-many even though I have
successfully created an accepted ref integrety with cascading
updates etc!

Anybody got any ideas?


What do you mean by "populate itself"? Cascade update updates
*existing* children records when the key field of the parent is
updated.


Except with an Autonumber, the parent value can never change
(Autonumbers are, by definition, not editable), so there's reason to
ever use CASCADE UPDATE on a relationship where the parent side is
an Autonumber PK.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

  #16  
Old November 2nd, 2008, 12:43 AM posted to microsoft.public.access.tablesdbdesign
Douglas J. Steele[_3_]
external usenet poster
 
Posts: 3,143
Default Using Autonumber as PK, cascade update doesn't work?

I believe David meant "there's NO reason to ever use ..."

David was pointing out that using CASCADE UPDATE only makes sense when the
value of the primary key changes. Since you cannot change the value of an
AutoNumber field, that means there's no point setting CASCADE UPDATE on any
relationships to the AutoNumber field.

I don't see David saying not to use AutoNumber fields though.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Howard Maidman" wrote in message
...
Hi David,

Take it you mean 'never' use autonumber as a primary key? However, it is
the only unique field in my table so don't know what else to use. Must
admit
I am new to Access and find it a real struggle so will probably stick to
Excel anyway.

Thanks

"David W. Fenton" wrote:

"Rick Brandt" wrote in
:

Howard Maidman wrote:
I too am using AutoNo. as primary key and cannot even get this to
populate itself in a subtable of a one-to-many even though I have
successfully created an accepted ref integrety with cascading
updates etc!

Anybody got any ideas?

What do you mean by "populate itself"? Cascade update updates
*existing* children records when the key field of the parent is
updated.


Except with an Autonumber, the parent value can never change
(Autonumbers are, by definition, not editable), so there's reason to
ever use CASCADE UPDATE on a relationship where the parent side is
an Autonumber PK.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/



  #17  
Old November 3rd, 2008, 01:21 AM posted to microsoft.public.access.tablesdbdesign
David W. Fenton
external usenet poster
 
Posts: 3,373
Default Using Autonumber as PK, cascade update doesn't work?

=?Utf-8?B?SG93YXJkIE1haWRtYW4=?=
wrote in
:

"David W. Fenton" wrote:

"Rick Brandt" wrote in
:

Howard Maidman wrote:
I too am using AutoNo. as primary key and cannot even get this
to populate itself in a subtable of a one-to-many even though
I have successfully created an accepted ref integrety with
cascading updates etc!

Anybody got any ideas?

What do you mean by "populate itself"? Cascade update updates
*existing* children records when the key field of the parent is
updated.


Except with an Autonumber, the parent value can never change
(Autonumbers are, by definition, not editable), so there's reason
to ever use CASCADE UPDATE on a relationship where the parent
side is an Autonumber PK.


Take it you mean 'never' use autonumber as a primary key?


No, not at all. Autonumber is almost always the best candidate for
PK.

What I should have written was:

there's **NO** reason to
ever use CASCADE UPDATE on a relationship where the parent side
is an Autonumber PK.


In other words, my comment was about when you should not use CASCADE
UPDATE, and not about when you should (or should not) use Autonumber
PKs.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
  #18  
Old November 3rd, 2008, 01:23 AM posted to microsoft.public.access.tablesdbdesign
David W. Fenton
external usenet poster
 
Posts: 3,373
Default Using Autonumber as PK, cascade update doesn't work?

"Douglas J. Steele" wrote in
:

I believe David meant "there's NO reason to ever use ..."


Doncha just hate it when your typist manages to completely reverse
your intended meaning? I need to fire that damned typist!

David was pointing out that using CASCADE UPDATE only makes sense
when the value of the primary key changes. Since you cannot change
the value of an AutoNumber field, that means there's no point
setting CASCADE UPDATE on any relationships to the AutoNumber
field.


That's correct. I've also followed up the post emphasizing what I
actually meant to write.

I don't see David saying not to use AutoNumber fields though.


Nope -- I'd never say that. I'm a huge believer in the use of
Autonumber surrogate keys, because real data often lacks the
information required to make natural PKs usable at all.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Please Help?? Auto Update Field in table A. Smart General Discussion 8 February 2nd, 2006 02:19 PM
how do I get autonumber function in access to work Katharine Jansen General Discussion 6 August 5th, 2005 03:36 PM
Cascade Update Related Fields Bug? Mike_iDbM Database Design 2 December 27th, 2004 10:37 PM
Autonumber Ally H. General Discussion 7 August 27th, 2004 04:51 PM
Update links does not work. Metallo Links and Linking 1 August 7th, 2004 09:01 AM


All times are GMT +1. The time now is 07:18 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.