Link Master/Child Fields?

[Dave Birley]

My Main Form has as its Record Source the Product_Family table. This is the
same table that is the Record Source for the "grandparent" control, a
combobox. Changes in the combobox are reflected in the first subform
"Company" Which has Company Subform as its Source Object. The controls on
this subform are populated by Query2 which is based on the Company table with
reference to the Product_Family table in this WHERE clause:

WHERE (((Company.Product_ID)=[Me]![Parent]![cboProductFamily].[Product_ID])).

So far so good. Works exactly the way I expect it to -- Company subform
revises its content every time an item in the Product combobox changes.

There is a table called tblGroup which manages the relationship between the
Product and the Company tables, which [auto-]generated a unique ID for each
possible combination.

The final table, called tblItems has reference to the Group Table. In the
Relationships, these tables are all linked together by the appropriate IDs:

Product_Family->Company->Group->Items

The Groups Subform has Group Subform as its Source Object, and the Record
Source for its controls is Query1:

SELECT Group.Group_Name, Item.Item, Item.Reference_Address, Company.Company_ID
FROM Company INNER JOIN ([Group] INNER JOIN Item ON Group.Group_ID =
Item.Group_ID) ON Company.Company_ID = Group.Company_ID;

However, when I want to link the Group Subform up to make it refresh on
changed selection in the Company Subform, the Master/Child link offerings
present only items from the Product_Family table for master. I need it to be
looking to the Group table, or else looking to the Company_ID determined by
the pointer in the Company Subform.

It is frustrating to be this close, and just missing some basic component --
have I given enough description here to allow someone to get the drift of
what I need?

Specifically I need the Group Subform to update when the selected item in
the Company Subform changes, and always to display only the Distinct Items
related to the Product_Family/Company selections.

 

[Ken Snell \(MVP\)]

You can "link" the two subforms using this trick; here is how you do this:

Put an invisible textbox (name it txtLinking) in the main form (form header
section is fine). Set its control source to an expression similar to this:

=[subform1].Form.[ControlNameWithLinkingValue]

where ControlNameWithLinkingValue is the name of the control in subform1
that holds the value for KitItem in subform2.

Then put this in LinkChildFields property of subform2 control:
Company_ID

And put this in LinkMasterFields property of subform2 control:
txtLinking

Now the two subforms are linked.

 

[Dave Birley]

Now that's slicker 'n a pig in poop! I knew there was a way to do it, and
what you're saying makes perfect sense (unlike most of what I seem to say
<g>).

I'll give her a shot and return and report!
--
Dave
Temping with Staffmark
in Rock Hill, SC

You can "link" the two subforms using this trick; here is how you do this:

Put an invisible textbox (name it txtLinking) in the main form (form header
section is fine). Set its control source to an expression similar to this:

=[subform1].Form.[ControlNameWithLinkingValue]

where ControlNameWithLinkingValue is the name of the control in subform1
that holds the value for KitItem in subform2.

Then put this in LinkChildFields property of subform2 control:
Company_ID

And put this in LinkMasterFields property of subform2 control:
txtLinking

Now the two subforms are linked.


--

Ken Snell
<MS ACCESS MVP>

My Main Form has as its Record Source the Product_Family table. This is
the
same table that is the Record Source for the "grandparent" control, a
combobox. Changes in the combobox are reflected in the first subform
"Company" Which has Company Subform as its Source Object. The controls on
this subform are populated by Query2 which is based on the Company table
with
reference to the Product_Family table in this WHERE clause:

WHERE
(((Company.Product_ID)=[Me]![Parent]![cboProductFamily].[Product_ID])).

So far so good. Works exactly the way I expect it to -- Company subform
revises its content every time an item in the Product combobox changes.

There is a table called tblGroup which manages the relationship between
the
Product and the Company tables, which [auto-]generated a unique ID for
each
possible combination.

The final table, called tblItems has reference to the Group Table. In the
Relationships, these tables are all linked together by the appropriate
IDs:

Product_Family->Company->Group->Items

