Relationships and Tables and Subforms Oh My

[Robert A Wukich, Sr]

Hello Everyone

Apparently my last post got lost (I think, otherwise I apologize for the
double post).

Thanks in advance for all of the advice earlier, here is my problem now:

As mentioned, I have numerous tables that store data with my SSN as a
primary key and also as my basis for one to many relationships with all
of the tables. My question is this:

I created subforms with all of the tables that I use involved, the
problem I see is that if I do not have any input for these subforms ,
than the SSN does not migrate to the other tables. Can anyone provide me
some more guidance on this?

Thanks
Robert A. Wukich, Sr
Sgt/USMC

 

[Douglas J. Steele]

You may be misinterpretting what a relationship does for you.

It doesn't transfer data from one table to another. If SSN is the foreign
key in your second table, then you need to add the SSN to the second table
yourself.

 

[Ed Warren]

I've answered this same question (in detail) in your earlier posts
(programming automation) and (Introduction) (About my database).
If you ain't got any data you ain't gonna 'propagate the ssn'. If you got
data you're gonna propatate the ssn (if the subform is setup correctly).

I think your suffering a GCE (gross conceptual error) here!

Ed Warren

 

[Robert A Wukich, Sr]

Greetings Ed

No GCE here, just the failure to ask a smart question... I think the
appropriate question I was searching for (It came to me over a beer) is:
Can I program a macro or something similar to get the SSN to propagate?
Or maybe just have a checkbox as a means for propagation? Would that
work? Would prefer a script or how about this as an idea, when you are
done with one screen, you have a check box so that some data is in that
table tied to a macro that would bring you to the next sub form? That
"should" work, shouldnt it?

Ahhh the miracles of beer.... <grin>

Robert

 

[Ed Warren]

You have to give us more specifics to work with!
if I have a form (form1), based on table1, with a key field (ID) with a
subform (Form2), based on table2, with (ID as a field)
I put Form2 in Form1, as a subform, and tie them together using (ID)
(Form1.ID (parent) --> Form2.ID (child))

I open form1
I enter/change data in Form1
I go to the subform (form1 is still open, the subform is a part of form1)
in the subform I enter some data

When I am done I have data in two tables

Table1 (Form1) (ID, and some other stuff)
Table2 (form2) (keyID, ID, and some other stuff)

No code, no macro, no problem

How is this 'different' from what you are trying to do??


Ed Warren.

 

[Larry Daugherty]

Or for a short paraphrase; you don't create an empty child record. It
really *is* a GCE. The child record is only created when there is
something to put into that record. That SSN you're trying to enter is
something you've identified as a Foreign Key - the meaning of which is
something like 'My parent's Primary Key value is this'. There is
absolutely no reason to enter an empty record. If you're anticipating
a need to later query your data to find all individuals who don't have
a record in that particular (child) table, the result is achieved by
including the PK from the 'parent' table in the query.

Ya' gotta listen to us old sailors.

HTH

 

[John Vinson]

I think the
appropriate question I was searching for (It came to me over a beer) is:
Can I program a macro or something similar to get the SSN to propagate?

I just want to chime in with the others:

There is NO benefit to creating empty "placeholder" records in the
related tables for every SSN.

There are MANY disadvantages to doing so. Typically, such empty
records end up staying empty and never getting filled in at all.

If you have a Form based on the table with SSN as its primary key,
with Subforms based on the related tables, using SSN as the
master/child link field... then the SSN will be propagated
automatically to the child tables, the instant you start adding data
to one of the subforms. The SSN is needed then (when there is data in
the table which needs to be linked); it is NOT needed before then.

John W. Vinson[MVP]