Subform to Subform Link

[Aaron]

It appears as if it is only possible to link a subform to a parent
form, and it is not possible to link to from one subform to another.
I could have one subform within another, but the problem is that the
subform that I need to be the "intermediate" parent needs to also be a
datasheet style, which does not permit subforms. I don't really know
what to do at this point. Any ideas?

 

[Joan Wild]

You can have the two subforms as adjacent form, even if one is a child of
the other.

Place a textbox on the mainform, with a name of txtLink. Set its control
source to
=[Name of First Subform].[Form]![OrderID]
substitute OrderID for the name of the field that links the two.

Set the Master/Child properties of the second subform to
Master: txtLink
Child: OrderID

 

[DawnTreader]

Hello Joan

is it possible to add a third subform linked to the other two?

i have a main form with
CountryID
CountryName

then i have a subform with

SiteID
SiteName

then i have another subform with

ProductID
ProductName

these links work fine. when i click on the site on the first subform it
gives me all the products listed on the site. the next part is the trick...

i have one final subform that has

IssueID
IssueDate
IssueDetails

so when i choose a country using the nav buttons, i only want to see the
sites in that country. then when i click on a site in the subform i want to
see all the products on that site. then when i click on the product i want to
see all the issues that product has had.

currently the site subform is linked by
Link Child Fields : CountryID
Link Master Fields : CountryID

the link on the Product subform is
Link Child Fields : SiteID
Link Master Fields : [sitesubformname].Form![SiteID]

the link that is not working on the Issue subform is
Link Child Fields : ProductID
Link Master Fields : [productsubformname].Form![ProductID]

as i am writing this i am wondering do i need to put
[sitesubformname].Form![productsubformname].Form![ProductID]

as the linking on the third subform?

You can have the two subforms as adjacent form, even if one is a child of
the other.

Place a textbox on the mainform, with a name of txtLink. Set its control
source to
=[Name of First Subform].[Form]![OrderID]
substitute OrderID for the name of the field that links the two.

Set the Master/Child properties of the second subform to
Master: txtLink
Child: OrderID

--
Joan Wild
Microsoft Access MVP

It appears as if it is only possible to link a subform to a parent
form, and it is not possible to link to from one subform to another.
I could have one subform within another, but the problem is that the
subform that I need to be the "intermediate" parent needs to also be a
datasheet style, which does not permit subforms. I don't really know
what to do at this point. Any ideas?

 

[DawnTreader]

Hello

i found the problem. it had nothing to do with my link. it had to do with
the code behind the first subform. everything works great now that i added
one line to the code.

Sub Form_Current()

Dim ParentDocName As String

On Error Resume Next
ParentDocName = Me.Parent.Name

If Err <> 0 Then
GoTo Form_Current_Exit
Else
On Error GoTo Form_Current_Err
Me.Parent![sfmProductList].Requery
Me.Parent![sfmIssues].Requery ' this was the line i needed to add.
End If

Form_Current_Exit:
Exit Sub

Form_Current_Err:
MsgBox Error$
Resume Form_Current_Exit

End Sub

this code is all behind the first subform for the sites. so there you go,
you can do 3 subforms all linked together.

Hello Joan

is it possible to add a third subform linked to the other two?

i have a main form with
CountryID
CountryName

then i have a subform with

SiteID
SiteName

then i have another subform with

ProductID
ProductName

these links work fine. when i click on the site on the first subform it
gives me all the products listed on the site. the next part is the trick...

i have one final subform that has

IssueID
IssueDate
IssueDetails

so when i choose a country using the nav buttons, i only want to see the
sites in that country. then when i click on a site in the subform i want to
see all the products on that site. then when i click on the product i want to
see all the issues that product has had.

currently the site subform is linked by
Link Child Fields : CountryID
Link Master Fields : CountryID

the link on the Product subform is
Link Child Fields : SiteID
Link Master Fields : [sitesubformname].Form![SiteID]

the link that is not working on the Issue subform is
Link Child Fields : ProductID
Link Master Fields : [productsubformname].Form![ProductID]

as i am writing this i am wondering do i need to put
[sitesubformname].Form![productsubformname].Form![ProductID]

as the linking on the third subform?

You can have the two subforms as adjacent form, even if one is a child of
the other.

Place a textbox on the mainform, with a name of txtLink. Set its control
source to
=[Name of First Subform].[Form]![OrderID]
substitute OrderID for the name of the field that links the two.

Set the Master/Child properties of the second subform to
Master: txtLink
Child: OrderID

--
Joan Wild
Microsoft Access MVP

It appears as if it is only possible to link a subform to a parent
form, and it is not possible to link to from one subform to another.
I could have one subform within another, but the problem is that the
subform that I need to be the "intermediate" parent needs to also be a
datasheet style, which does not permit subforms. I don't really know
what to do at this point. Any ideas?

 

[DawnTreader]

Hello again

there was one last thing that i needed

the exact same code needs to be on the second subform, except for the line
that refers to the second subform.

Me.Parent![sfmProductList].Requery

so that line needed to go, but the rest of the code needed to be on the
second subform.

Hello

i found the problem. it had nothing to do with my link. it had to do with
the code behind the first subform. everything works great now that i added
one line to the code.

Sub Form_Current()

Dim ParentDocName As String

On Error Resume Next
ParentDocName = Me.Parent.Name

If Err <> 0 Then
GoTo Form_Current_Exit
Else
On Error GoTo Form_Current_Err
Me.Parent![sfmProductList].Requery
Me.Parent![sfmIssues].Requery ' this was the line i needed to add.
End If

Form_Current_Exit:
Exit Sub

Form_Current_Err:
MsgBox Error$
Resume Form_Current_Exit

End Sub

this code is all behind the first subform for the sites. so there you go,
you can do 3 subforms all linked together.

Hello Joan

is it possible to add a third subform linked to the other two?

i have a main form with
CountryID
CountryName

then i have a subform with

SiteID
SiteName

then i have another subform with

ProductID
ProductName

these links work fine. when i click on the site on the first subform it
gives me all the products listed on the site. the next part is the trick...

i have one final subform that has

IssueID
IssueDate
IssueDetails

so when i choose a country using the nav buttons, i only want to see the
sites in that country. then when i click on a site in the subform i want to
see all the products on that site. then when i click on the product i want to
see all the issues that product has had.

currently the site subform is linked by
Link Child Fields : CountryID
Link Master Fields : CountryID

the link on the Product subform is
Link Child Fields : SiteID
Link Master Fields : [sitesubformname].Form![SiteID]

the link that is not working on the Issue subform is
Link Child Fields : ProductID
Link Master Fields : [productsubformname].Form![ProductID]

as i am writing this i am wondering do i need to put
[sitesubformname].Form![productsubformname].Form![ProductID]

as the linking on the third subform?

You can have the two subforms as adjacent form, even if one is a child of
the other.

Place a textbox on the mainform, with a name of txtLink. Set its control
source to
=[Name of First Subform].[Form]![OrderID]
substitute OrderID for the name of the field that links the two.

Set the Master/Child properties of the second subform to
Master: txtLink
Child: OrderID

--
Joan Wild
Microsoft Access MVP
It appears as if it is only possible to link a subform to a parent
form, and it is not possible to link to from one subform to another.
I could have one subform within another, but the problem is that the
subform that I need to be the "intermediate" parent needs to also be a
datasheet style, which does not permit subforms. I don't really know
what to do at this point. Any ideas?