Linking 2 Subforms To Display Only Current Record

[Guest]

I am desperately trying to link a subform (Suppliers) to another subform
(MSDSDocs) from my main form (Chemicals). Chemicals have many Suppliers and
each Supplier has many MSDSDocs. On my main form (Chemicals), if you activate
the Suppliers subform (I have it set to visible.NotVisible), it shows all of
the Suppliers for the specific chemical that is being displayed on the main
form (Chemicals). The Suppliers subform is displayed as a continuous from and
the field SupplierName is set to a hyperlink. The list would look something
like this:
Supplier1
Supplier2
Supplier3 and so on.

When I click any of the above supplier names, the second subform for
MSDSDocs is displayed (I have it set to visible.NotVisible also) BUT it
displays all of the MSDSDocs from all of the Suppliers that are associated
with the chemical on the main form. The linking for the chemical via
(IDPartNo) is working as the MSDSDocs subform only displays the MSDSDocs for
the current chemical record. HOWEVER, I only want it to display the MSDSDocs
for Supplier1 when I click on Supplier1, only the MSDSDocs for Supplier2 when
I click on Supplier2 and so on. I don’t want it to display all of the
MSDSDocs for all of the Suppliers associated with a particular chemical. The
code on my subform Suppliers field SupplierName onclick event is:

stLinkCriteria = "[IDSupplier]='" & Me![IDSupplier] & "' And [IDPartNo]
='" & Me![IDPartNo] & "'"

IDPartNo is from my Chemicals Table, Suppliers Table, and MSDSDocs Table.
Any suggestions? It took me a while just to write this question. If this is
confusing, I posted a screen shot of what I am trying to do at
http://www.chemreport.com/test/helpme.htm
Sorry for the long post but I spent my whole Thanksgiving holiday on this
one. I know I am close?

 

[tina]

Chemicals have many Suppliers and each Supplier has many MSDSDocs.

this suggests that there is no *direct* relationship between the Chemicals
table and the MSDSDocs table - instead the following table relationships, as

tblChemicals 1:n tblSuppliers
tblSuppliers 1:n tblMSDSDocs

with a foreign key field in tblSuppliers to link to the primary key field in
tblChemicals; and a foreign key field in tblMSDSDocs to link to the primary
key field in tblSuppliers.

if that's the case, then i'm wondering if you have the Chemicals table
directly (and incorrectly) linked to the MSDSDocs table - as the following
statement seems to suggest.

The linking for the chemical via (IDPartNo) is working as the MSDSDocs
subform only displays the MSDSDocs for the current chemical record.

it's reasonably easy to link the parent (Suppliers) record in one subform to
the child (MSDSDocs) records in another subform. but first i think we better
review your tables/relationships structure regarding these three tables,
because if that's not set up correctly then you'll probably have trouble
following the logic of the solution i give, and trouble making it work.

so: what are the relationships between the three tables (please use the
same relationship "syntax" that i followed, above)? what is name of the
primary key field in each table? and what is the name of each foreign key
field and what primary key field is it linked to?

hth

I am desperately trying to link a subform (Suppliers) to another subform
(MSDSDocs) from my main form (Chemicals). Chemicals have many Suppliers and
each Supplier has many MSDSDocs. On my main form (Chemicals), if you activate
the Suppliers subform (I have it set to visible.NotVisible), it shows all of
the Suppliers for the specific chemical that is being displayed on the main
form (Chemicals). The Suppliers subform is displayed as a continuous from and
the field SupplierName is set to a hyperlink. The list would look something
like this:
Supplier1
Supplier2
Supplier3 and so on.

When I click any of the above supplier names, the second subform for
MSDSDocs is displayed (I have it set to visible.NotVisible also) BUT it
displays all of the MSDSDocs from all of the Suppliers that are associated
with the chemical on the main form. The linking for the chemical via
(IDPartNo) is working as the MSDSDocs subform only displays the MSDSDocs for
the current chemical record. HOWEVER, I only want it to display the MSDSDocs
for Supplier1 when I click on Supplier1, only the MSDSDocs for Supplier2 when
I click on Supplier2 and so on. I don't want it to display all of the
MSDSDocs for all of the Suppliers associated with a particular chemical. The
code on my subform Suppliers field SupplierName onclick event is:

