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]