The Groups Subform has Group Subform as its Source Object, and the Record
Source for its controls is Query1:

SELECT Group.Group_Name, Item.Item, Item.Reference_Address,
Company.Company_ID
FROM Company INNER JOIN ([Group] INNER JOIN Item ON Group.Group_ID =
Item.Group_ID) ON Company.Company_ID = Group.Company_ID;

However, when I want to link the Group Subform up to make it refresh on
changed selection in the Company Subform, the Master/Child link offerings
present only items from the Product_Family table for master. I need it to
be
looking to the Group table, or else looking to the Company_ID determined
by
the pointer in the Company Subform.

It is frustrating to be this close, and just missing some basic
component --
have I given enough description here to allow someone to get the drift of
what I need?

Specifically I need the Group Subform to update when the selected item in
the Company Subform changes, and always to display only the Distinct Items
related to the Product_Family/Company selections.

 

[Ken Snell \(MVP\)]

You're welcome; I forgot to change one word in my copy/paste; here is
corrected copy:

You can "link" the two subforms using this trick; here is how you do this:

Put an invisible textbox (name it txtLinking) in the main form (form header
section is fine). Set its control source to an expression similar to this:

=[subform1].Form.[ControlNameWithLinkingValue]

where ControlNameWithLinkingValue is the name of the control in subform1
that holds the value for Company_ID in subform2.

Then put this in LinkChildFields property of subform2 control:
Company_ID

And put this in LinkMasterFields property of subform2 control:
txtLinking

Now the two subforms are linked.


--

Ken Snell
<MS ACCESS MVP>

Now that's slicker 'n a pig in poop! I knew there was a way to do it, and
what you're saying makes perfect sense (unlike most of what I seem to say
<g>).

I'll give her a shot and return and report!
--
Dave
Temping with Staffmark
in Rock Hill, SC

You can "link" the two subforms using this trick; here is how you do
this:

Put an invisible textbox (name it txtLinking) in the main form (form
header
section is fine). Set its control source to an expression similar to
this:

=[subform1].Form.[ControlNameWithLinkingValue]

where ControlNameWithLinkingValue is the name of the control in subform1
that holds the value for KitItem in subform2.

Then put this in LinkChildFields property of subform2 control:
Company_ID

And put this in LinkMasterFields property of subform2 control:
txtLinking

Now the two subforms are linked.


--

Ken Snell
<MS ACCESS MVP>

My Main Form has as its Record Source the Product_Family table. This
is
the
same table that is the Record Source for the "grandparent" control, a
combobox. Changes in the combobox are reflected in the first subform
"Company" Which has Company Subform as its Source Object. The controls
on
this subform are populated by Query2 which is based on the Company
table
with
reference to the Product_Family table in this WHERE clause:

WHERE
(((Company.Product_ID)=[Me]![Parent]![cboProductFamily].[Product_ID])).

So far so good. Works exactly the way I expect it to -- Company subform
revises its content every time an item in the Product combobox changes.

There is a table called tblGroup which manages the relationship between
the
Product and the Company tables, which [auto-]generated a unique ID for
each
possible combination.

The final table, called tblItems has reference to the Group Table. In
the
Relationships, these tables are all linked together by the appropriate
IDs:

Product_Family->Company->Group->Items

The Groups Subform has Group Subform as its Source Object, and the
Record
Source for its controls is Query1:

SELECT Group.Group_Name, Item.Item, Item.Reference_Address,
Company.Company_ID
FROM Company INNER JOIN ([Group] INNER JOIN Item ON Group.Group_ID =
Item.Group_ID) ON Company.Company_ID = Group.Company_ID;

However, when I want to link the Group Subform up to make it refresh on
changed selection in the Company Subform, the Master/Child link
offerings
present only items from the Product_Family table for master. I need it
to
be
looking to the Group table, or else looking to the Company_ID
determined
by
the pointer in the Company Subform.

It is frustrating to be this close, and just missing some basic
component --
have I given enough description here to allow someone to get the drift
of
what I need?