stLinkCriteria = "[IDSupplier]='" & Me![IDSupplier] & "' And [IDPartNo]
='" & Me![IDPartNo] & "'"

IDPartNo is from my Chemicals Table, Suppliers Table, and MSDSDocs Table.
Any suggestions? It took me a while just to write this question. If this is
confusing, I posted a screen shot of what I am trying to do at
http://www.chemreport.com/test/helpme.htm
Sorry for the long post but I spent my whole Thanksgiving holiday on this
one. I know I am close?

 

[Guest]

Hi Tina: The relationships are as follows with primary key listed first

IDPartNo.tblChemicals:IDPartNo.tblSuppliers
IDSupplier.tblSuppliers:IDSupplier.tblMSDSDocs

I'm not sure what you mean regarding foreign keys.

Chemicals have many Suppliers and each Supplier has many MSDSDocs.

this suggests that there is no *direct* relationship between the Chemicals
table and the MSDSDocs table - instead the following table relationships, as

tblChemicals 1:n tblSuppliers
tblSuppliers 1:n tblMSDSDocs

with a foreign key field in tblSuppliers to link to the primary key field in
tblChemicals; and a foreign key field in tblMSDSDocs to link to the primary
key field in tblSuppliers.

if that's the case, then i'm wondering if you have the Chemicals table
directly (and incorrectly) linked to the MSDSDocs table - as the following
statement seems to suggest.

The linking for the chemical via (IDPartNo) is working as the MSDSDocs
subform only displays the MSDSDocs for the current chemical record.

it's reasonably easy to link the parent (Suppliers) record in one subform to
the child (MSDSDocs) records in another subform. but first i think we better
review your tables/relationships structure regarding these three tables,
because if that's not set up correctly then you'll probably have trouble
following the logic of the solution i give, and trouble making it work.

so: what are the relationships between the three tables (please use the
same relationship "syntax" that i followed, above)? what is name of the
primary key field in each table? and what is the name of each foreign key
field and what primary key field is it linked to?

hth

I am desperately trying to link a subform (Suppliers) to another subform
(MSDSDocs) from my main form (Chemicals). Chemicals have many Suppliers and
each Supplier has many MSDSDocs. On my main form (Chemicals), if you activate
the Suppliers subform (I have it set to visible.NotVisible), it shows all of
the Suppliers for the specific chemical that is being displayed on the main
form (Chemicals). The Suppliers subform is displayed as a continuous from and
the field SupplierName is set to a hyperlink. The list would look something
like this:
Supplier1
Supplier2
Supplier3 and so on.

When I click any of the above supplier names, the second subform for
MSDSDocs is displayed (I have it set to visible.NotVisible also) BUT it
displays all of the MSDSDocs from all of the Suppliers that are associated
with the chemical on the main form. The linking for the chemical via
(IDPartNo) is working as the MSDSDocs subform only displays the MSDSDocs for
the current chemical record. HOWEVER, I only want it to display the MSDSDocs
for Supplier1 when I click on Supplier1, only the MSDSDocs for Supplier2 when
I click on Supplier2 and so on. I don't want it to display all of the
MSDSDocs for all of the Suppliers associated with a particular chemical. The
code on my subform Suppliers field SupplierName onclick event is:

stLinkCriteria = "[IDSupplier]='" & Me![IDSupplier] & "' And [IDPartNo]
='" & Me![IDPartNo] & "'"

IDPartNo is from my Chemicals Table, Suppliers Table, and MSDSDocs Table.
Any suggestions? It took me a while just to write this question. If this is
confusing, I posted a screen shot of what I am trying to do at
http://www.chemreport.com/test/helpme.htm
Sorry for the long post but I spent my whole Thanksgiving holiday on this
one. I know I am close?

 

