By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,483 Members | 1,506 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,483 IT Pros & Developers. It's quick & easy.

Linking Multiple Subforms

P: n/a
I have a main form with two subforms. The first subform relates to the
main form using an identity key (this works). The second subform
relates to both the main and first subform. But I want to be able to
move to a record in subform1 and have the corresponding records in
subform2. Similar to the Customers Order form in the Northwind db.

My problem is getting the second subform to work. The parent-child
link is set to subform1. I also checked that it works in a query. But
when the master form loads I get an error looking for the parent field
in subform1. I check this in the immediate window and the value is
already set. But it's not showing up in the order that the form is
loading.

I hope my explanation is clear about this problem.

So at this point I'm stuck and have no idea which way I should go with
this. Any help would be greatly appreciated.

One other thing. When the input parameter box opens up, if I put in
the correct number then the subforms link up.

So my main question is how to get the parent key from subform1 to
automatically be a valid field (and have subform2 use that value) when
the main form opens?

Dec 16 '06 #1
Share this Question
Share on Google+
9 Replies


P: n/a
"Ecohouse" <vi*******@netzero.comwrote in message
news:11*********************@16g2000cwy.googlegrou ps.com...
>I have a main form with two subforms. The first subform relates to the
main form using an identity key (this works). The second subform
relates to both the main and first subform. But I want to be able to
move to a record in subform1 and have the corresponding records in
subform2. Similar to the Customers Order form in the Northwind db.

My problem is getting the second subform to work. The parent-child
link is set to subform1. [snip]
There is no parent-child link. You have a MasterLink property and a ChildLink
property. The ChildLink property must be the name of a field in the subform's
RecordSource. The MasterLink can be a field name or a control name, but if the
latter it must be the name of a control that has a Value property (which a
subform control does not have).

So. What do you actually have entered in these two properties for the second
subform? The ChildLink should be two field names separated by a semi-colon and
the MasterLink should look something like...

ControlName; SubformControlName.Form!ControlName

