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]