New record does not update all subforms

[Trent]

I am new to Access and this is my first post.

I have created what should be a relatively straight-forward database,
but one thing is eluding me. I have thought about what I want to
accomplish and have set up my tables/forms in the following way that
made the most sense to me. (Table, form & field names are generic for
simplicity.)

I have four tables "A" thru "D." A is demographic information and B,
C and D have additional (one-to-one) info. Each has a primary field
named "z" and the relationships are A-->B, A-->C and A-->D, using "z"
field. All relationships are one-to-one.

My primary form "A" contains three tabbed subforms, "B" "C" and "D."
Thus, when one is entering data in "B" subform, the demographic "A"
info remains visible.

When I click the new record button, a new, blank, record appears. I
enter the new identifying number in A's field "z" and the new number
is disseminated to tables/subforms B and C. D, however, remains stuck
on the previous record.

There is one big difference between between D and the other subforms.
Table D has many fields, so it is divided into several subforms. This
was accomplished by adding a blank subform "d_master" to the primary
form on the appropriate tab-page, and creating a new tab control that
contains subforms D1, D2, D3, etc. Each D subform is associated with
D table (that is, there are no D1, D2, etc tables).

Here is a rough ascii depiction of the form layout. (View with fixed
font.)

-------------------
| A |
| |
|---|---|-------| |
| B | C |Dmaster| |
| | |-------- |
| | |D1 | D2| |
| | | | | |
| | | | | |
-------------------

Things I have tried that have not worked:
1) Creating a Dmaster table containing only field "z."

2) With the Dmaster table version, I tried setting the relationships
two ways:
A-->Dmaster, Dmaster-->D
and
A-->Dmaster, A-->D

3) Because table/form B and C work, I have also tried creating a new D
table by copy/pasting the structure of C and thus recreating D on a
table that otherwise behaves as desired.

There are no macros or VBA, and I have no experience with either.
Thus, if the solution requires either, please be very explicit.

Thank you for your attention,
Trent Nicol

 

[Guest]

Try a new approach. Backup the database.
Remove the relationship. Change "z" field in B, C, and D to not be primary
key.
Create a one-to-many relationship from table A "z" field in B, C, and D
fields. Set referential integery and cascade update but not cascade delete.

Then give it a try.

 

[John W. Vinson]

Comments inline.

I am new to Access and this is my first post.

I have created what should be a relatively straight-forward database,
but one thing is eluding me. I have thought about what I want to
accomplish and have set up my tables/forms in the following way that
made the most sense to me. (Table, form & field names are generic for
simplicity.)

I have four tables "A" thru "D." A is demographic information and B,
C and D have additional (one-to-one) info. Each has a primary field
named "z" and the relationships are A-->B, A-->C and A-->D, using "z"
field. All relationships are one-to-one.

One to one relationships are VERY RARE and my guess is that it's inappropriate
in this case. See below.

My primary form "A" contains three tabbed subforms, "B" "C" and "D."
Thus, when one is entering data in "B" subform, the demographic "A"
info remains visible.

What do you mean by "tabbed subforms"? My inclination would be to have the
parent data on the mainform, and a Tab Control with three pages - one for B,
one for C, one for D. Is that what you mean?

When I click the new record button, a new, blank, record appears. I
enter the new identifying number in A's field "z" and the new number
is disseminated to tables/subforms B and C. D, however, remains stuck
on the previous record.

There is one big difference between between D and the other subforms.
Table D has many fields, so it is divided into several subforms. This
was accomplished by adding a blank subform "d_master" to the primary
form on the appropriate tab-page, and creating a new tab control that
contains subforms D1, D2, D3, etc. Each D subform is associated with
D table (that is, there are no D1, D2, etc tables).

Different SUBFORMS will not suit your need. They'll all be independent, and in
fact will interfere with one another, since you'll have two subforms competing
(from Access' viewpoint) to update the same record in the same table. See
below for an alternative idea, but you could consider putting a tab control ON
Subform D, with multiple controls on each page of the tab control.

1) Creating a Dmaster table containing only field "z."

