Turn subform visible and pass criteria

M

Mishanya

I have frmMain with frmSub1 (datasheet view) and frmSub2 (single view, set to
invisible). Both suforms are linked to the frmMain by the ClientID field and
have OrderID in their underlying queries.
I want to turn frmSub2 visible by double-clicking OrderDate-cell on frmSub1,
so frmSub2 would appear on the screen (as part of the same main form) with
the same record-set as the clicked one.

So far I've been able to turn frmSub2 visible without setting criteria
(turns visible with paging option):

Private Sub OrderDate_DblClick(Cancel As Integer)
Forms![frmMain]![frmSub2].Visible = Not Forms![frmMain]![frmSub2].Visible
End Sub

or seting the criteria allright though opening frmSub2 in the new window
(opens filtered with only one page):

Private Sub OrderDate_DblClick(Cancel As Integer)
On Error GoTo Err_OrderDate_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmSub2"

stLinkCriteria = "[OrderID]=" & Me![OrderID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_OrderDate_Click:
Exit Sub

Err_OrderDate_Click:
MsgBox Err.Description

End Sub

I'll appriciate help with combining those two: setting the criteria for
frmSub2 as
the OrderID record-set of the clicked record and visualisation it in the same
frmMain.
 
M

Mishanya

Thank You for an alternative! I've thought of it, but eventually chosen the
blank space option. Or do You mean making the 1st tab blank, so it covers the
subform2' tab?
Still, is it possible to envoke subform2 visiblity on criteria, "drawn" from
subform1?

Jeff Boyce said:
Mishanya

Another alternative to leaving a (?large) blank space on your screen (room
for the frmSub2 to appear) might be to use a tab control and put frmSub2 on
one of the tab pages.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Mishanya said:
I have frmMain with frmSub1 (datasheet view) and frmSub2 (single view, set to
invisible). Both suforms are linked to the frmMain by the ClientID field and
have OrderID in their underlying queries.
I want to turn frmSub2 visible by double-clicking OrderDate-cell on frmSub1,
so frmSub2 would appear on the screen (as part of the same main form) with
the same record-set as the clicked one.

So far I've been able to turn frmSub2 visible without setting criteria
(turns visible with paging option):

Private Sub OrderDate_DblClick(Cancel As Integer)
Forms![frmMain]![frmSub2].Visible = Not Forms![frmMain]![frmSub2].Visible
End Sub

or seting the criteria allright though opening frmSub2 in the new window
(opens filtered with only one page):

Private Sub OrderDate_DblClick(Cancel As Integer)
On Error GoTo Err_OrderDate_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmSub2"

stLinkCriteria = "[OrderID]=" & Me![OrderID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_OrderDate_Click:
Exit Sub

Err_OrderDate_Click:
MsgBox Err.Description

End Sub

I'll appriciate help with combining those two: setting the criteria for
frmSub2 as
the OrderID record-set of the clicked record and visualisation it in the same
frmMain.
Click to expand...
Click to expand...
 
M

Mishanya

Great. What about passing criteria? Can I pass it thru the same event?

Jeff Boyce said:
If your frmSub1 needs to show first, put it on the first tab.

If your frmSub2 doesn't need to show at first, put it on the second tab.

Some users are disconcerted when things pop into existence, so consider
using .Enabled instead of .Visible property.

You can add code to an event (maybe the Click or Double-Click event of the
frmSub1 control on the tab page) that will set:
Me!frmSub2Control.Enabled = True

Good luck!

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Mishanya said:
Thank You for an alternative! I've thought of it, but eventually chosen the
blank space option. Or do You mean making the 1st tab blank, so it covers the
subform2' tab?
Still, is it possible to envoke subform2 visiblity on criteria, "drawn" from
subform1?

Jeff Boyce said:
Mishanya

Another alternative to leaving a (?large) blank space on your screen (room
for the frmSub2 to appear) might be to use a tab control and put frmSub2 on
one of the tab pages.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

I have frmMain with frmSub1 (datasheet view) and frmSub2 (single view, set
to
invisible). Both suforms are linked to the frmMain by the ClientID field
and
have OrderID in their underlying queries.
I want to turn frmSub2 visible by double-clicking OrderDate-cell on
frmSub1,
so frmSub2 would appear on the screen (as part of the same main form) with
the same record-set as the clicked one.

So far I've been able to turn frmSub2 visible without setting criteria
(turns visible with paging option):

Private Sub OrderDate_DblClick(Cancel As Integer)
Forms![frmMain]![frmSub2].Visible = Not
Forms![frmMain]![frmSub2].Visible
End Sub

or seting the criteria allright though opening frmSub2 in the new window
(opens filtered with only one page):

Private Sub OrderDate_DblClick(Cancel As Integer)
On Error GoTo Err_OrderDate_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmSub2"

stLinkCriteria = "[OrderID]=" & Me![OrderID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_OrderDate_Click:
Exit Sub

Err_OrderDate_Click:
MsgBox Err.Description

End Sub

I'll appriciate help with combining those two: setting the criteria for
frmSub2 as
the OrderID record-set of the clicked record and visualisation it in the
same
frmMain.
Click to expand...
Click to expand...
Click to expand...
 
J

Jeff Boyce

If your frmSub1 needs to show first, put it on the first tab.

If your frmSub2 doesn't need to show at first, put it on the second tab.

Some users are disconcerted when things pop into existence, so consider
using .Enabled instead of .Visible property.

You can add code to an event (maybe the Click or Double-Click event of the
frmSub1 control on the tab page) that will set:
Me!frmSub2Control.Enabled = True

Good luck!

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Mishanya said:
Thank You for an alternative! I've thought of it, but eventually chosen the
blank space option. Or do You mean making the 1st tab blank, so it covers the
subform2' tab?
Still, is it possible to envoke subform2 visiblity on criteria, "drawn" from
subform1?

Jeff Boyce said:
Mishanya

Another alternative to leaving a (?large) blank space on your screen (room
for the frmSub2 to appear) might be to use a tab control and put frmSub2 on
one of the tab pages.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Mishanya said:
I have frmMain with frmSub1 (datasheet view) and frmSub2 (single view,
Click to expand...
set
to
invisible). Both suforms are linked to the frmMain by the ClientID
Click to expand...
field
and
have OrderID in their underlying queries.
I want to turn frmSub2 visible by double-clicking OrderDate-cell on frmSub1,
so frmSub2 would appear on the screen (as part of the same main form) with
the same record-set as the clicked one.

So far I've been able to turn frmSub2 visible without setting criteria
(turns visible with paging option):

Private Sub OrderDate_DblClick(Cancel As Integer)
Forms![frmMain]![frmSub2].Visible = Not Forms![frmMain]![frmSub2].Visible
End Sub

or seting the criteria allright though opening frmSub2 in the new window
(opens filtered with only one page):

Private Sub OrderDate_DblClick(Cancel As Integer)
On Error GoTo Err_OrderDate_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmSub2"

stLinkCriteria = "[OrderID]=" & Me![OrderID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_OrderDate_Click:
Exit Sub

Err_OrderDate_Click:
MsgBox Err.Description

End Sub

I'll appriciate help with combining those two: setting the criteria for
frmSub2 as
the OrderID record-set of the clicked record and visualisation it in
Click to expand...
the
same
frmMain.
Click to expand...
Click to expand...
Click to expand...
 
J

Jeff Boyce

I don't understand ... why do you think you need to "pass criteria"? If you
have a main form/subform construction, the subform control keeps track of
which record is on the main form and displays accordingly.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Mishanya said:
Great. What about passing criteria? Can I pass it thru the same event?

Jeff Boyce said:
If your frmSub1 needs to show first, put it on the first tab.

If your frmSub2 doesn't need to show at first, put it on the second tab.

Some users are disconcerted when things pop into existence, so consider
using .Enabled instead of .Visible property.

You can add code to an event (maybe the Click or Double-Click event of the
frmSub1 control on the tab page) that will set:
Me!frmSub2Control.Enabled = True

Good luck!

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Mishanya said:
Thank You for an alternative! I've thought of it, but eventually
Click to expand...
chosen
the
blank space option. Or do You mean making the 1st tab blank, so it
Click to expand...
covers
the
subform2' tab?
Still, is it possible to envoke subform2 visiblity on criteria,
Click to expand...
"drawn"
from
subform1?

:

Mishanya

Another alternative to leaving a (?large) blank space on your screen (room
for the frmSub2 to appear) might be to use a tab control and put frm
Click to expand...
Sub2
on
one of the tab pages.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

I have frmMain with frmSub1 (datasheet view) and frmSub2 (single
Click to expand...
view,
set
to
invisible). Both suforms are linked to the frmMain by the ClientID field
and
have OrderID in their underlying queries.
I want to turn frmSub2 visible by double-clicking OrderDate-cell on
frmSub1,
so frmSub2 would appear on the screen (as part of the same main
Click to expand...
form)
with
the same record-set as the clicked one.

So far I've been able to turn frmSub2 visible without setting criteria
(turns visible with paging option):

Private Sub OrderDate_DblClick(Cancel As Integer)
Forms![frmMain]![frmSub2].Visible = Not
Forms![frmMain]![frmSub2].Visible
End Sub

or seting the criteria allright though opening frmSub2 in the new window
(opens filtered with only one page):

Private Sub OrderDate_DblClick(Cancel As Integer)
On Error GoTo Err_OrderDate_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmSub2"

stLinkCriteria = "[OrderID]=" & Me![OrderID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_OrderDate_Click:
Exit Sub

Err_OrderDate_Click:
MsgBox Err.Description

End Sub

I'll appriciate help with combining those two: setting the
Click to expand...
criteria
for
frmSub2 as
the OrderID record-set of the clicked record and visualisation it
Click to expand...
in
the
same
frmMain.
Click to expand...
Click to expand...
Click to expand...
 
M

Mishanya

Hi Jeff.
As I'v tried to explain,both the Subforms are linked to the ParentForm, but
I needed to control the Subform2 criteria from the SubForm1 (as SubForm2 is a
single-form detailed presentation of each row/recordset in the
Subform1-datasheet.
Eventually I've worked it out as follows:

DblClick Event of SubForm1' OrderID-control:

Dim f as Form
Set f = Parent!Subform2.Form 'reference to the SubForm2

f.Filter = "OrderID = " & Me!OrderID
f.FilterOn = True 'pass criteria to the SubForm2

Parent!Subform2.Visible = True 'make the SubForm2 visible


Still, thank You for Your effort!

Jeff Boyce said:
I don't understand ... why do you think you need to "pass criteria"? If you
have a main form/subform construction, the subform control keeps track of
which record is on the main form and displays accordingly.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Mishanya said:
Great. What about passing criteria? Can I pass it thru the same event?

Jeff Boyce said:
If your frmSub1 needs to show first, put it on the first tab.

If your frmSub2 doesn't need to show at first, put it on the second tab.

Some users are disconcerted when things pop into existence, so consider
using .Enabled instead of .Visible property.

You can add code to an event (maybe the Click or Double-Click event of the
frmSub1 control on the tab page) that will set:
Me!frmSub2Control.Enabled = True

Good luck!

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Thank You for an alternative! I've thought of it, but eventually chosen
the
blank space option. Or do You mean making the 1st tab blank, so it covers
the
subform2' tab?
Still, is it possible to envoke subform2 visiblity on criteria, "drawn"
from
subform1?

:

Mishanya

Another alternative to leaving a (?large) blank space on your screen
(room
for the frmSub2 to appear) might be to use a tab control and put frm Sub2
on
one of the tab pages.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

I have frmMain with frmSub1 (datasheet view) and frmSub2 (single view,
set
to
invisible). Both suforms are linked to the frmMain by the ClientID
field
and
have OrderID in their underlying queries.
I want to turn frmSub2 visible by double-clicking OrderDate-cell on
frmSub1,
so frmSub2 would appear on the screen (as part of the same main form)
with
the same record-set as the clicked one.

So far I've been able to turn frmSub2 visible without setting criteria
(turns visible with paging option):

Private Sub OrderDate_DblClick(Cancel As Integer)
Forms![frmMain]![frmSub2].Visible = Not
Forms![frmMain]![frmSub2].Visible
End Sub

or seting the criteria allright though opening frmSub2 in the new
window
(opens filtered with only one page):

Private Sub OrderDate_DblClick(Cancel As Integer)
On Error GoTo Err_OrderDate_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmSub2"

stLinkCriteria = "[OrderID]=" & Me![OrderID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_OrderDate_Click:
Exit Sub

Err_OrderDate_Click:
MsgBox Err.Description

End Sub

I'll appriciate help with combining those two: setting the criteria
for
frmSub2 as
the OrderID record-set of the clicked record and visualisation it in
the
same
frmMain.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Code for turning a subform visible with criteria 6
Passing criteria between subforms 4
Pass value to form to subform to subform 2
problem pass values in form to subform to subform 1
DoCmd.OpenForm question 3
link criteria VB 2
Use values from form & subform in stlinkcriteria 3
why do I get an error 1

Top