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

[KevinNGC]

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!

 

[KevinNGC]

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?

 

[mnature]

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).

 

[BruceM]

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.

 

[Duncan Bachen]

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.

 

[John Vinson]

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]

 

[KevinNGC]

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]

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]

 

[KevinNGC]

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

 

[Howard Maidman]

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!

 

[Howard Maidman]

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

 

[Rick Brandt]

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?

 

[John W. Vinson]

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.

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.

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?


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.

 

[David W. Fenton]

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.

 

[Howard Maidman]

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

 

[Douglas J. Steele]

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.

 

[David W. Fenton]

=?Utf-8?B?SG93YXJkIE1haWRtYW4=?=

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:

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]

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.