Chaplain Doug said:I have two subforms on a tab control page. I want the
second subform to be linked with the first subform (both
forms are continuous and thus cannot be combined into one
form). How may this be accomplished? Thanks.
Chaplain Doug
Chaplain said:I have two subforms on a tab control page. I want the
second subform to be linked with the first subform (both
forms are continuous and thus cannot be combined into one
form). How may this be accomplished?
-----Original Message-----
I have two subforms on a tab control page. I want the
second subform to be linked with the first subform (both
forms are continuous and thus cannot be combined into one
form). How may this be accomplished? Thanks.
Chaplain Doug
You can put an invisible text box on the form that is parent to them
both, and set its controlsource property to an expression that returns
the linking field from the first subform; e.g.,
=[Subform1]![ID]
Then specify the name of this hidden text box in the Link Master Fields
property for the second subform.
--
Dirk Goldgar, MS Access MVP
www.datagnostics.com
(please reply to the newsgroup)
.
Graeme said:Hi Marshall, I have tried this (firstsubform.Form.keyfield) and found that
you need to issue a Requery command on the second subform for it to display
related records.
Is there something I missed?
..-----Original Message-----
I have two subforms on a tab control page. I want the
second subform to be linked with the first subform (both
forms are continuous and thus cannot be combined into one
form). How may this be accomplished? Thanks.
Chaplain Doug
You can put an invisible text box on the form that is parent to them
both, and set its controlsource property to an expression that returns
the linking field from the first subform; e.g.,
=[Subform1]![ID]
Then specify the name of this hidden text box in the Link Master Fields
property for the second subform.
--
Dirk Goldgar, MS Access MVP
www.datagnostics.com
(please reply to the newsgroup)
.
Chaplain Doug said:Created the text box on the main form whose source is the
linking field from the first form. Sure enough the text
box updates as I move from record to record in the first
form. I set the Link Master Fields in the second subform
control to the name of the text box (StudentIDLink). This
did not syncronize the forms. I tried setting the Link
Child Fields value to the field I wish to link (StudentID)
and this did not help. What am I missing?
.-----Original Message-----
I have two subforms on a tab control page. I want the
second subform to be linked with the first subform (both
forms are continuous and thus cannot be combined into one
form). How may this be accomplished? Thanks.
Chaplain Doug
You can put an invisible text box on the form that is parent to them
both, and set its controlsource property to an expression that returns
the linking field from the first subform; e.g.,
=[Subform1]![ID]
Then specify the name of this hidden text box in the Link Master Fields
property for the second subform.
--
Dirk Goldgar, MS Access MVP
www.datagnostics.com
(please reply to the newsgroup)
.
Chaplain Doug said:Created the text box on the main form whose source is the
linking field from the first form. Sure enough the text
box updates as I move from record to record in the first
form. I set the Link Master Fields in the second subform
control to the name of the text box (StudentIDLink). This
did not syncronize the forms. I tried setting the Link
Child Fields value to the field I wish to link (StudentID)
and this did not help. What am I missing?
-----Original Message-----
Hi Doug
It sounds like you've got it all right, but something must be missing, so
I'll go through all the steps to see if you can find the problem.
I'm making a few assumptions here:
You have a one-to-many relationship between Table1 and Table2, and a
one-to-many relationship between Table2 and Table3. Your main form is based
on Table1, SubformA is based on Table2, and SubformB is based on Table3.
When you navigate to a new Table1 record on the main form, SubformA lists
the related Table2 records in continuous view. What you want is for
SubformB to list the Table3 records which relate to the *current* row on
SubformA.
Am I kind of right?
In that case, you create a textbox on the main form, as Dirk has suggested.
You can hide it later when everything is working. You set its Name to
something sensible and its ControlSource to:
=[SubformA].[Form]![Primary key of TableA]
Note that here, "SubformA" is the name of the *control* which contains the
subform, which is not necessarily the same as the name of the form object it
contains.
Test this: as you change records in SubformA, you should see the contents
of the textbox changing.
Now, for the SubformB control, set LinkMasterFields to the *name* of your
textbox, and set LinkChildFields to the name of the *field in the
recordsource* of SubformB that contains the foreign key (the "many" side of
the relationship).
Now it should all work OK.
--
Good Luck!
Graham Mandeno [Access MVP]
Auckland, New Zealand
Created the text box on the main form whose source is the
linking field from the first form. Sure enough the text
box updates as I move from record to record in the first
form. I set the Link Master Fields in the second subform
control to the name of the text box (StudentIDLink). This
did not syncronize the forms. I tried setting the Link
Child Fields value to the field I wish to link (StudentID)
and this did not help. What am I missing?
expression-----Original Message-----
I have two subforms on a tab control page. I want the
second subform to be linked with the first subform (both
forms are continuous and thus cannot be combined into one
form). How may this be accomplished? Thanks.
Chaplain Doug
You can put an invisible text box on the form that is parent to them
both, and set its controlsource property to an
that returnsLinkthe linking field from the first subform; e.g.,
=[Subform1]![ID]
Then specify the name of this hidden text box in the
Master Fields.property for the second subform.
--
Dirk Goldgar, MS Access MVP
www.datagnostics.com
(please reply to the newsgroup)
.
.
Chaplain Doug said:My configuration is as follows:
I have three subform controls on a page of a tab control.
SubformA is a continuous form tied to table Profiles.
SubformB is a continuous form tied to table Progress.
SubformC is a continuous form tied to table [Progress
Level 2]. The is a one to many relationship between the
Profiles tables and the Progress table. There is a one to
many relationship between the Profiles table and the
[Progress Level 2] table. The field which links the
tables is StudentID. It is the primary key in Profiles
and just a field in the other two tables. When I select a
record in SubformA (via record selector), I want SubformB
and SubformC to display the records for this student
(StudentID).
At present my techniquye is to set the recordsource for
SubformB and SubformC in the OnCurrent event in SubformA.
It works but . . . if there are no existing records in
SubformC, then I cannot create records there. I did some
acrobatics to make that work. But it all seems dorky. As
I said, I tried your suggestions and still no workie.
-----Original Message-----
My configuration is as follows:
I have three subform controls on a page of a tab control.
SubformA is a continuous form tied to table Profiles.
SubformB is a continuous form tied to table Progress.
SubformC is a continuous form tied to table [Progress
Level 2]. The is a one to many relationship between the
Profiles tables and the Progress table. There is a one to
many relationship between the Profiles table and the
[Progress Level 2] table. The field which links the
tables is StudentID. It is the primary key in Profiles
and just a field in the other two tables. When I select a
record in SubformA (via record selector), I want SubformB
and SubformC to display the records for this student
(StudentID).
At present my techniquye is to set the recordsource for
SubformB and SubformC in the OnCurrent event in SubformA.
It works but . . . if there are no existing records in
SubformC, then I cannot create records there. I did some
acrobatics to make that work. But it all seems dorky. As
I said, I tried your suggestions and still no workie.
My guess is that your problem creating records in SubformC comes from
the absence of Link Master/Child Fields to fill in the necessary key
values as a record is inserted. There are ways to work around that if
you really want to continue with this approach, but I think setting up
the Link Master/Child Fields properly according to the method Graham,
Marsh, and I have been suggesting is by far the best way. So let's try
to get that to work.
I'm going to assume for the moment that the tables [Progress] and
[Progress Level 2] are both related to table [Profiles] (via StudentID),
but not to each other. Try these steps, which I'll make as detailed as
I can:
1. On SubformA, you must have a control that is bound to the StudentID
field. Name that control "txtStudentID". (Note -- the control *could*
just be named "StudentID", but I'm telling you to give it a distinct
name so that in a later step I can ensure that you are referring to the
control, not the field.)
2. On the *main* form, add a text box. Name this text box
"txtCurrentStudent".
3. On the main form, set the ControlSource property of txtCurrentStudent
to this expression:
=[SubformA].[Form]![txtStudentID]
4. On the main form, open the property sheet of the subform control that
displays SubformB. Its name *may* be "SubformB", or it may be something
else; it doesn't matter, but make sure it's the property sheet of the
subform control you're looking at, not the form inside that control.
5. On the Data tab of the property sheet, set properties as shown:
Link Child Fields: StudentID
Link Master Fields: txtCurrentStudent
Then close the property sheet.
6. On the main form, open the property sheet of the subform control that
displays SubformC. Its name *may* be "SubformC", or it may be something
else; it doesn't matter, but make sure it's the property sheet of the
subform control you're looking at, not the form inside that control.
7. On the Data tab of the property sheet, set properties as shown:
Link Child Fields: StudentID
Link Master Fields: txtCurrentStudent
Then close the property sheet.
8. "Comment out" or remove any code you have in the main form or
SubformA that sets the RecordSource properties of the other subforms.
These subforms should just have their RecordSource properties set at
design time to draw all records from their respective tables.
9. Save the form (and subforms, if necessary).
10. Open the main form and try it out.
That ought to work, Doug. If it doesn't, something's going on that we
don't know about.
--
Dirk Goldgar, MS Access MVP
www.datagnostics.com
(please reply to the newsgroup)
.
-----Original Message-----
My configuration is as follows:
I have three subform controls on a page of a tab control.
SubformA is a continuous form tied to table Profiles.
SubformB is a continuous form tied to table Progress.
SubformC is a continuous form tied to table [Progress
Level 2]. The is a one to many relationship between the
Profiles tables and the Progress table. There is a one to
many relationship between the Profiles table and the
[Progress Level 2] table. The field which links the
tables is StudentID. It is the primary key in Profiles
and just a field in the other two tables. When I select a
record in SubformA (via record selector), I want SubformB
and SubformC to display the records for this student
(StudentID).
At present my techniquye is to set the recordsource for
SubformB and SubformC in the OnCurrent event in SubformA.
It works but . . . if there are no existing records in
SubformC, then I cannot create records there. I did some
acrobatics to make that work. But it all seems dorky. As
I said, I tried your suggestions and still no workie.
My guess is that your problem creating records in SubformC comes from
the absence of Link Master/Child Fields to fill in the necessary key
values as a record is inserted. There are ways to work around that if
you really want to continue with this approach, but I think setting up
the Link Master/Child Fields properly according to the method Graham,
Marsh, and I have been suggesting is by far the best way. So let's try
to get that to work.
I'm going to assume for the moment that the tables [Progress] and
[Progress Level 2] are both related to table [Profiles] (via StudentID),
but not to each other. Try these steps, which I'll make as detailed as
I can:
1. On SubformA, you must have a control that is bound to the StudentID
field. Name that control "txtStudentID". (Note -- the control *could*
just be named "StudentID", but I'm telling you to give it a distinct
name so that in a later step I can ensure that you are referring to the
control, not the field.)
2. On the *main* form, add a text box. Name this text box
"txtCurrentStudent".
3. On the main form, set the ControlSource property of txtCurrentStudent
to this expression:
=[SubformA].[Form]![txtStudentID]
4. On the main form, open the property sheet of the subform control that
displays SubformB. Its name *may* be "SubformB", or it may be something
else; it doesn't matter, but make sure it's the property sheet of the
subform control you're looking at, not the form inside that control.
5. On the Data tab of the property sheet, set properties as shown:
Link Child Fields: StudentID
Link Master Fields: txtCurrentStudent
Then close the property sheet.
6. On the main form, open the property sheet of the subform control that
displays SubformC. Its name *may* be "SubformC", or it may be something
else; it doesn't matter, but make sure it's the property sheet of the
subform control you're looking at, not the form inside that control.
7. On the Data tab of the property sheet, set properties as shown:
Link Child Fields: StudentID
Link Master Fields: txtCurrentStudent
Then close the property sheet.
8. "Comment out" or remove any code you have in the main form or
SubformA that sets the RecordSource properties of the other subforms.
These subforms should just have their RecordSource properties set at
design time to draw all records from their respective tables.
9. Save the form (and subforms, if necessary).
10. Open the main form and try it out.
That ought to work, Doug. If it doesn't, something's going on that we
don't know about.
--
Dirk Goldgar, MS Access MVP
www.datagnostics.com
(please reply to the newsgroup)
.
-----Original Message-----
My configuration is as follows:
I have three subform controls on a page of a tab control.
SubformA is a continuous form tied to table Profiles.
SubformB is a continuous form tied to table Progress.
SubformC is a continuous form tied to table [Progress
Level 2]. The is a one to many relationship between the
Profiles tables and the Progress table. There is a one to
many relationship between the Profiles table and the
[Progress Level 2] table. The field which links the
tables is StudentID. It is the primary key in Profiles
and just a field in the other two tables. When I select a
record in SubformA (via record selector), I want SubformB
and SubformC to display the records for this student
(StudentID).
At present my techniquye is to set the recordsource for
SubformB and SubformC in the OnCurrent event in SubformA.
It works but . . . if there are no existing records in
SubformC, then I cannot create records there. I did some
acrobatics to make that work. But it all seems dorky. As
I said, I tried your suggestions and still no workie.
My guess is that your problem creating records in SubformC comes from
the absence of Link Master/Child Fields to fill in the necessary key
values as a record is inserted. There are ways to work around that if
you really want to continue with this approach, but I think setting up
the Link Master/Child Fields properly according to the method Graham,
Marsh, and I have been suggesting is by far the best way. So let's try
to get that to work.
I'm going to assume for the moment that the tables [Progress] and
[Progress Level 2] are both related to table [Profiles] (via StudentID),
but not to each other. Try these steps, which I'll make as detailed as
I can:
1. On SubformA, you must have a control that is bound to the StudentID
field. Name that control "txtStudentID". (Note -- the control *could*
just be named "StudentID", but I'm telling you to give it a distinct
name so that in a later step I can ensure that you are referring to the
control, not the field.)
2. On the *main* form, add a text box. Name this text box
"txtCurrentStudent".
3. On the main form, set the ControlSource property of txtCurrentStudent
to this expression:
=[SubformA].[Form]![txtStudentID]
4. On the main form, open the property sheet of the subform control that
displays SubformB. Its name *may* be "SubformB", or it may be something
else; it doesn't matter, but make sure it's the property sheet of the
subform control you're looking at, not the form inside that control.
5. On the Data tab of the property sheet, set properties as shown:
Link Child Fields: StudentID
Link Master Fields: txtCurrentStudent
Then close the property sheet.
6. On the main form, open the property sheet of the subform control that
displays SubformC. Its name *may* be "SubformC", or it may be something
else; it doesn't matter, but make sure it's the property sheet of the
subform control you're looking at, not the form inside that control.
7. On the Data tab of the property sheet, set properties as shown:
Link Child Fields: StudentID
Link Master Fields: txtCurrentStudent
Then close the property sheet.
8. "Comment out" or remove any code you have in the main form or
SubformA that sets the RecordSource properties of the other subforms.
These subforms should just have their RecordSource properties set at
design time to draw all records from their respective tables.
9. Save the form (and subforms, if necessary).
10. Open the main form and try it out.
That ought to work, Doug. If it doesn't, something's going on that we
don't know about.
--
Dirk Goldgar, MS Access MVP
www.datagnostics.com
(please reply to the newsgroup)
.
Chaplain Doug said:Also Dirk: These three subforms are on a page of a tab
control. I assume that txtCurrentStudentID should be on
the same tab control page?
Chaplain Doug said:Your directions step 8 made the final difference. I went
into each of the subforms (b and c) and changed their
record sources to the respective tables. Now everything
work! God bless and thanks for your patience.