Requery Subform 3 from Subform 2

[CJ]

Hi groupies

I appear to be in a complete mental fog today...........

Here is a generic example of what I am trying to do:

My Main form lists each room
Subform 1 lists the closets in each room. It is used to select the closets
from a combo box.
Subform 2 lists the boxes in each closet as a continuous form
Subform 3 lists the contents of each box in each closet

Subform 1 and 2 work. When I choose a closet, I see a list of the boxes
inside.

My problem is the communication between 2 & 3.

When I click on a box in Subform 2, I want Subform 3 to show the contents of
that box but I can not get it to work. Subform 3 is only showing the
contents of the first box
in that closet.

On my main form I have a text box that obtains the value of the selected
box. Subform 3 uses this text box as filter criteria
and it is working fine when I test things with a separate query.

I need to have Subform 3 requery when I select a different box in Subform
2.......I won't list all of the things that I have
tried because I can't remember them all......sigh

Can somebody please help me out with something I KNOW is very easy.

 

[Marshall Barton]

Here is a generic example of what I am trying to do:

My Main form lists each room
Subform 1 lists the closets in each room. It is used to select the closets
from a combo box.
Subform 2 lists the boxes in each closet as a continuous form
Subform 3 lists the contents of each box in each closet

Subform 1 and 2 work. When I choose a closet, I see a list of the boxes
inside.

My problem is the communication between 2 & 3.

When I click on a box in Subform 2, I want Subform 3 to show the contents of
that box but I can not get it to work. Subform 3 is only showing the
contents of the first box in that closet.

On my main form I have a text box that obtains the value of the selected
box. Subform 3 uses this text box as filter criteria
and it is working fine when I test things with a separate query.

I need to have Subform 3 requery when I select a different box in Subform
2.

Use subform2's Current event to requery subform3:

Parent.subformcontrol2.Form.Requery

Seems like you should have the equivalent statement in
sunform1's Current event or is subform2 using its Link
Master/Child properties instead of a query criteria?

 

[CJ]

Hi Marshall

Well, I know I tried this but I tried it again and what happens is that
Subform 2 just requeries, I can't see anything happening with Sub3 and the
problem is not fixed. I do have the Child/Master properties in place for Sub
1 and Sub 2. I thought I had it correct for Sub 3 but apparently not.

I may not have made things clear, Subform 3 is on the main form, not Subform
2. Things work fine if I put Sub 3 on Sub 2 but that is not where I want it.

Any other thoughts?

 

[Joan Wild]

If you have used the form wizard to create the main form with two adjacent
subforms (even though subform 3 is a child of subform 2), it would have
created all the necessary code for you.

But here is a codeless solution:
Add a textbox to your mainform, name it txtLink. Set its controlsource to
Forms!subform2!PrimaryKeyControl
Change 'subform2' to the actual name of the control containing subform2 and
'PrimaryKeyControl' to the name of the control that contains the field that
links subform3 to subform2.

In the link Master/Child properties for the subform3 control put txtLink as
the Master and put the correct field in subform3 that links to subform2 for
the Child.

--
Joan Wild
Microsoft Access MVP

Hi Marshall

Well, I know I tried this but I tried it again and what happens is that
Subform 2 just requeries, I can't see anything happening with Sub3 and the
problem is not fixed. I do have the Child/Master properties in place for
Sub 1 and Sub 2. I thought I had it correct for Sub 3 but apparently not.

I may not have made things clear, Subform 3 is on the main form, not
Subform 2. Things work fine if I put Sub 3 on Sub 2 but that is not where
I want it.

Any other thoughts?

 

[Marshall Barton]

What, you didn't notice my obvious typo ;-)

That was supposed to requery subform3

Parent.subformcontrol3.Form.Requery

Joan's idea is another way to approach synchronizing
parrallel subform's. I hesitated to mention it because it
seemed like you were avoiding the link master/child
properties.

 

[CJ]

Well, thanks Joan.

I had everything except setting the Master to the name of the text box on
the main form. Sometimes you really can't see the forest for the trees.

Thanks for taking the time to share your knowledge.

If you have used the form wizard to create the main form with two adjacent
subforms (even though subform 3 is a child of subform 2), it would have
created all the necessary code for you.

But here is a codeless solution:
Add a textbox to your mainform, name it txtLink. Set its controlsource to
Forms!subform2!PrimaryKeyControl
Change 'subform2' to the actual name of the control containing subform2
and 'PrimaryKeyControl' to the name of the control that contains the field
that links subform3 to subform2.

In the link Master/Child properties for the subform3 control put txtLink
as the Master and put the correct field in subform3 that links to subform2
for the Child.

 

[CJ]

Hi Marsh.

No worries. I went with Joan's method because that is what I had in mind
originally......just couldn't get the details straight.

Thanks a bunch!

What, you didn't notice my obvious typo ;-)

That was supposed to requery subform3

Parent.subformcontrol3.Form.Requery

Joan's idea is another way to approach synchronizing
parrallel subform's. I hesitated to mention it because it
seemed like you were avoiding the link master/child
properties.
--
Marsh
MVP [MS Access]

Well, I know I tried this but I tried it again and what happens is that
Subform 2 just requeries, I can't see anything happening with Sub3 and the
problem is not fixed. I do have the Child/Master properties in place for
Sub
1 and Sub 2. I thought I had it correct for Sub 3 but apparently not.

I may not have made things clear, Subform 3 is on the main form, not
Subform
2. Things work fine if I put Sub 3 on Sub 2 but that is not where I want
it.