[tina]

IDPartNo.tblChemicals:IDPartNo.tblSuppliers

IDSupplier.tblSuppliers:IDSupplier.tblMSDSDocs

okay, from the above i'll infer that
IDPartNo is the primary key field of tblChemicals
and tblSuppliers has a foreign key field also named IDPartNo
and the two tables are linked 1:n (one-to-many) on those two fields

IDSupplier is the primary key field of tblSuppliers
and tblMSDSDocs has a foreign key field also named IDSupplier
and the two tables are linked 1:n on those two fields

if the above conclusions are not correct, then the rest of what i say here
will probably not be useful - but here goes:
the main form is bound to tblChemicals. the "first" subform is bound to
tblSuppliers, and i'll call the subform control "ChildSuppliers". the
subform control's LinkChildFields and LinkMasterFields properties should
each be set to

IDPartNo

the "second" subform is bound to tblMSDSDocs, and i'll call the subform
control "ChildDocs". the subform control's LinkChildFields property should
be set to

IDSupplier

we'll get the the LinkMasterFields property in a minute.

okay, now add an unbound textbox to the main form, i'll call it
txtIDSupplier. in the Suppliers *subform*, add the following code to the
form's Current event, as

Private Sub Form_Current()

Me.Parent!txtIDSupplier = Me!IDSupplier

End Sub

if you don't know how to add a procedure to an event property, see "Create a
VBA event procedure" at http://home.att.net/~california.db/downloads.html,
for illustrated instructions.

go back to the ChildDocs subform control, and set the LinkMasterFields
property to

[txtIDSupplier]

an understanding of primary/foreign keys and how they work in table
relationships is essential to building a solid database that meets
normalization standards. strongly recommend you read up on data modeling and
normalization. see http://home.att.net/~california.db/tips.html#aTip1 for
more information.

hth

Hi Tina: The relationships are as follows with primary key listed first

IDPartNo.tblChemicals:IDPartNo.tblSuppliers
IDSupplier.tblSuppliers:IDSupplier.tblMSDSDocs

I'm not sure what you mean regarding foreign keys.

Chemicals have many Suppliers and each Supplier has many MSDSDocs.

this suggests that there is no *direct* relationship between the Chemicals
table and the MSDSDocs table - instead the following table relationships, as

tblChemicals 1:n tblSuppliers
tblSuppliers 1:n tblMSDSDocs

with a foreign key field in tblSuppliers to link to the primary key field in
tblChemicals; and a foreign key field in tblMSDSDocs to link to the primary
key field in tblSuppliers.

if that's the case, then i'm wondering if you have the Chemicals table
directly (and incorrectly) linked to the MSDSDocs table - as the following
statement seems to suggest.

The linking for the chemical via (IDPartNo) is working as the MSDSDocs
subform only displays the MSDSDocs for the current chemical record.

it's reasonably easy to link the parent (Suppliers) record in one subform to
the child (MSDSDocs) records in another subform. but first i think we better
review your tables/relationships structure regarding these three tables,
because if that's not set up correctly then you'll probably have trouble
following the logic of the solution i give, and trouble making it work.

so: what are the relationships between the three tables (please use the
same relationship "syntax" that i followed, above)? what is name of the
primary key field in each table? and what is the name of each foreign key
field and what primary key field is it linked to?

hth

I am desperately trying to link a subform (Suppliers) to another subform
(MSDSDocs) from my main form (Chemicals). Chemicals have many

Suppliers
and

each Supplier has many MSDSDocs. On my main form (Chemicals), if you activate
the Suppliers subform (I have it set to visible.NotVisible), it shows

all
of

the Suppliers for the specific chemical that is being displayed on the main
form (Chemicals). The Suppliers subform is displayed as a continuous

from
and

the field SupplierName is set to a hyperlink. The list would look something
like this:
Supplier1
Supplier2
Supplier3 and so on.

