Passing criteria between subforms

[Mishanya]

I have mainForm with two subforms, linked to the main by ClientID. The
subForm1 is datasheet-view (Orders), while subForm2 is single-view and its
recordsets (Order details) relevant to the subForm1 rows (it gives more
detailed/differently queried look on each row-recordset).
I want to programm subForm1 rows (one of its fields) so dblclicking it would
pass criteria to the subForm2 and make it show the relevant recordset
(OrderID).
The procedure
stLinkCriteria = "[OrderID]=" & Me![OrderID]
DoCmd.OpenForm stDocName, , , stLinkCriteria
do the job of passing the criteria fine, but opens the subForm2 in new window.

1) How can I just "refresh" the recordset of already loaded subForm2 by
clicking row in the subForm1?
2) If I want to have the subForm2 hidden until the subForm1 is clicked, can
incorporate Forms![frmMain]![frmSub2].Visible in the same event?

 

[Scott Lichtenberg]

Mishanya,

The trick is to understand how to refer to objects in your form. Here are a
couple of ways to access data in subforms.

1. From your main form, access data in a subform. This code declares and
opens a variable that represents the subform, then sets it's recordsource.
The "MySubformControl" is the name of the control on your main form that
contains the subform. It is not the name of the subform as it appears in
the database window. You need to use the .Form property to indicate that
your variable represents the subform itself, rather than just the subform
control on your main form.

Dim f as Form
Set f = Me!MySubformControl.Form
f.Recordsource = "SELECT * FROM MyTable WHERE MyField = " & Me!cboField1


2. If you are in a subform, and want to refer to another subform on your
main form, try this:

Dim f as Form
Set f = Parent!Subform2.Form 'where Subform2 is the name
of the control
f.Recordsource = "SELECT ..."

3. If you are looking to make a subform visible/invisible, you'll need to
refer to the control on the main form. You're not actually making the
subform itself invisible. You are changing the property of the control that
holds the subform.

Me!Subform1.Visible = False
Parent!Subform2.Visible = False 'if you are in one subform and
want to hide the other one.

Hope this helps

 

[Mishanya]

Scott, thank You for the detailed answer.
I'm Access-rookie, so may be You could kindly help me to implement Your
advise.

Both the SubForms are linked to the MainForm (e.g. they show data filtered
for ClientiD that is chosen in the MainForm cboClientName).
The Subform1 is datasheet with OrderID control as one of its row-controls
(each row is a different order).
The Subform2 is single-form based on query that contains OrderID (each page
is a different order). It is hidden (VisibleProperty is set to NO)
I want to dblclick SubForm1' OrderID control and to 1) turn the SubForm2
visible while 2) showing the relevant OrderID page.

I've never used the code that You mentioned. Can You please put it together
for the dblClick event?

Mishanya,

The trick is to understand how to refer to objects in your form. Here are a
couple of ways to access data in subforms.

1. From your main form, access data in a subform. This code declares and
opens a variable that represents the subform, then sets it's recordsource.
The "MySubformControl" is the name of the control on your main form that
contains the subform. It is not the name of the subform as it appears in
the database window. You need to use the .Form property to indicate that
your variable represents the subform itself, rather than just the subform
control on your main form.

Dim f as Form
Set f = Me!MySubformControl.Form
f.Recordsource = "SELECT * FROM MyTable WHERE MyField = " & Me!cboField1


2. If you are in a subform, and want to refer to another subform on your
main form, try this:

Dim f as Form
Set f = Parent!Subform2.Form 'where Subform2 is the name
of the control
f.Recordsource = "SELECT ..."

3. If you are looking to make a subform visible/invisible, you'll need to
refer to the control on the main form. You're not actually making the
subform itself invisible. You are changing the property of the control that
holds the subform.

Me!Subform1.Visible = False
Parent!Subform2.Visible = False 'if you are in one subform and
want to hide the other one.

Hope this helps

I have mainForm with two subforms, linked to the main by ClientID. The
subForm1 is datasheet-view (Orders), while subForm2 is single-view and its
recordsets (Order details) relevant to the subForm1 rows (it gives more
detailed/differently queried look on each row-recordset).
I want to programm subForm1 rows (one of its fields) so dblclicking it
would
pass criteria to the subForm2 and make it show the relevant recordset
(OrderID).
The procedure
stLinkCriteria = "[OrderID]=" & Me![OrderID]
DoCmd.OpenForm stDocName, , , stLinkCriteria
do the job of passing the criteria fine, but opens the subForm2 in new
window.

1) How can I just "refresh" the recordset of already loaded subForm2 by
clicking row in the subForm1?
2) If I want to have the subForm2 hidden until the subForm1 is clicked,
can
incorporate Forms![frmMain]![frmSub2].Visible in the same event?

 

[Scott Lichtenberg]

Mishanya,

Here's some code that should get you going. There are a lot of different
ways to do what you want. I've tried to do it in a way that will require
you to make the least number of changes to what you currently have. Put
this code in the OnClick or OnDoubleClick event of Subform1

You may have to change some field names, etc.


Dim f as Form
Set f = Parent!Subform2.Form 'This is how you refer to the other
subform

f.Filter = "OrderID = " & Me!OrderID
f.FilterOn = True

Parent!Subform2.Visible = True 'You are making the control visible,
not the subform itself.


Hope this helps

Scott, thank You for the detailed answer.
I'm Access-rookie, so may be You could kindly help me to implement Your
advise.

Both the SubForms are linked to the MainForm (e.g. they show data filtered
for ClientiD that is chosen in the MainForm cboClientName).
The Subform1 is datasheet with OrderID control as one of its row-controls
(each row is a different order).
The Subform2 is single-form based on query that contains OrderID (each
page
is a different order). It is hidden (VisibleProperty is set to NO)
I want to dblclick SubForm1' OrderID control and to 1) turn the SubForm2
visible while 2) showing the relevant OrderID page.