Specifically I need the Group Subform to update when the selected item
in
the Company Subform changes, and always to display only the Distinct
Items
related to the Product_Family/Company selections.

 

[Ken Snell \(MVP\)]

When I read your original post, I understood that the Company_ID value was
the link between the Group and Company, but I think I misunderstood which
value is linking the two subforms. If that value is Group_ID, then the
invisible textbox on the main form should get the value of Group_ID from the
first subform (it's best to use a control that is bound to the Group_ID
field in the subform as the target of the control source of the invisible
textbox, txtLinking), and then use Group_ID as the child field in the second
subform's LinkChildFields property (again, it's best to use a control that
is bound to Group_ID field in the second subform as the LinkChildFields
property value).
--

Ken Snell
<MS ACCESS MVP>

Well, that has brought me the closest I've been -- not quite all the way
home, but the edge of the forest is in sight.

By "ControlNameWithLinkingValue" I am assuming you mean I have the choice
between the listbox on SubForm1 (lstCompany) or the ID field I really want
to
get out of it (Company_ID).

If I use the listbox name, the machine doesn't love me; put square
brackets
around its name as in your example, and it removes them. Then it tries to
tell me that things are too complicated for it.

If I use Company_ID I [[[FINALLY]]] get a filtered display in my items
list,
but there are a couple of oddities to the list: I'm pretty sure it isn't
the
correct data, and, more importantly, when I select anything other than the
top row of displayed items, the pointer jumps immediately back up to the
top
slot.

In my rather long narrative at the top of this thread, I spoke of my
tblGroup, whose purpose is to tie the Product and Company selections
together, and then, based on that result, to "push" the second sub-form.
Do I
need to use your invisible textbox trick again to "display" the resultant
Group_ID value, and then pick that off as my control for txtLinking?

I'm going to give that a shot, and if it works I'll let you know, but if
you
know for sure that isn't the way to go, I'll await your next epistle
breathlessly <g>!
--
Dave
Temping with Staffmark
in Rock Hill, SC

You're welcome; I forgot to change one word in my copy/paste; here is
corrected copy:

You can "link" the two subforms using this trick; here is how you do
this:

Put an invisible textbox (name it txtLinking) in the main form (form
header
section is fine). Set its control source to an expression similar to
this:

=[subform1].Form.[ControlNameWithLinkingValue]

where ControlNameWithLinkingValue is the name of the control in subform1
that holds the value for Company_ID in subform2.

Then put this in LinkChildFields property of subform2 control:
Company_ID

And put this in LinkMasterFields property of subform2 control:
txtLinking

Now the two subforms are linked.


--

Ken Snell
<MS ACCESS MVP>

Now that's slicker 'n a pig in poop! I knew there was a way to do it,
and
what you're saying makes perfect sense (unlike most of what I seem to
say
<g>).

I'll give her a shot and return and report!
--
Dave
Temping with Staffmark
in Rock Hill, SC


:

You can "link" the two subforms using this trick; here is how you do
this:

Put an invisible textbox (name it txtLinking) in the main form (form
header
section is fine). Set its control source to an expression similar to
this:

=[subform1].Form.[ControlNameWithLinkingValue]

where ControlNameWithLinkingValue is the name of the control in
subform1
that holds the value for KitItem in subform2.

Then put this in LinkChildFields property of subform2 control:
Company_ID

And put this in LinkMasterFields property of subform2 control:
txtLinking

Now the two subforms are linked.


--

Ken Snell
<MS ACCESS MVP>

My Main Form has as its Record Source the Product_Family table.
This
is
the
same table that is the Record Source for the "grandparent" control,
a
combobox. Changes in the combobox are reflected in the first subform
"Company" Which has Company Subform as its Source Object. The
controls
on
this subform are populated by Query2 which is based on the Company
table
with
reference to the Product_Family table in this WHERE clause:

WHERE
(((Company.Product_ID)=[Me]![Parent]![cboProductFamily].[Product_ID])).

So far so good. Works exactly the way I expect it to -- Company
subform
revises its content every time an item in the Product combobox
changes.