When I click any of the above supplier names, the second subform for
MSDSDocs is displayed (I have it set to visible.NotVisible also) BUT it
displays all of the MSDSDocs from all of the Suppliers that are associated
with the chemical on the main form. The linking for the chemical via
(IDPartNo) is working as the MSDSDocs subform only displays the

MSDSDocs
for

the current chemical record. HOWEVER, I only want it to display the MSDSDocs
for Supplier1 when I click on Supplier1, only the MSDSDocs for

Supplier2
when

I click on Supplier2 and so on. I don't want it to display all of the
MSDSDocs for all of the Suppliers associated with a particular

chemical.
The

code on my subform Suppliers field SupplierName onclick event is:

stLinkCriteria = "[IDSupplier]='" & Me![IDSupplier] & "' And [IDPartNo]
='" & Me![IDPartNo] & "'"

IDPartNo is from my Chemicals Table, Suppliers Table, and MSDSDocs Table.
Any suggestions? It took me a while just to write this question. If

this
is

confusing, I posted a screen shot of what I am trying to do at
http://www.chemreport.com/test/helpme.htm
Sorry for the long post but I spent my whole Thanksgiving holiday on this
one. I know I am close?

 

[Guest]

You're a wizard Tina! Thanks a million. It works great! I really appreciate
your taking the time to help me with this issue and hope that some day I can
contribute to the group!

Mike

IDPartNo.tblChemicals:IDPartNo.tblSuppliers
IDSupplier.tblSuppliers:IDSupplier.tblMSDSDocs

okay, from the above i'll infer that
IDPartNo is the primary key field of tblChemicals
and tblSuppliers has a foreign key field also named IDPartNo
and the two tables are linked 1:n (one-to-many) on those two fields

IDSupplier is the primary key field of tblSuppliers
and tblMSDSDocs has a foreign key field also named IDSupplier
and the two tables are linked 1:n on those two fields

if the above conclusions are not correct, then the rest of what i say here
will probably not be useful - but here goes:
the main form is bound to tblChemicals. the "first" subform is bound to
tblSuppliers, and i'll call the subform control "ChildSuppliers". the
subform control's LinkChildFields and LinkMasterFields properties should
each be set to

IDPartNo

the "second" subform is bound to tblMSDSDocs, and i'll call the subform
control "ChildDocs". the subform control's LinkChildFields property should
be set to

IDSupplier

we'll get the the LinkMasterFields property in a minute.

okay, now add an unbound textbox to the main form, i'll call it
txtIDSupplier. in the Suppliers *subform*, add the following code to the
form's Current event, as

Private Sub Form_Current()

Me.Parent!txtIDSupplier = Me!IDSupplier

End Sub

if you don't know how to add a procedure to an event property, see "Create a
VBA event procedure" at http://home.att.net/~california.db/downloads.html,
for illustrated instructions.

go back to the ChildDocs subform control, and set the LinkMasterFields
property to

[txtIDSupplier]

an understanding of primary/foreign keys and how they work in table
relationships is essential to building a solid database that meets
normalization standards. strongly recommend you read up on data modeling and
normalization. see http://home.att.net/~california.db/tips.html#aTip1 for
more information.

hth

Hi Tina: The relationships are as follows with primary key listed first

IDPartNo.tblChemicals:IDPartNo.tblSuppliers
IDSupplier.tblSuppliers:IDSupplier.tblMSDSDocs

I'm not sure what you mean regarding foreign keys.

Chemicals have many Suppliers and each Supplier has many MSDSDocs.

this suggests that there is no *direct* relationship between the Chemicals
table and the MSDSDocs table - instead the following table relationships, as

tblChemicals 1:n tblSuppliers
tblSuppliers 1:n tblMSDSDocs

with a foreign key field in tblSuppliers to link to the primary key field in
tblChemicals; and a foreign key field in tblMSDSDocs to link to the primary
key field in tblSuppliers.

if that's the case, then i'm wondering if you have the Chemicals table
directly (and incorrectly) linked to the MSDSDocs table - as the following
statement seems to suggest.

