FORM/SUBFORM AND TABLES

[Guest]

I have three tables, SSN, CORR, MEDAL. The SSN.SSN (allows not duplicates)
is linked to the CORR.SSN (allows duplicates) and the MEDAL.SSN (allows
duplicates) is linked to the CORR.SSN.
I have three forms, SSNFORM, CORRFORM, MEDALFORM
The CORRFORM is a subform of the SSNFORM and the SSNFORM has an unbound
lookup feature for the SSN and looks up the matching record/records in the
CORRFORM.
What I am trying to do now is -- based on just one of the records in the
CORRFORM (CORR table) - open the MEDALFORM have it automatically add the SSN
from the CORRFORM so that it will tie these two records together add data to
the other fields in the MEDALFORM, and then be able to merge these two tables
via a query.

 

[Guest]

SSNs are not unique, so you may not want to prevent duplicates on this field.
Generate an ID with an Autonumber is a better design.

To help with your form troubles, setup a form to subform and the linking
field will automatically populate. Other than that, you don't want fields in
the child table that already exist in the parent.

 

[Guest]

I tried the auto number in the tables also, but still could not get it to
correspond with the specific record in the CORR table. I don't have
duplicate fields in the two tables, I just want to be able to tie the records
together so when I query I can pull info from both tables.

 

[John W. Vinson]

I have three tables, SSN, CORR, MEDAL. The SSN.SSN (allows not duplicates)
is linked to the CORR.SSN (allows duplicates) and the MEDAL.SSN (allows
duplicates) is linked to the CORR.SSN.

Well... that link appears to be incorrect. Since CORR allows multiple records
for each SSN, you cannot use SSN to link CORR to MEDAL, because the SSN does
not uniquely specify a record in CORR.

If you're going to use SSN as a primary key at all (and I agree that it has
real problems) then it should be the PK of SSN. CORR should have some other
field CorrID (let's say) as its PK, perhaps an Autonumber; MEDAL should then
have a Long Integer CorrID field as the link to CORR. It may be that you tried
to link autonumbers to autonumbers - that is *NOT* going to work.

I have three forms, SSNFORM, CORRFORM, MEDALFORM
The CORRFORM is a subform of the SSNFORM and the SSNFORM has an unbound
lookup feature for the SSN and looks up the matching record/records in the
CORRFORM.

What are the Master/Child Link Fields of the form and subform?

What I am trying to do now is -- based on just one of the records in the
CORRFORM (CORR table) - open the MEDALFORM have it automatically add the SSN
from the CORRFORM so that it will tie these two records together add data to
the other fields in the MEDALFORM, and then be able to merge these two tables
via a query.

But it WON'T link the the two records together.

Suppose in CORR you have records

555-55-5555 Tweedledum
555-55-5555 Tweedledee
555-55-5555 Red Queen

You add a new record to MEDALS. You can put 555-55-5555 into it, but that
doesn't let you specify who gets the medal!

Also, it should NOT be necessary to pop up a separate form. A Sub-subform is
going to be much easier to implement.

John W. Vinson [MVP]