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
  #1  
Old March 14th, 2006, 05:51 PM posted to microsoft.public.access.tablesdbdesign
external usenet poster
 
Posts: n/a
Default 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  
Old March 14th, 2006, 06:48 PM posted to microsoft.public.access.tablesdbdesign
external usenet poster
 
Posts: n/a
Default 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  
Old March 14th, 2006, 06:52 PM posted to microsoft.public.access.tablesdbdesign
external usenet poster
 
Posts: n/a
Default 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  
Old March 14th, 2006, 08:28 PM posted to microsoft.public.access.tablesdbdesign
external usenet poster
 
Posts: n/a
Default 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  
Old March 14th, 2006, 08:48 PM posted to microsoft.public.access.tablesdbdesign
external usenet poster
 
Posts: n/a
Default 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  
Old March 14th, 2006, 08:53 PM posted to microsoft.public.access.tablesdbdesign
external usenet poster
 
Posts: n/a
Default 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  
Old March 15th, 2006, 03:36 AM posted to microsoft.public.access.tablesdbdesign
external usenet poster
 
Posts: n/a
Default 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  
Old March 15th, 2006, 05:24 AM posted to microsoft.public.access.tablesdbdesign
external usenet poster
 
Posts: n/a
Default 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  
Old March 15th, 2006, 01:22 PM posted to microsoft.public.access.tablesdbdesign
external usenet poster
 
Posts: n/a
Default 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  
Old October 26th, 2008, 08:47 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?



"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

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 11:44 AM.


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