The linking for the chemical via (IDPartNo) is working as the MSDSDocs
subform only displays the MSDSDocs for the current chemical record.

it's reasonably easy to link the parent (Suppliers) record in one subform to
the child (MSDSDocs) records in another subform. but first i think we better
review your tables/relationships structure regarding these three tables,
because if that's not set up correctly then you'll probably have trouble
following the logic of the solution i give, and trouble making it work.

so: what are the relationships between the three tables (please use the
same relationship "syntax" that i followed, above)? what is name of the
primary key field in each table? and what is the name of each foreign key
field and what primary key field is it linked to?

hth


I am desperately trying to link a subform (Suppliers) to another subform
(MSDSDocs) from my main form (Chemicals). Chemicals have many Suppliers
and
each Supplier has many MSDSDocs. On my main form (Chemicals), if you
activate
the Suppliers subform (I have it set to visible.NotVisible), it shows all
of
the Suppliers for the specific chemical that is being displayed on the
main
form (Chemicals). The Suppliers subform is displayed as a continuous from
and
the field SupplierName is set to a hyperlink. The list would look
something
like this:
Supplier1
Supplier2
Supplier3 and so on.

When I click any of the above supplier names, the second subform for
MSDSDocs is displayed (I have it set to visible.NotVisible also) BUT it
displays all of the MSDSDocs from all of the Suppliers that are associated
with the chemical on the main form. The linking for the chemical via
(IDPartNo) is working as the MSDSDocs subform only displays the MSDSDocs
for
the current chemical record. HOWEVER, I only want it to display the
MSDSDocs
for Supplier1 when I click on Supplier1, only the MSDSDocs for Supplier2
when
I click on Supplier2 and so on. I don't want it to display all of the
MSDSDocs for all of the Suppliers associated with a particular chemical.
The
code on my subform Suppliers field SupplierName onclick event is:

stLinkCriteria = "[IDSupplier]='" & Me![IDSupplier] & "' And [IDPartNo]
='" & Me![IDPartNo] & "'"

IDPartNo is from my Chemicals Table, Suppliers Table, and MSDSDocs Table.
Any suggestions? It took me a while just to write this question. If this
is
confusing, I posted a screen shot of what I am trying to do at
http://www.chemreport.com/test/helpme.htm
Sorry for the long post but I spent my whole Thanksgiving holiday on this
one. I know I am close?

 

[tina]

you're welcome!
re "future contributing", i'm sure you will, Mike, and probably sooner than
you think now. the more you use Access, the more you learn, and when you
begin to internalize what you've learned - that is, understanding not only
the "what and how" but also the "why" of various solutions to design
issues - and apply it independently, you'll feel confident in helping others
get to that same place.

You're a wizard Tina! Thanks a million. It works great! I really appreciate
your taking the time to help me with this issue and hope that some day I can
contribute to the group!

Mike

IDPartNo.tblChemicals:IDPartNo.tblSuppliers
IDSupplier.tblSuppliers:IDSupplier.tblMSDSDocs

okay, from the above i'll infer that
IDPartNo is the primary key field of tblChemicals
and tblSuppliers has a foreign key field also named IDPartNo
and the two tables are linked 1:n (one-to-many) on those two fields

IDSupplier is the primary key field of tblSuppliers
and tblMSDSDocs has a foreign key field also named IDSupplier
and the two tables are linked 1:n on those two fields

if the above conclusions are not correct, then the rest of what i say here
will probably not be useful - but here goes:
the main form is bound to tblChemicals. the "first" subform is bound to
tblSuppliers, and i'll call the subform control "ChildSuppliers". the
subform control's LinkChildFields and LinkMasterFields properties should
each be set to

IDPartNo

the "second" subform is bound to tblMSDSDocs, and i'll call the subform
control "ChildDocs". the subform control's LinkChildFields property should
be set to

IDSupplier

we'll get the the LinkMasterFields property in a minute.

