Subforms not appearing on Main form

[Guest]

Hello,

So I have a main form with about 3 subforms on it. It works pretty well
except when I add a new record to the main form, the 3 subforms do not
appear. I know this is because the Primary key to the main form has to have a
corresponding foreign key to the data sources on the 3 subforms. Is there
anyway programaticly to add this to those other tables to which the subforms
are pulling from?

 

[BruceM]

If you set the Link Child and Link Master fields of the subform control to
the tables' linking fields you should be able to have a child record for
each parent record.
I'm not sure, though, that I understand what you mean by "the 3 subforms do
not appear". Do you mean the subforms themselves are not visible, or that
there is no place to add a record, or what exactly?
It may help if you decribe the database's purpose and structure, including
any relationships.

 

[Guest]

Hi Bruce,

I'm trying to do a Contact management database. The main form is based on
Company information. One subform is contact information another is order
information. For the information I have populated in all three tables, the
forms show up fine. However, when I add a new company record, the two
subforms Contacts and Orders are not visible because they do not have a
corresponding company ID in their tables. Is there any way to have a company
ID added to those two tables when I add one to the main form?

 

[BruceM]

You add the CompanyID to the Contacts and Orders tables by setting up the
table relationships so that the Contacts table and the Orders table are in
one-to-many relationship with the Company table, then by assuring the Link
Master and Link Child properties of the subform controls are set to the
linking fields.

tblCompany
CompanyID (Primary key, or PK)
CompanyName
etc.

tblContacts
ContactID (PK)
CompanyID (foreign key, or FK)
FirstName
LastName
etc.

tblOrders
OrderID (PK)
CompanyID (FK)
OrderDate
etc.

Open the Relationships window (Tools > Relationships), and add the three
tables. Drag CompanyID from tblCompany onto CompanyID in tblContacts.
Click Enforce Referential Integrity. Do the same thing with CompanyID in
tblCompany and tblOrders. Note that if CompanyID is autonumber in
tblCompany, it must be Long Integer in tblContacts and in tblOrders;
otherwise the PK and FK fields must be the same data type as established in
tables design view (autonumber is a type of long integer, but it is a
separate selection in table design view). Close the relationship window.
Make a form (frmCompany) based on tblCompany, another (frmOrders) based on
tblOrders, and a third (frmContacts) based on tblContacts. frmOrders and
frmContacts should probably have their Default View set to Continuous to
start.
With frmCompany open in design view, use the toolbox to add a
subform/subreport control. On the Property sheet (click View > Properties
if it is not open), set the Source Object to frmContacts. The Link Child
and Link Master fields should take care of themselves if you have set up the
table relationships correctly. Repeat with another subform control for
frmOrders.
When you add a new record, the FK fields automatically match the PK field.
There is no need to assign the number programatically. Once you start
entering data onto frmCompany you should see places to add Contact and Order
information. To be sure the subforms are showing up on the form, add a
label or something to the headers for frmOrders and frmContacts so that even
if there are no records you can see the subform is there.

 

[Guest]

Hi Bruce,

My experience with the referential integrity part is that if you delete or
change the PK in the main table, it will make the corresponding change in the
related tables.

However, I'm not sure that if you add a PK to the main table, it
automatically adds to the related tables. I've tried to do this but so far
have been unsuccessful.

 

[BruceM]

The related tables acquire the same data (let's say number) as in the PK
field in the parent record. However, they only acquire that number as their
foreign key field when you add a record. If you do not add a contact
record, the linked field will not appear on the subform or in its underlying
table.

Follow my instructions carefully, using sample data in a new database if
need be. It will work.

 

[Guest]

Thanks Bruce,

I got it to work when I used a sample database. Now I'll just figure out
what I did different on the real database. Thanks for the assistance!

 

[BruceM]

Glad to hear the experiment worked. Feel free to post details of the table
structure and relationships in your actual database project.