2) With the Dmaster table version, I tried setting the relationships
two ways:
A-->Dmaster, Dmaster-->D
and
A-->Dmaster, A-->D

Wouldn't avoid the problem - it just adds another layer.

3) Because table/form B and C work, I have also tried creating a new D
table by copy/pasting the structure of C and thus recreating D on a
table that otherwise behaves as desired.

I think the problem lies, not in your Form, but in the normalization of your
tables. Tables which are related one to one can be combined into just one
table: why have one logical record spanning two different tables?

More of concern, if you have more fields than will fit on a screen, you're
almost surely embedding one or more one-to-many relationships in *every
record* of table D. You may have the same problem with B and C as well! A
table with thirty fields in it is already PAINFULLY wide. Could you post a
description of the Entities - real life persons, things or events -
represented by each table? What are some representative fieldnames, especially
from D?

John W. Vinson [MVP]

 

[Trent]

First, Karl, I tried to post earlier and it didn't seem to go
through. Your suggestion did not work for me.

John, comments inline:

Comments inline.




One to one relationships are VERY RARE and my guess is that it's inappropriate
in this case. See below.

Possibly. But switching to one-to-many as Karl suggested did not
help. More below.

What do you mean by "tabbed subforms"? My inclination would be to have the
parent data on the mainform, and a Tab Control with three pages - one for B,
one for C, one for D. Is that what you mean?

This is correct.

