Update a field in several tables from one form

[pete]

. Reply (E-mail) Forward (E-mail)

Subject: Re: Complete table fields from a form
From: "MacDermott" <[email protected]> Sent:
6/26/2004 6:41:55 AM




Have you tried enforcing referential integrity, with a
cascade update?
- Turtle

Hi

Posted this in getting started but could not find a
solution so trying here!
The problem

I have 5 tables joined together with a one to one
relationship on an index number field which is in each
table and is the primary key for the tables.
I have made a form which enters information into each field of
each table when I enter a new record. However the index
number field on the form enters the number in the field

of the first table only as this was linked when the form
was created .The index number is the same in the

other 4 tables. Is there a way to automatically insert

the new record index number into the other 4 tables?

Get desperate here!

gratefully tried this suggestion:
"Have you tried enforcing referential integrity, with a
cascade update?"
- Turtle
But didn't work I even tried it after varying the table
relationships and removing the primary key from the tables
2-5.
The info is split into tables because in one table there
would be over 100 fields!

 

[John Vinson]

The info is split into tables because in one table there
would be over 100 fields!

Pete, could you explain what valid Entity has over 100 atomic,
non-repeating, non-interdependent Attributes?

I've needed as many as 60 fields for a table... twice in twenty years.
I very strongly suspect that your tables could be normalized into
tall-thin form.

 

[Guest]

Sorry I can't understand Klingon !
can anyone else suggest an answer please?
Thanks

 

[John Vinson]

Sorry I can't understand Klingon !
can anyone else suggest an answer please?

I am suggesting that you redesign your tables.

A basic principle of relational databases is that "fields are
expensive, records are cheap" - tables should be tall and thin, rather
than wide and flat. If you have 200 or more fields in a table, *THEN
YOUR TABLE STRUCTURE IS WRONG* - you're almost certainly storing a one
to many relationship in each record.

It would be possible to write VBA code to create an (empty,
placeholder) record in each of the related tables, but you would be
much better off properly normalizing your tables.

Could you describe the content and structure of these tables? Perhaps
someone can suggest a normalized design which would be easier to work
with!

 

[Guest]

Thank you for taking time to elaborate I appreciate it.
None of the tables have over 100 fields there are five
tables . No matter how few fields there are the index
number in Table 1 needs to be copied to that field which
exists in all the other tables; all the other fields are
filled in direct from the form.

I think the big problem here is I am from Planet stupid
and can't see the concept!
This is what comes of trying impress the boss.
Anyway thanks again
am suggesting that you redesign your tables.

 

[John Vinson]

Thank you for taking time to elaborate I appreciate it.
None of the tables have over 100 fields there are five
tables . No matter how few fields there are the index
number in Table 1 needs to be copied to that field which
exists in all the other tables; all the other fields are
filled in direct from the form.

I think the big problem here is I am from Planet stupid
and can't see the concept!
This is what comes of trying impress the boss.
Anyway thanks again

If you would - as requested - describe the contents of these tables,
we'll be glad to try to help. Again... the reason you're having the
problem has nothing to do with "planet stupid"; it has to do with the
fact that you are (I would guess) relatively knowledgable in Excel but
not in relational design, and you're trying to apply spreadsheet logic
to a database. It's a very common mistake!

If you are committed to these (VERY unusual, and in my opinion
unnecessary and inappropriate) one to one relationships, I can make
two suggestions:

1. Use a Form for your primary table with four Subforms for the linked
tables. If you use the ID as the master/child link field, it will
automatically fill in the ID. The child table will not be filled in
until you add data to some field other than the ID, but that's
actually preferable - you really SHOULDN'T have a "placeholder" record
containing nothing but the ID, since it's useless to have a record
with nothing in it; it will risk having records left blank.

2. If you insist on doing it wrong, I'll hold my nose and write you
some code. If that's what you want, post the names of your five tables
and the name of the ID field. You'ld put code in the AfterInsert event
of the main form to append a record to the additional tables.

 

[Pete]

Thank you are very patient and kind.
I think the code would work better for me as it would get
me out of jail quicker.
the id field is:
Assetnumber
Table names are:
Register - this holds main details of the other records
Work to do
Condition Notes
Items replaced
Items added
The tables use text fields and drop down boxes from look
up lists.
All the fields in the tables are different except for the
Assetnumber which is primary key in each table. One form
with pages enters details into the tables.
Oh dear, I can hear thousands of readers across the globe
laughing loudly .
Thanks for your help and I promise to try and do it
properly next time.
Pete
If anyone knows of a good book to learn this stuff that
would be nice in case the boss needs more of this stuff

If you could the code would be nice but I would need to

 

[John Vinson]

Thank you are very patient and kind.
I think the code would work better for me as it would get
me out of jail quicker.

Well, I fear it would get you out of the frying pan into the fire.
Loooking at your data structure, I'm even more convinced that such
code would a) be unnecessary and b) would take your database in the
wrong direction.

the id field is:
Assetnumber
Table names are:
Register - this holds main details of the other records
Work to do
Condition Notes
Items replaced
Items added

Notes, and Items, are both *PLURAL* words. Do you have multiple notes
for an asset? Or just one note? What about items? It REALLY looks to
me like these should be one to many relationships, not one to one: the
one to one relationship would require that each assetnumber have ONE
AND ONLY ONE, never any more, items replaced or items added. Is that
what you want? Only one record in each of these four tables?

Horrible thought: Does your [Condition Notes] table have fields named
Note1, Note2, Note3, Note4 and so on?

The tables use text fields and drop down boxes from look
up lists.
All the fields in the tables are different except for the
Assetnumber which is primary key in each table. One form
with pages enters details into the tables.

Again... *why not use Subforms* on those Pages? If you have the
Register table as the recordsource of the form, and on each tab page
you have a Subform based on [Work To Do], [Condition Notes], etc., and
have the Assetnumber as the master/child link field of the subform,
then:

If there is no Work To Do there will be no record in the Work To Do
table. But that's ok, because there is no work to do.

If there is work to do, you would type data into the Subform; Access
will instantly, silently, and correctly insert the correct Assetnumber
into the [Work To Do] table.

I cannot see ANY NEED for ANY PURPOSE to adding a new record to the
Work To Do table if in fact there is no work to do, and I can see many
problems with doing so.

Could you explain *why you consider it necessary*?

Oh dear, I can hear thousands of readers across the globe
laughing loudly .
Thanks for your help and I promise to try and do it
properly next time.

I don't think you need to wait till next time. With a few small
changes you can do it right *this* time.