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 |
#1
|
|||
|
|||
Using Autonumber as PK, cascade update doesn't work?
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! |
#2
|
|||
|
|||
Using Autonumber as PK, cascade update doesn't work?
I was thinking (which can be scary sometimes) and figured that if this
doesn't work then an After Update append query would be the answer? "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! |
#3
|
|||
|
|||
Using Autonumber as PK, cascade update doesn't work?
This is from Microsoft Office Access 2003 Inside Out:
If you build a table with a primary key that is Text or Number (perhaps a ProductID field that could change at some point in the future), it might be a good idea to select Cascade Update Related Fields. This option requests that Access automatically update any foreign key values in the child table (the many table in a one-to-many relationship) if you change a primary key value in a parent table (the one table in a one-to-many relationship). |
#4
|
|||
|
|||
Using Autonumber as PK, cascade update doesn't work?
Are you entering a record directly into the table? If so, how are you
entering the related record(s)? You really shoud be using forms, if you are not already. Typically you would have a form frmMain based on tblMain, and subforms (fsubSub1 and fsubSub2) based on tblSub1 and tblSub2. If you add to the main form and the subforms a text box bound to MainID (in the record source table for that particular form or subform) you will have an easier time seeing what is going on. Because of the relationships between tblMain and tblSub1/tblSub2, every record you create in the subform will have the same value in MainID as does frmMain. Below the surface, all of those records created through the subforms will have the same value in MainID as did the parent record (the one entered by way of frmMain). This is not Cascade Update. Cascade Update occurs if you need to change the PK in tblMain. It will update the FK field (MainID) in the subforms, assuring that relationships that existed before the change still exist after the change. If MainID in tblMain is 999, MainID in related records in tblSub1/tblSub2 will also be 999. If you change 999 to 1234 in tblMain, 999 will also change to 1234 in tblSub1/tblSub2. "KevinNGC" wrote in message news 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! |
#5
|
|||
|
|||
Using Autonumber as PK, cascade update doesn't work?
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! You're right that Cascade only affects existing records. When you create your new record on the Main Table, no record is created on the Sub1 or Sub2 tables. It's your job to create these records through your forms. If you use a subform to enter the data for Sub1, and you've got your Link Child and Link Master properties setup, then the MainID will be filled in automagically when you start creating a new record on Sub1. If you aren't using a subform, then you'll have to explicitly set the value of MainID yourself = to the MainID on the Main form. It sounds like you might be trying to enter the new records directly in table view. -- -D Duncan Bachen Director of I.T., Ole Hansen and Sons, Inc. |
#6
|
|||
|
|||
Using Autonumber as PK, cascade update doesn't work?
On Tue, 14 Mar 2006 08:51:26 -0800, 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? You seem to be misunderstanding how Cascade Update is designed to work: when you EDIT the value of a Primary Key field in a main table, cascade update transfers that change to any related tables. Since you cannot *EDIT* the value stored in an Autonumber field, it is pointless to emplement cascade updates on an Autonumber field. 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. Why a one to one? Such relationships are *very* rare; if you're not using Subclassing or Table-Based Field Level Security, I wonder if you might not be better off just putting Sub2's fields into Main. 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. Again... that's not how it works. Relationships don't automagically create new records in the sub tables; they PREVENT the addition of incorrect records, and (with cascade updates or deletes) prevent the inadvertant creation of orphan records by either deleting them or editing them to match. I gather that you want a new record to instantly appear in Sub1 and in Sub2 as soon as you create a record in Main. It won't! Access won't create a record until there is something to put into it. Normally one would manage this by using a Form based on Main, with two subforms - one based on Sub1 and the other based on Sub2, using MainID as the Master/Child Link Field. You'ld enter data into the mainform (creating a new MainID with your first keystroke, since it's an autonumber); then you would start typing data into the first subform, creating a new Sub1ID and inheriting the mainform's MainID. Then you'll do the same on the second subform. It is NOT necessary to first create empty "placeholder" records. John W. Vinson[MVP] |
#7
|
|||
|
|||
Using Autonumber as PK, cascade update doesn't work?
Thank you all for your input. I was just unclear about exactly how the
Cascade Updates worked. Yes, I am using forms by the way! One thing for you John, about a one to one relationship. Other things I've read lead me to believe that a one to one is one way to implement some additional security. I'm not sure about what you mentioned, Subclassing and Table-Based Field Level security yet, still learning as I go here. Work and school and learning Access on the side is insane! Thanks once again everyone, you all do a really great job here. "John Vinson" wrote: On Tue, 14 Mar 2006 08:51:26 -0800, 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? You seem to be misunderstanding how Cascade Update is designed to work: when you EDIT the value of a Primary Key field in a main table, cascade update transfers that change to any related tables. Since you cannot *EDIT* the value stored in an Autonumber field, it is pointless to emplement cascade updates on an Autonumber field. 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. Why a one to one? Such relationships are *very* rare; if you're not using Subclassing or Table-Based Field Level Security, I wonder if you might not be better off just putting Sub2's fields into Main. 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. Again... that's not how it works. Relationships don't automagically create new records in the sub tables; they PREVENT the addition of incorrect records, and (with cascade updates or deletes) prevent the inadvertant creation of orphan records by either deleting them or editing them to match. I gather that you want a new record to instantly appear in Sub1 and in Sub2 as soon as you create a record in Main. It won't! Access won't create a record until there is something to put into it. Normally one would manage this by using a Form based on Main, with two subforms - one based on Sub1 and the other based on Sub2, using MainID as the Master/Child Link Field. You'ld enter data into the mainform (creating a new MainID with your first keystroke, since it's an autonumber); then you would start typing data into the first subform, creating a new Sub1ID and inheriting the mainform's MainID. Then you'll do the same on the second subform. It is NOT necessary to first create empty "placeholder" records. John W. Vinson[MVP] |
#8
|
|||
|
|||
Using Autonumber as PK, cascade update doesn't work?
On Tue, 14 Mar 2006 18:36:27 -0800, KevinNGC
wrote: One thing for you John, about a one to one relationship. Other things I've read lead me to believe that a one to one is one way to implement some additional security. I'm not sure about what you mentioned, Subclassing and Table-Based Field Level security yet, still learning as I go here. We may be talking about the same thing. If you have a table with some fields which are public knowledge (say an employee's LastName, FirstName, DepartmentID) and other fields which aren't (CurrentSalary, PerformanceRating), you can put the latter into a separate table with a higher level of security. John W. Vinson[MVP] |
#9
|
|||
|
|||
Using Autonumber as PK, cascade update doesn't work?
Oh, yes, that's exactly what I am doing. I guess I haven't quite picked up
on the "official" names for stuff yet. Thank you once again! Enjoy the rest of the week! kevin "John Vinson" wrote: On Tue, 14 Mar 2006 18:36:27 -0800, KevinNGC wrote: One thing for you John, about a one to one relationship. Other things I've read lead me to believe that a one to one is one way to implement some additional security. I'm not sure about what you mentioned, Subclassing and Table-Based Field Level security yet, still learning as I go here. We may be talking about the same thing. If you have a table with some fields which are public knowledge (say an employee's LastName, FirstName, DepartmentID) and other fields which aren't (CurrentSalary, PerformanceRating), you can put the latter into a separate table with a higher level of security. John W. Vinson[MVP] |
#10
|
|||
|
|||
Using Autonumber as PK, cascade update doesn't work?
"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! |
|
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 |