....where the first ControlName is a control on the main form (could also be a
field name from the main form's RecordSource) and the second entry is the name
of the subform control containing the first subform followed by ".Form" followed
by the name of a control on the first subform or the name of a field in that
form's Recordsource.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com


Dec 16 '06 #2

P: n/a
Thanks for the help Rick. I use the subform wizard when I do this.
But this is what is happening:

subform1: Master Field = SK (control in Main Form)
Child Field = TrainingMasterSK (control in Subform1)
subform2: Master Field = TrainingModuleTopicSK (control in Subform1)
Child Field = TrainingModuleTopicSK (control in
Subform2)

I created a query to test the links between the subforms data and it
works. This is that query: SELECT ModuleComponents.*, TrainingModule.*
FROM ModuleComponents INNER JOIN TrainingModule ON
ModuleComponents.TrainingModuleTopicSK =
TrainingModule.TrainingModuleTopicSK;

So I think that I have this setup properly. Or am I still off here?
Rick Brandt wrote:
"Ecohouse" <vi*******@netzero.comwrote in message
news:11*********************@16g2000cwy.googlegrou ps.com...
I have a main form with two subforms. The first subform relates to the
main form using an identity key (this works). The second subform
relates to both the main and first subform. But I want to be able to
move to a record in subform1 and have the corresponding records in
subform2. Similar to the Customers Order form in the Northwind db.

My problem is getting the second subform to work. The parent-child
link is set to subform1. [snip]

There is no parent-child link. You have a MasterLink property and a ChildLink
property. The ChildLink property must be the name of a field in the subform's
RecordSource. The MasterLink can be a field name or a control name, but if the
latter it must be the name of a control that has a Value property (which a
subform control does not have).

So. What do you actually have entered in these two properties for the second
subform? The ChildLink should be two field names separated by a semi-colon and
the MasterLink should look something like...

ControlName; SubformControlName.Form!ControlName

...where the first ControlName is a control on the main form (could also be a
field name from the main form's RecordSource) and the second entry is the name
of the subform control containing the first subform followed by ".Form" followed
by the name of a control on the first subform or the name of a field in that
form's Recordsource.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
Dec 16 '06 #3

P: n/a
"Ecohouse" <vi*******@netzero.comwrote in message
news:11*********************@j72g2000cwa.googlegro ups.com...
Thanks for the help Rick. I use the subform wizard when I do this.
But this is what is happening:

subform1: Master Field = SK (control in Main Form)
Child Field = TrainingMasterSK (control in Subform1)
subform2: Master Field = TrainingModuleTopicSK (control in Subform1)
Child Field = TrainingModuleTopicSK (control in
Subform2)

I created a query to test the links between the subforms data and it
works. This is that query: SELECT ModuleComponents.*, TrainingModule.*
FROM ModuleComponents INNER JOIN TrainingModule ON
ModuleComponents.TrainingModuleTopicSK =
TrainingModule.TrainingModuleTopicSK;

So I think that I have this setup properly. Or am I still off here?
The second subform cannot directly reference something in the first subform. It
has to make that reference by going through the main form. This makes sense if
you understand that forms displayed in subforms are not actually "open forms".
They are "forms being displayed in a special control inside another form". The
only way to reference something on the subform is to reference the main form,
then the subform control containing the subform, then the property of that
control named "Form", then finally the field or control desired.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com


Dec 16 '06 #4

P: n/a
So I need to put a dummy control on the main form which references the
control TrainingModuleTopicSK in subform1?
Rick Brandt wrote:
"Ecohouse" <vi*******@netzero.comwrote in message
news:11*********************@j72g2000cwa.googlegro ups.com...
Thanks for the help Rick. I use the subform wizard when I do this.
But this is what is happening:

subform1: Master Field = SK (control in Main Form)
Child Field = TrainingMasterSK (control in Subform1)
subform2: Master Field = TrainingModuleTopicSK (control in Subform1)
Child Field = TrainingModuleTopicSK (control in
Subform2)

I created a query to test the links between the subforms data and it
works. This is that query: SELECT ModuleComponents.*, TrainingModule.*
FROM ModuleComponents INNER JOIN TrainingModule ON
ModuleComponents.TrainingModuleTopicSK =
TrainingModule.TrainingModuleTopicSK;

So I think that I have this setup properly. Or am I still off here?

The second subform cannot directly reference something in the first subform. It
has to make that reference by going through the main form. This makes sense if
you understand that forms displayed in subforms are not actually "open forms".
They are "forms being displayed in a special control inside another form". The
only way to reference something on the subform is to reference the main form,
then the subform control containing the subform, then the property of that
control named "Form", then finally the field or control desired.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
Dec 16 '06 #5

P: n/a
"Ecohouse" <vi*******@netzero.comwrote in message
news:11**********************@79g2000cws.googlegro ups.com...
So I need to put a dummy control on the main form which references the
control TrainingModuleTopicSK in subform1?
That is one way to do it, but you should be able to make the reference directly
in the MasterLink property. Instead of this in the MasterLink property...

TrainingModuleTopicSK

....you should be able to make an entry like...

SubFormControlName.Form!TrainingModuleTopicSK

Note the first part of that. It needs to be the name of the *control* that
hosts the first subform. That control could very well have a different name
than the form shown inside it. You need the name of the control, not the form.
Often when using drag and drop or the wizard the control WILL be named the same
as the form it contains, but you have to check that.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
Dec 16 '06 #6

P: n/a
Now I'm confused. What do you mean by "It needs to be the name of the
*control* that
hosts the first subform?" To me the name of the control is
"TrainingModuleTopicSK".

You're not saying to name this the following:
TrainingModuleTopicSK.Form!TrainingModuleTopicSK

The actual subform name is: subTrainingModule
The name of the control on that subform is: TrainingModuleTopicSK
Rick Brandt wrote:
"Ecohouse" <vi*******@netzero.comwrote in message
news:11**********************@79g2000cws.googlegro ups.com...
So I need to put a dummy control on the main form which references the
control TrainingModuleTopicSK in subform1?

That is one way to do it, but you should be able to make the reference directly
in the MasterLink property. Instead of this in the MasterLink property...

TrainingModuleTopicSK

...you should be able to make an entry like...

SubFormControlName.Form!TrainingModuleTopicSK

Note the first part of that. It needs to be the name of the *control* that
hosts the first subform. That control could very well have a different name
than the form shown inside it. You need the name of the control, not the form.
Often when using drag and drop or the wizard the control WILL be named the same
as the form it contains, but you have to check that.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
Dec 16 '06 #7

P: n/a
"Ecohouse" <vi*******@netzero.comwrote in message
news:11**********************@l12g2000cwl.googlegr oups.com...
Now I'm confused. What do you mean by "It needs to be the name of the
*control* that
hosts the first subform?" To me the name of the control is
"TrainingModuleTopicSK".

You're not saying to name this the following:
TrainingModuleTopicSK.Form!TrainingModuleTopicSK

The actual subform name is: subTrainingModule
The name of the control on that subform is: TrainingModuleTopicSK
It can be confusing to new users. The two rectangles you see on your main form
are *controls* just like TextBoxes or ComboBoxes or ListBoxes. They just happen
to be a special kind of control called a subform control. These subform
controls have a property "SourceObject" that determines what form is displayed
in them.

While it is convenient mental shorthand to think of a subform as "one form
inside another", in reality the outer, or parent form actually contains only a
subform control, not a copy of the actual form displayed inside the control.
Therefore when you want to reference a TextBox or field of a subform you have to
get there by going "through the subform control".

Access 97 and older display subform controls in design view as solid white
rectangles. This makes it pretty obvious that the object is a control and not
the actual form. The newer versions decided it would be better to display the
design of the form inside the subform control while in design view and this
obscures the fact that what you are looking at is NOT the actual form.

A good example of a similar control is an image control. In normal view you see
a picture on the form. In design view you don't see the image, but rather the
rectangular control that is used to display the image. A subform control is
just like that, a control that allows you to work with one form inside of
another, but it is a "window" so-to-speak to that other form.

The syntax I posted before is how you reference something on a subform. You
have to do it via the Form property of the subform control hosting the form. If
in design view of your main form you click one time on the edge of one of your
subforms that will select the subform control and you will see its properties
(including "Name") in the property sheet. If you click in the center of the
rectangle the property sheet will now show properties of the actual form inside
the control (more fuel for the confusion).

If you check the name property of the first subform control you might very well
find that it has the same name as the form within. That is fine, but it will
not always be the case. If the name is the same then the syntax you need is...

subTrainingModule.Form!TrainingModuleTopicSK
--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
Dec 17 '06 #8

P: n/a
explaining a subform control to a new developer is always hard, unless you
can sit beside him/her and do a show-and-tell. i thought you did an
excellent job of it, Rick; but in case the op (or somebody else reading this
thread) doesn't quite get it, the following may help:

http://home.att.net/~california.db/instructions.html
click the SubformControlName link to see an illustrated explanation in PDF
format.

hth
"Rick Brandt" <ri*********@hotmail.comwrote in message
news:b1*******************@newssvr29.news.prodigy. net...
"Ecohouse" <vi*******@netzero.comwrote in message
news:11**********************@l12g2000cwl.googlegr oups.com...
Now I'm confused. What do you mean by "It needs to be the name of the
*control* that
hosts the first subform?" To me the name of the control is
"TrainingModuleTopicSK".

You're not saying to name this the following:
TrainingModuleTopicSK.Form!TrainingModuleTopicSK

The actual subform name is: subTrainingModule
The name of the control on that subform is: TrainingModuleTopicSK

It can be confusing to new users. The two rectangles you see on your main
form
are *controls* just like TextBoxes or ComboBoxes or ListBoxes. They just
happen
to be a special kind of control called a subform control. These subform
controls have a property "SourceObject" that determines what form is
displayed
in them.

While it is convenient mental shorthand to think of a subform as "one form
inside another", in reality the outer, or parent form actually contains
only a
subform control, not a copy of the actual form displayed inside the
control.
Therefore when you want to reference a TextBox or field of a subform you
have to
get there by going "through the subform control".

Access 97 and older display subform controls in design view as solid white
rectangles. This makes it pretty obvious that the object is a control and
not
the actual form. The newer versions decided it would be better to display
the
design of the form inside the subform control while in design view and
this
obscures the fact that what you are looking at is NOT the actual form.

A good example of a similar control is an image control. In normal view
you see
a picture on the form. In design view you don't see the image, but rather
the
rectangular control that is used to display the image. A subform control
is
just like that, a control that allows you to work with one form inside of
another, but it is a "window" so-to-speak to that other form.

The syntax I posted before is how you reference something on a subform.
You
have to do it via the Form property of the subform control hosting the
form. If
in design view of your main form you click one time on the edge of one of
your
subforms that will select the subform control and you will see its
properties
(including "Name") in the property sheet. If you click in the center of
the
rectangle the property sheet will now show properties of the actual form
inside
the control (more fuel for the confusion).

If you check the name property of the first subform control you might very
well
find that it has the same name as the form within. That is fine, but it
will
not always be the case. If the name is the same then the syntax you need
is...
>
subTrainingModule.Form!TrainingModuleTopicSK
--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com


Dec 17 '06 #9

P: n/a
Thanks for all the help. I understand the concept that a subform is
really a control on the main form. I use subforms all the time. I
just never had to use more than one that are linked to each other.

Once I used this: subTrainingModule.Form!TrainingModuleTopicSK
as the Masterlink, the forms linked up. But I also had to use a
requery to the second subform from the on current of the first subform.
I was also trying to set the recordsource of a combobox in the second
subform based on the selection in the first subform. I'm pretty close
at this point and just to have to put in some more code and I should be
fine.

Thanks again.
tina wrote:
explaining a subform control to a new developer is always hard, unless you
can sit beside him/her and do a show-and-tell. i thought you did an
excellent job of it, Rick; but in case the op (or somebody else reading this
thread) doesn't quite get it, the following may help:

http://home.att.net/~california.db/instructions.html
click the SubformControlName link to see an illustrated explanation in PDF
format.

hth
"Rick Brandt" <ri*********@hotmail.comwrote in message
news:b1*******************@newssvr29.news.prodigy. net...
"Ecohouse" <vi*******@netzero.comwrote in message
news:11**********************@l12g2000cwl.googlegr oups.com...
Now I'm confused. What do you mean by "It needs to be the name of the
*control* that
hosts the first subform?" To me the name of the control is
"TrainingModuleTopicSK".
>
You're not saying to name this the following:
TrainingModuleTopicSK.Form!TrainingModuleTopicSK
>
The actual subform name is: subTrainingModule
The name of the control on that subform is: TrainingModuleTopicSK
It can be confusing to new users. The two rectangles you see on your main
form
are *controls* just like TextBoxes or ComboBoxes or ListBoxes. They just
happen
to be a special kind of control called a subform control. These subform
controls have a property "SourceObject" that determines what form is
displayed
in them.

While it is convenient mental shorthand to think of a subform as "one form
inside another", in reality the outer, or parent form actually contains
only a
subform control, not a copy of the actual form displayed inside the
control.
Therefore when you want to reference a TextBox or field of a subform you
have to
get there by going "through the subform control".

Access 97 and older display subform controls in design view as solid white
rectangles. This makes it pretty obvious that the object is a control and
not
the actual form. The newer versions decided it would be better to display
the
design of the form inside the subform control while in design view and
this
obscures the fact that what you are looking at is NOT the actual form.

A good example of a similar control is an image control. In normal view
you see
a picture on the form. In design view you don't see the image, but rather
the
rectangular control that is used to display the image. A subform control
is
just like that, a control that allows you to work with one form inside of
another, but it is a "window" so-to-speak to that other form.

The syntax I posted before is how you reference something on a subform.
You
have to do it via the Form property of the subform control hosting the
form. If
in design view of your main form you click one time on the edge of one of
your
subforms that will select the subform control and you will see its
properties
(including "Name") in the property sheet. If you click in the center of
the
rectangle the property sheet will now show properties of the actual form
inside
the control (more fuel for the confusion).

If you check the name property of the first subform control you might very
well
find that it has the same name as the form within. That is fine, but it
will
not always be the case. If the name is the same then the syntax you need
is...

subTrainingModule.Form!TrainingModuleTopicSK
--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
Dec 17 '06 #10

This discussion thread is closed

Replies have been disabled for this discussion.