Procedure for updating multiple tables

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi gang,

I have a form with which I want to update multiple related tables, using
Access 2000.

For the sake of the example, the "main" table, on the right side of all the
right joins, is Task; all other tables relate to that one with the attendant
referential integrity.

Within the scope of the form, I can update all fields attached to that
table, but not any fields attached to other tables, which makes sense
considering the ref integ.

I'm fine with coding a procedure in VBA, say at the After Update event, to
query the database and update it if the text entered in the field is not
found, for every field. What I'm wondering is if there's an intrinsic
functionality to the form, or the ref. integ, I'm missing that would make
this easier.

Hope that made sense and thanks a bunch for your help!

Paul
 
Hi gang,

I have a form with which I want to update multiple related tables, using
Access 2000.

For the sake of the example, the "main" table, on the right side of all the
right joins, is Task; all other tables relate to that one with the attendant
referential integrity.

Within the scope of the form, I can update all fields attached to that
table, but not any fields attached to other tables, which makes sense
considering the ref integ.

I'm fine with coding a procedure in VBA, say at the After Update event, to
query the database and update it if the text entered in the field is not
found, for every field. What I'm wondering is if there's an intrinsic
functionality to the form, or the ref. integ, I'm missing that would make
this easier.
Click to expand...

Typically one would use a different Subform for each related table.

If you're storing the *same* data in the main table and also in the
related tables - DON'T! The whole *point* of relational databases is
to avoid redundancy, thereby avoiding redundancy. The related tables
should only contain the Foreign Key field linking them to the main
table, and other fields pertinent to the entity represented by that
table; if there is no data to enter in the "child" table, then simply
don't create a record! It is NOT necessary nor appropriate to create
empty "placeholder" records.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Hey John,

Thanks for your response. LOVE the topically relevant humor: "to avoid
redundancy, thereby avoiding redundancy".

My tables are set up correctly, but I think I'm just being a hardhead about
coding the form(s). I've seen examples that use the subforms, and I'm sure
that's the way to go. I guess I was just hoping that I could buck convection
and conjure up something different.

Thanks again,
Paul
 
Back
Top