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. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Please Help?? Auto Update Field in table | A. Smart | General Discussion | 8 | February 2nd, 2006 01: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 09: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 |