Different SUBFORMS will not suit your need. They'll all be independent, and in
fact will interfere with one another, since you'll have two subforms competing
(from Access' viewpoint) to update the same record in the same table. See
below for an alternative idea, but you could consider putting a tab control ON
Subform D, with multiple controls on each page of the tab control.

If I understand you, I do have have a tab control on subform D.
However, I'm not clear on what you mean by multiple controls on each
page... But see below for a very explicit description and a link to a
picture. I think it should clarify what I am trying to accomplish.

I think the problem lies, not in your Form, but in the normalization of your
tables. Tables which are related one to one can be combined into just one
table: why have one logical record spanning two different tables?
More of concern, if you have more fields than will fit on a screen, you're
almost surely embedding one or more one-to-many relationships in *every
record* of table D.

This statement is confusing to me. Can you state it another way?

You may have the same problem with B and C as well! A

table with thirty fields in it is already PAINFULLY wide. Could you post a
description of the Entities - real life persons, things or events -
represented by each table? What are some representative fieldnames, especially
from D?

I run a research lab and we're beginning a new large-scale study on
reading ability in kids and how it relates to brain function. Each
subject will have many different tests and will have much personal
data collected. It all could go into one table, but the table would
have an enormous number of fields. The data divide logically into
categories, however. Thus, one table is "History" containing info
about medications, disabilities, musical background, birth history,
etc. Another is "Learning" which contains the results of several
standardized tests (IQ, reading, spelling, etc.) Another is
"Screening" which includes the results of vision, hearing and some
other similar tests. [This is why I think it's one-to-one: There are
no cases where subject #50 will have more than one IQ score, for
example. But I confess, as a newbie, I'm still unclear on relationship
types.]

Here's a link to a pic of what the form looks like so far:
http://img453.imageshack.us/my.php?image=llb2qw1.gif

Using my generic nomenclature above, "z" field is Subject#. "A" table
is "LLB2" and contains the demographic info above the tab-control.
"B", "C" (and all the other working ones) are "Checklist",
"Screening", "Learning" etc. The misbehaving "D" table is History.
History is a LONG questionnaire, and alone has 135 fields. However,
it rather nicely divides into 9 categories. Hence the desire for the
subforms (I've actually used 8, because some (3,4,5) have very few
questions and were combined onto one subform. 2, meanwhile, was
actually split to two pages.) I realize that I could divide History
into nine tables, but I feel that for future queries, it will be
easier for a user to not have to remember which of the nine would
contain "MotherDrink," for example. (I did name all fields with a
leading number so that the correspondence with the form, and indeed
the actual questionnaire, would be easier to find. Hence example
field names are "2MotherDrink," "5GradeRepeated," "7InstrumentPlayed."

Thus I have one History table, but eight forms that point to it. Each
form occupies a second-level tab within the History tab. All other
tables are small enough that I did not feel the need to split them
into sub-subforms.

I hope this clarifies things and leads to a solution that doesn't
require a major reworking of my structure.

Thanks!
Trent

 

[John W. Vinson]

I run a research lab and we're beginning a new large-scale study on
reading ability in kids and how it relates to brain function. Each
subject will have many different tests and will have much personal
data collected. It all could go into one table, but the table would
have an enormous number of fields. The data divide logically into
categories, however. Thus, one table is "History" containing info
about medications, disabilities, musical background, birth history,
etc. Another is "Learning" which contains the results of several
standardized tests (IQ, reading, spelling, etc.) Another is
"Screening" which includes the results of vision, hearing and some
other similar tests. [This is why I think it's one-to-one: There are
no cases where subject #50 will have more than one IQ score, for
example. But I confess, as a newbie, I'm still unclear on relationship
types.]

Here's a link to a pic of what the form looks like so far:
http://img453.imageshack.us/my.php?image=llb2qw1.gif

Using my generic nomenclature above, "z" field is Subject#. "A" table
is "LLB2" and contains the demographic info above the tab-control.
"B", "C" (and all the other working ones) are "Checklist",
"Screening", "Learning" etc. The misbehaving "D" table is History.
History is a LONG questionnaire, and alone has 135 fields. However,
it rather nicely divides into 9 categories. Hence the desire for the
subforms (I've actually used 8, because some (3,4,5) have very few
questions and were combined onto one subform. 2, meanwhile, was
actually split to two pages.) I realize that I could divide History
into nine tables, but I feel that for future queries, it will be
easier for a user to not have to remember which of the nine would
contain "MotherDrink," for example. (I did name all fields with a
leading number so that the correspondence with the form, and indeed
the actual questionnaire, would be easier to find. Hence example
field names are "2MotherDrink," "5GradeRepeated," "7InstrumentPlayed."

Thus I have one History table, but eight forms that point to it. Each
form occupies a second-level tab within the History tab. All other
tables are small enough that I did not feel the need to split them
into sub-subforms.

I hope this clarifies things and leads to a solution that doesn't
require a major reworking of my structure.

I'm very sorry, but I really must at least suggest that you consider a major
reworking of your structure.

"Fields are expensive, records are cheap". You're storing data - medication
names, disabilities, etc. - in FIELDNAMES; I would guess that you have
checkboxes for (say) Down's syndrome, MotherDrink, etc. etc.

This is the source of your problem. This design is appropriate for a
spreadsheet BUT IT IS WRONG for a relational database.

If each child may have zero, one, or more disabilities, and each disability
may affect zero, one, or more children, you have - not a one to one
relationship - but a MANY (children) to MANY (disabilities) relationship.

The correct table structure for your history table questionnaire data would
have THREE tables: your TableA (demographic) data; a Questions table (with 135
records, not 135 fields!!!); and an Answers table, with fields for the unique
ID of the child, the unique ID of the question, and the "answer" to that
question (perhaps a yes/no field, or a number field indicating the intensity,
or other ways of reporting *this* historical observation for *this* child).

To enter the data you would have a main form based on the demographic table,
with a subform based on Answers. You'ld simply pick a question from a combo
box (based on the 135-row Questions table) and enter the answer for that
question.

One advantage of this normalized approach is that you can *add a new question*
painlessly. With your design, if you add, remove, or change a question you
must redesign your table, redesign all queries using the table, change the
structure of your form, change the structure of any report using that item...
nightmare!! With the normalized design you can simply add a new question to
the table, and then add new answers to the answer table. No form changes, no
report changes are needed.

I realize this is "hard lines" - but the time to correct your design is now,
before you get even further down the wrong path.

John W. Vinson [MVP]

 

[Trent]

On Jul 14, 7:52 pm, John W. Vinson
[...a suggested new field-centric approach]

Thanks for your comments. This never would have occured to me.
-Trent