okay, now add an unbound textbox to the main form, i'll call it
txtIDSupplier. in the Suppliers *subform*, add the following code to the
form's Current event, as

Private Sub Form_Current()

Me.Parent!txtIDSupplier = Me!IDSupplier

End Sub

if you don't know how to add a procedure to an event property, see "Create a
VBA event procedure" at http://home.att.net/~california.db/downloads.html,
for illustrated instructions.

go back to the ChildDocs subform control, and set the LinkMasterFields
property to

[txtIDSupplier]

an understanding of primary/foreign keys and how they work in table
relationships is essential to building a solid database that meets
normalization standards. strongly recommend you read up on data modeling and
normalization. see http://home.att.net/~california.db/tips.html#aTip1 for
more information.

hth

Hi Tina: The relationships are as follows with primary key listed first

IDPartNo.tblChemicals:IDPartNo.tblSuppliers
IDSupplier.tblSuppliers:IDSupplier.tblMSDSDocs

I'm not sure what you mean regarding foreign keys.


:

Chemicals have many Suppliers and each Supplier has many MSDSDocs.

this suggests that there is no *direct* relationship between the Chemicals
table and the MSDSDocs table - instead the following table relationships, as

tblChemicals 1:n tblSuppliers
tblSuppliers 1:n tblMSDSDocs

with a foreign key field in tblSuppliers to link to the primary key field in
tblChemicals; and a foreign key field in tblMSDSDocs to link to the primary
key field in tblSuppliers.

if that's the case, then i'm wondering if you have the Chemicals table
directly (and incorrectly) linked to the MSDSDocs table - as the following
statement seems to suggest.

The linking for the chemical via (IDPartNo) is working as the MSDSDocs
subform only displays the MSDSDocs for the current chemical record.

it's reasonably easy to link the parent (Suppliers) record in one subform to
the child (MSDSDocs) records in another subform. but first i think

we
better

review your tables/relationships structure regarding these three tables,
because if that's not set up correctly then you'll probably have trouble
following the logic of the solution i give, and trouble making it work.

so: what are the relationships between the three tables (please use the
same relationship "syntax" that i followed, above)? what is name of the
primary key field in each table? and what is the name of each

foreign
key

field and what primary key field is it linked to?

hth


I am desperately trying to link a subform (Suppliers) to another subform
(MSDSDocs) from my main form (Chemicals). Chemicals have many Suppliers
and
each Supplier has many MSDSDocs. On my main form (Chemicals), if you
activate
the Suppliers subform (I have it set to visible.NotVisible), it

shows
all

of
the Suppliers for the specific chemical that is being displayed on the
main
form (Chemicals). The Suppliers subform is displayed as a

continuous
from

and
the field SupplierName is set to a hyperlink. The list would look
something
like this:
Supplier1
Supplier2
Supplier3 and so on.

When I click any of the above supplier names, the second subform for
MSDSDocs is displayed (I have it set to visible.NotVisible also)

BUT
it

displays all of the MSDSDocs from all of the Suppliers that are associated
with the chemical on the main form. The linking for the chemical via
(IDPartNo) is working as the MSDSDocs subform only displays the MSDSDocs
for
the current chemical record. HOWEVER, I only want it to display the
MSDSDocs
for Supplier1 when I click on Supplier1, only the MSDSDocs for Supplier2
when
I click on Supplier2 and so on. I don't want it to display all of the
MSDSDocs for all of the Suppliers associated with a particular chemical.
The
code on my subform Suppliers field SupplierName onclick event is:

stLinkCriteria = "[IDSupplier]='" & Me![IDSupplier] & "' And [IDPartNo]
='" & Me![IDPartNo] & "'"

IDPartNo is from my Chemicals Table, Suppliers Table, and MSDSDocs Table.
Any suggestions? It took me a while just to write this question.

If
this

is
confusing, I posted a screen shot of what I am trying to do at
http://www.chemreport.com/test/helpme.htm
Sorry for the long post but I spent my whole Thanksgiving holiday

on
this

one. I know I am close?