There is a table called tblGroup which manages the relationship
between
the
Product and the Company tables, which [auto-]generated a unique ID
for
each
possible combination.

The final table, called tblItems has reference to the Group Table.
In
the
Relationships, these tables are all linked together by the
appropriate
IDs:

Product_Family->Company->Group->Items

The Groups Subform has Group Subform as its Source Object, and the
Record
Source for its controls is Query1:

SELECT Group.Group_Name, Item.Item, Item.Reference_Address,
Company.Company_ID
FROM Company INNER JOIN ([Group] INNER JOIN Item ON Group.Group_ID =
Item.Group_ID) ON Company.Company_ID = Group.Company_ID;

However, when I want to link the Group Subform up to make it refresh
on
changed selection in the Company Subform, the Master/Child link
offerings
present only items from the Product_Family table for master. I need
it
to
be
looking to the Group table, or else looking to the Company_ID
determined
by
the pointer in the Company Subform.

It is frustrating to be this close, and just missing some basic
component --
have I given enough description here to allow someone to get the
drift
of
what I need?

Specifically I need the Group Subform to update when the selected
item
in
the Company Subform changes, and always to display only the Distinct
Items
related to the Product_Family/Company selections.

 

[Dave Birley]

Hehehe! Sure is hard to solve a problem programmatically when the DATA is
wrong!

What I had previously was a database where Company was the top level, and
Product second, with Group last. Boss-o said that what he wanted would
require Product in first place and Company in second.

Oooooooookay. That meant that wll the hooks I had tying those first two
tables together went into the toilet, and in the process messed up the Group
table. Now I've had a chance to look at things some more (with your
intercession helping a LOT), it has dawned on me that the Group table is
totally redundant. However I now have to add a Company_ID field to the Items
table and make it refer to the newly revised Company table instead of the
Group table.

Fortunately I have backups of the earlier version. and fortunately (v2.0)
there are only 593 items in the Items table, but it's grunt work all the way.

We can close the case, Inspector!
--
Dave
Temping with Staffmark
in Rock Hill, SC

When I read your original post, I understood that the Company_ID value was
the link between the Group and Company, but I think I misunderstood which
value is linking the two subforms. If that value is Group_ID, then the
invisible textbox on the main form should get the value of Group_ID from the
first subform (it's best to use a control that is bound to the Group_ID
field in the subform as the target of the control source of the invisible
textbox, txtLinking), and then use Group_ID as the child field in the second
subform's LinkChildFields property (again, it's best to use a control that
is bound to Group_ID field in the second subform as the LinkChildFields
property value).
--

Ken Snell
<MS ACCESS MVP>

Well, that has brought me the closest I've been -- not quite all the way
home, but the edge of the forest is in sight.

By "ControlNameWithLinkingValue" I am assuming you mean I have the choice
between the listbox on SubForm1 (lstCompany) or the ID field I really want
to
get out of it (Company_ID).

If I use the listbox name, the machine doesn't love me; put square
brackets
around its name as in your example, and it removes them. Then it tries to
tell me that things are too complicated for it.

If I use Company_ID I [[[FINALLY]]] get a filtered display in my items
list,
but there are a couple of oddities to the list: I'm pretty sure it isn't
the
correct data, and, more importantly, when I select anything other than the
top row of displayed items, the pointer jumps immediately back up to the
top
slot.

In my rather long narrative at the top of this thread, I spoke of my
tblGroup, whose purpose is to tie the Product and Company selections
together, and then, based on that result, to "push" the second sub-form.
Do I
need to use your invisible textbox trick again to "display" the resultant
Group_ID value, and then pick that off as my control for txtLinking?

I'm going to give that a shot, and if it works I'll let you know, but if
you
know for sure that isn't the way to go, I'll await your next epistle
breathlessly <g>!
--
Dave
Temping with Staffmark
in Rock Hill, SC

You're welcome; I forgot to change one word in my copy/paste; here is
corrected copy:

You can "link" the two subforms using this trick; here is how you do
this:

Put an invisible textbox (name it txtLinking) in the main form (form
header
section is fine). Set its control source to an expression similar to
this:

=[subform1].Form.[ControlNameWithLinkingValue]

where ControlNameWithLinkingValue is the name of the control in subform1
that holds the value for Company_ID in subform2.

Then put this in LinkChildFields property of subform2 control:
Company_ID

And put this in LinkMasterFields property of subform2 control:
txtLinking

Now the two subforms are linked.


--

Ken Snell
<MS ACCESS MVP>


Now that's slicker 'n a pig in poop! I knew there was a way to do it,
and
what you're saying makes perfect sense (unlike most of what I seem to
say
<g>).

I'll give her a shot and return and report!
--
Dave
Temping with Staffmark
in Rock Hill, SC


:

You can "link" the two subforms using this trick; here is how you do
this:

Put an invisible textbox (name it txtLinking) in the main form (form
header
section is fine). Set its control source to an expression similar to
this:

=[subform1].Form.[ControlNameWithLinkingValue]

where ControlNameWithLinkingValue is the name of the control in
subform1
that holds the value for KitItem in subform2.

Then put this in LinkChildFields property of subform2 control:
Company_ID

And put this in LinkMasterFields property of subform2 control:
txtLinking

Now the two subforms are linked.


--

Ken Snell
<MS ACCESS MVP>

My Main Form has as its Record Source the Product_Family table.
This
is
the
same table that is the Record Source for the "grandparent" control,
a
combobox. Changes in the combobox are reflected in the first subform
"Company" Which has Company Subform as its Source Object. The
controls
on
this subform are populated by Query2 which is based on the Company
table
with
reference to the Product_Family table in this WHERE clause:

WHERE
(((Company.Product_ID)=[Me]![Parent]![cboProductFamily].[Product_ID])).

So far so good. Works exactly the way I expect it to -- Company
subform
revises its content every time an item in the Product combobox
changes.

There is a table called tblGroup which manages the relationship
between
the
Product and the Company tables, which [auto-]generated a unique ID
for
each
possible combination.

The final table, called tblItems has reference to the Group Table.
In
the
Relationships, these tables are all linked together by the
appropriate
IDs:

Product_Family->Company->Group->Items

The Groups Subform has Group Subform as its Source Object, and the
Record
Source for its controls is Query1:

SELECT Group.Group_Name, Item.Item, Item.Reference_Address,
Company.Company_ID
FROM Company INNER JOIN ([Group] INNER JOIN Item ON Group.Group_ID =
Item.Group_ID) ON Company.Company_ID = Group.Company_ID;

However, when I want to link the Group Subform up to make it refresh
on
changed selection in the Company Subform, the Master/Child link
offerings
present only items from the Product_Family table for master. I need
it
to
be
looking to the Group table, or else looking to the Company_ID
determined
by
the pointer in the Company Subform.

It is frustrating to be this close, and just missing some basic
component --
have I given enough description here to allow someone to get the
drift
of
what I need?

Specifically I need the Group Subform to update when the selected
item
in
the Company Subform changes, and always to display only the Distinct
Items
related to the Product_Family/Company selections.

 

[Ken Snell \(MVP\)]

Glad you found the source of "problems".. good luck!

--

Ken Snell
<MS ACCESS MVP>

Hehehe! Sure is hard to solve a problem programmatically when the DATA is
wrong!

What I had previously was a database where Company was the top level, and
Product second, with Group last. Boss-o said that what he wanted would
require Product in first place and Company in second.

Oooooooookay. That meant that wll the hooks I had tying those first two
tables together went into the toilet, and in the process messed up the
Group
table. Now I've had a chance to look at things some more (with your
intercession helping a LOT), it has dawned on me that the Group table is
totally redundant. However I now have to add a Company_ID field to the
Items
table and make it refer to the newly revised Company table instead of the
Group table.

Fortunately I have backups of the earlier version. and fortunately (v2.0)
there are only 593 items in the Items table, but it's grunt work all the
way.

We can close the case, Inspector!

< snipped >