Re: Cascade Update using only queries

Jim

That doesn't sound like any forms I've built.

If you have "parent" and "child" tables, the way Access offers to (easily)
handle the relationships is a Main form/Subform.

The main form gets the parent table record, the subform gets the (related)
child table records.

Since, in Access, it all starts with the data, it would probably help folks
here offer more specific suggestions if you'd provide a more specific
description of your table structure. Examples help.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Jim Murray" <JimMurray@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:F01A1DED-4C0E-4F59-8BDA-BE32FD9E6319@xxxxxxxxxxxxxxxx
? Are you suggesting that I set up a form, populate it from a select query
that links all of the tables, list all of the foreign keys in text boxes
and
set up a vb function to replace the values of those foreign keys with a
new
foreign key?
? You've done this and it works?
Jim

"Jeff Boyce" wrote:

Access tables store data, Access forms (and reports) display data (and
offer
a rich event environment).

Consider stepping away from the tables and using forms instead.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Jim Murray" <JimMurray@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:D9A825CF-58A6-4875-9EAE-4F1E2F3D32F1@xxxxxxxxxxxxxxxx
Yes, I'm working directly in the tables and all of my foreign keys are
long
integers.
Jim

"Jeff Boyce" wrote:
Are you working directly in the tables?
It is quite common to have a parent-child relationship between tables.
As
it is quite common to use a main form/subform construction to help
with
data
entry.

It sounds like your "child" tables already have a foreign key to point
back
to the parent table's primary key.

By the way, you didn't explicitly state this, so I need to ask ... you
are
NOT using an Autonumber data type as your "foreign key" field, right?
To
make the parent-child relationship work, you'd need to have a primary
key
in
the child table and a foreign key field of type LongInt to point back
to
the
parent record that "owns" it.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Jim Murray" <JimMurray@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:E4DB4A39-D47C-4A50-8AD0-4FE74B52FA2B@xxxxxxxxxxxxxxxx
I did not setup my Access / SQL to allow cascade updates. I'm trying
to
accomplish this by query. My main table, tblPersons, has an
autonumber
primary key. The target tables (11 of them) use this as a foreign
key.
I
can
update through the query if each table has an entry with the
PersonsID.
The
query does not work if one table does not have an entry matching
that
PersonsID. I could write a complex VB script checking each table for
an
entry
and dropping those that don't from the SQL script but this seems
extreme.
Is
there a simple way of doing this?
Sample of Query (only 3 tables)
UPDATE ((tblPersons LEFT JOIN tblActions ON tblPersons.PersonsID =
tblActions.PersonsID) LEFT JOIN tblAddresses ON tblPersons.PersonsID
=
tblAddresses.PersonsID) LEFT JOIN tblAlias ON tblPersons.PersonsID =
tblAlias.PersonsID SET tblActions.PersonsID =
[Forms]![adminForm]![cboCombineToPersons], tblAddresses.PersonsID =
[Forms]![adminForm]![cboCombineToPersons], tblAlias.PersonsID =
[Forms]![adminForm]![cboCombineToPersons];









.

Relevant Pages

  • Re: Cascade Update using only queries
    ... "Jeff Boyce" wrote: ... NOT using an Autonumber data type as your "foreign key" field, ... update through the query if each table has an entry with the PersonsID. ...
    (microsoft.public.access.queries)
  • Re: Foreign key question
    ... then the children have two foreign key fields relating ... But each parent will also have only one first child, ... Eg you have a table of planes and a table of propeller types. ...
    (microsoft.public.fox.programmer.exchange)
  • Re: deleting a row potentially referenced by many rows in another table
    ... you would use ON DELETE CASCADE on a foreign key in a child table to ... FOREIGN KEY REFERENCES Parent ... row in the child table referencing that (parent) row is deleted (N:1 ...
    (comp.databases.theory)
  • Re: Cascading updates and NULL
    ... foreign key references between the parent and the child and use cascading ... a parent and a child. ... >> DataCol into the child table, cascading updates work fine. ...
    (microsoft.public.sqlserver.programming)
  • Re: Data Normalization
    ... CustomerID foreign key. ... of the database whether an SSN would be a good candidate for a primary key. ... applicable that relates that given record to it's parent. ... Create the relationship between the child table and my new parent ...
    (microsoft.public.access.queries)