I've never used the code that You mentioned. Can You please put it
together
for the dblClick event?

Mishanya,

The trick is to understand how to refer to objects in your form. Here
are a
couple of ways to access data in subforms.

1. From your main form, access data in a subform. This code declares
and
opens a variable that represents the subform, then sets it's
recordsource.
The "MySubformControl" is the name of the control on your main form that
contains the subform. It is not the name of the subform as it appears in
the database window. You need to use the .Form property to indicate that
your variable represents the subform itself, rather than just the subform
control on your main form.

Dim f as Form
Set f = Me!MySubformControl.Form
f.Recordsource = "SELECT * FROM MyTable WHERE MyField = " &
Me!cboField1


2. If you are in a subform, and want to refer to another subform on your
main form, try this:

Dim f as Form
Set f = Parent!Subform2.Form 'where Subform2 is the
name
of the control
f.Recordsource = "SELECT ..."

3. If you are looking to make a subform visible/invisible, you'll need
to
refer to the control on the main form. You're not actually making the
subform itself invisible. You are changing the property of the control
that
holds the subform.

Me!Subform1.Visible = False
Parent!Subform2.Visible = False 'if you are in one subform and
want to hide the other one.

Hope this helps

I have mainForm with two subforms, linked to the main by ClientID. The
subForm1 is datasheet-view (Orders), while subForm2 is single-view and
its
recordsets (Order details) relevant to the subForm1 rows (it gives more
detailed/differently queried look on each row-recordset).
I want to programm subForm1 rows (one of its fields) so dblclicking it
would
pass criteria to the subForm2 and make it show the relevant recordset
(OrderID).
The procedure
stLinkCriteria = "[OrderID]=" & Me![OrderID]
DoCmd.OpenForm stDocName, , , stLinkCriteria
do the job of passing the criteria fine, but opens the subForm2 in new
window.

1) How can I just "refresh" the recordset of already loaded subForm2 by
clicking row in the subForm1?
2) If I want to have the subForm2 hidden until the subForm1 is clicked,
can
incorporate Forms![frmMain]![frmSub2].Visible in the same event?

 

[Mishanya]

I'm very gratful, Scott!
Took me 2 weeks of battering against the wall.
It will take some more to understand why instead of Parent!Subform2.Form I
had to use Forms![Parent]![SubForm2].Form, but stil, now it works.
Thanks a lot!

Mishanya,

Here's some code that should get you going. There are a lot of different
ways to do what you want. I've tried to do it in a way that will require
you to make the least number of changes to what you currently have. Put
this code in the OnClick or OnDoubleClick event of Subform1

You may have to change some field names, etc.


Dim f as Form
Set f = Parent!Subform2.Form 'This is how you refer to the other
subform

f.Filter = "OrderID = " & Me!OrderID
f.FilterOn = True

Parent!Subform2.Visible = True 'You are making the control visible,
not the subform itself.


Hope this helps

Scott, thank You for the detailed answer.
I'm Access-rookie, so may be You could kindly help me to implement Your
advise.

Both the SubForms are linked to the MainForm (e.g. they show data filtered
for ClientiD that is chosen in the MainForm cboClientName).
The Subform1 is datasheet with OrderID control as one of its row-controls
(each row is a different order).
The Subform2 is single-form based on query that contains OrderID (each
page
is a different order). It is hidden (VisibleProperty is set to NO)
I want to dblclick SubForm1' OrderID control and to 1) turn the SubForm2
visible while 2) showing the relevant OrderID page.

I've never used the code that You mentioned. Can You please put it
together
for the dblClick event?

Mishanya,

The trick is to understand how to refer to objects in your form. Here
are a
couple of ways to access data in subforms.

1. From your main form, access data in a subform. This code declares
and
opens a variable that represents the subform, then sets it's
recordsource.
The "MySubformControl" is the name of the control on your main form that
contains the subform. It is not the name of the subform as it appears in
the database window. You need to use the .Form property to indicate that
your variable represents the subform itself, rather than just the subform
control on your main form.

Dim f as Form
Set f = Me!MySubformControl.Form
f.Recordsource = "SELECT * FROM MyTable WHERE MyField = " &
Me!cboField1


2. If you are in a subform, and want to refer to another subform on your
main form, try this:

Dim f as Form
Set f = Parent!Subform2.Form 'where Subform2 is the
name
of the control
f.Recordsource = "SELECT ..."

3. If you are looking to make a subform visible/invisible, you'll need
to
refer to the control on the main form. You're not actually making the
subform itself invisible. You are changing the property of the control
that
holds the subform.

Me!Subform1.Visible = False
Parent!Subform2.Visible = False 'if you are in one subform and
want to hide the other one.

Hope this helps





I have mainForm with two subforms, linked to the main by ClientID. The
subForm1 is datasheet-view (Orders), while subForm2 is single-view and
its
recordsets (Order details) relevant to the subForm1 rows (it gives more
detailed/differently queried look on each row-recordset).
I want to programm subForm1 rows (one of its fields) so dblclicking it
would
pass criteria to the subForm2 and make it show the relevant recordset
(OrderID).
The procedure
stLinkCriteria = "[OrderID]=" & Me![OrderID]
DoCmd.OpenForm stDocName, , , stLinkCriteria
do the job of passing the criteria fine, but opens the subForm2 in new
window.

1) How can I just "refresh" the recordset of already loaded subForm2 by
clicking row in the subForm1?
2) If I want to have the subForm2 hidden until the subForm1 is clicked,
can
incorporate Forms![frmMain]![frmSub2].Visible in the same event?