SUBFORMS IN A FORM

  • Thread starter Thread starter Sandra
  • Start date Start date
S

Sandra

I have four tables I want to display on a form. They all
have a one-to-many relationship in order 1,2,3,4 with
primary to foreign key. The relationships show fine in the
relationship view.

I can do three tables (Form with table 1 and two subforms
with tables 2 and 3) and it works perfect.
When I make the form with table 1 with three subforms with
tables 2, 3, and 4 they do not link correctly. The design
doesn't allow me to change link child fields and link
parent fields. What am I doing wrong?

Frustrated...
 
Sandra,

Subforms are meant to be used inside of a master form, as you know. The
Link Child and Master properties of the subform control tell the subform
what records to display by joining the subform's recordset with the parent
form's recordset using the fields you specify in those properties.

It's designed to be used where the master field is apart of the parent form.
In your case, you can nest your subfoms within each other or you can trick
it by adding lookups to the master fields to the main form.

To nest them, you simply make 4 forms...one for each of your tables. Let's
call them frm1, frm2, frm3 and frm4. Each form shows the records from the
respective table ie: frm1 shows tbl1, frm2 shows tbl2, etc. Now you can add
frm4 as a subform on frm3, add frm3 as a subform on frm2, and add frm2 as a
subform on frm1. Using the respective PKs and FKs as LinkMaster and
LinkChild properties. This method usually requires too much screen space so
the next option might work better for you.

To display three related subforms on a single main form do the following:
Add frm2, frm3 and frm4 as subforms on frm1. Now set the master and child
properties of the subform frm2 and test to make sure the linking works. Now
add a text box to frm1 that looks up the Primary key on frm2. To do this
set the controlsource property of this new text box to:
=frm2.Form!FieldName
Where 'frm2' is the name of the subform control and 'FieldName' is the name
of the primary key field on frm2.
Now set the LinkMaster property of the frm3 subform to the name of the new
textbox and the Child property to the related foreign key field name.
Now test. When you move to a new record on frm2 it should requery frm3 to
display the related records automatically.

Repeat for linking frm4 to frm3.

I hope this make some sense instead of making you more frustrated.

HTH,
Josh
 
Back
Top