Code for turning a subform visible with criteria

[Mishanya]

I have frmMain with frmSub1 (datasheet) and frmSub2 (single, 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 and OrderID-wise filter it by double-clicking
OrderDate value on frmSub1.

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

Please help with combining those two: setting the criteria for frmSub2 as
the OrderID value of the clicked record and visualisation it in the same
frmMain.

 

[vbasean]

If both forms are based off of tables/queries that have a child field
([OrderID])
1) add both sub forms like you would any other
2) add a frame with two toggle buttons for each sub form
your frame will have two values, such as 1 and 2 (for this example) and two
captions "SubForm1" "SubForm2" (whatever meaningful names of your subforms
are)
3) on the form load event have the frame default to one value and hide the
other form
me.framename = 1 ' default value
me.subform1.visible = false ' this if the form hidden at first
me.subform2.visible = true ' this is the default form shown at first

on the 'After_Update' event of the frame you show and hide respectively

Select Case me.framename
Case 1
me.subform2.visible = false ' this if the form to be hidden
me.subform1.visible = true ' this is the form to be shown
Case 2
me.subform1.visible = false ' this if the form to be hidden
me.subform2.visible = true ' this is the form to be shown
End Select

 

[vbasean]

I realized my answer wasn't exactly what you were looking for.

what I don't understand is opening a sub form.

If you are just trying to open a form based on criteria from another
you pass the openargs like you have now BUT
on the form load event you FILTER the form based off that open arg

in the form load event:
me.Filter = OpenArgs
me.FilterOn = True

 

[Mishanya]

Hi Chris. FoA thanks for Your answer.
I'm not trying to "open" subForm2. It's open, but hidden (VisibleProperty
set to "NO"). It's recordset is already filtered for ClientID of the "mother"
MainForm and it has single view (for every client there are one to several
entries), so when it is visible I can page between the entries for the
choosen in the mother-Mainform client.
Now, the subForm1 is visible all the time, also filtered for ClientID of the
"mother" MainForm and it has datasheet view. The q-ty of rows is equal to the
q-ty of entries in the subForm2 (the rows fit to the pages of subForm2).
So, I want to click on any row (one of its fields) in order to turn subForm2
visible and filtered for the same row as I ckicked.
It's simple to add DoCmd OpenForm to the click event and open subForm2 in
new window filtered for the clicked recordset. It's also simple to add
Forms![MainForm]![subForm2].Visible = True to the click event so the subForm2
would turn visible, though not filtered for the clicked recordset. I want to
trick it so the click will make the subForm2 visible and filtered (the
clicked ValueID will be passed to the subForm2 ).
Sounds simple and, apparently, does not require Openargs nor some cumbersome
code. Just need to put up some procedure with stLinkCriteria = "[ValueID]=" &
Me![ValueID] and Forms![MainForm]![subForm2].Visible = True combined.
What do You say?

I realized my answer wasn't exactly what you were looking for.

what I don't understand is opening a sub form.

If you are just trying to open a form based on criteria from another
you pass the openargs like you have now BUT
on the form load event you FILTER the form based off that open arg

in the form load event:
me.Filter = OpenArgs
me.FilterOn = True

--
~Your Friend Chris
http://myvbastuff.blogspot.com/
thinking out loud

I have frmMain with frmSub1 (datasheet) and frmSub2 (single, 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 and OrderID-wise filter it by double-clicking
OrderDate value on frmSub1.

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

Please help with combining those two: setting the criteria for frmSub2 as
the OrderID value of the clicked record and visualisation it in the same
frmMain.

 

[vbasean]

This is my opinion but I think you're going about it the wrong way.

Opening a form in the background and hiding it just to show it and filter it
(or go to a certain index) doesn't seem to be the best aproach.

This is why a form has openargs, so you can command it to do something
unique when you open it. In this case, filter it (OR EVEN reset the entire
recordsource.)

It's not very difficult to pull this trick off.
1) you place the filter criteria in the form load of the form to open (In
your case, the form you are currently hiding)

in the form load sub:
if me.openargs <> "" then 'if the form was passed openargs string
me.filter = openargs 'in this case, you already are passing a valid filter
string i.e. "[field] = [value]"
me.filteron = true

and that's it, that's really all you need to do:
1) get rid of opening the forms together
2) keep your double click event as is
3) add the above code as mentioned
--
~Your Friend Chris
http://myvbastuff.blogspot.com/
thinking out loud

Hi Chris. FoA thanks for Your answer.
I'm not trying to "open" subForm2. It's open, but hidden (VisibleProperty
set to "NO"). It's recordset is already filtered for ClientID of the "mother"
MainForm and it has single view (for every client there are one to several
entries), so when it is visible I can page between the entries for the
choosen in the mother-Mainform client.
Now, the subForm1 is visible all the time, also filtered for ClientID of the
"mother" MainForm and it has datasheet view. The q-ty of rows is equal to the
q-ty of entries in the subForm2 (the rows fit to the pages of subForm2).
So, I want to click on any row (one of its fields) in order to turn subForm2
visible and filtered for the same row as I ckicked.
It's simple to add DoCmd OpenForm to the click event and open subForm2 in
new window filtered for the clicked recordset. It's also simple to add
Forms![MainForm]![subForm2].Visible = True to the click event so the subForm2
would turn visible, though not filtered for the clicked recordset. I want to
trick it so the click will make the subForm2 visible and filtered (the
clicked ValueID will be passed to the subForm2 ).
Sounds simple and, apparently, does not require Openargs nor some cumbersome
code. Just need to put up some procedure with stLinkCriteria = "[ValueID]=" &
Me![ValueID] and Forms![MainForm]![subForm2].Visible = True combined.
What do You say?

I realized my answer wasn't exactly what you were looking for.

what I don't understand is opening a sub form.

If you are just trying to open a form based on criteria from another
you pass the openargs like you have now BUT
on the form load event you FILTER the form based off that open arg

in the form load event:
me.Filter = OpenArgs
me.FilterOn = True

--
~Your Friend Chris
http://myvbastuff.blogspot.com/
thinking out loud

I have frmMain with frmSub1 (datasheet) and frmSub2 (single, 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 and OrderID-wise filter it by double-clicking
OrderDate value on frmSub1.

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

Please help with combining those two: setting the criteria for frmSub2 as
the OrderID value of the clicked record and visualisation it in the same
frmMain.

 

[vbasean]

oh, and you'll have to filter by ID
so

stLinkCriteria = "[OrderID]=" & Me![OrderID] & _
" AND [OrderDate] = " & me.OrderDate
DoCmd.OpenForm stDocName, ,acDialog , stLinkCriteria ' open it in dialog mode
' to ensure the user doesn't fool with the main form

--
~Your Friend Chris
http://myvbastuff.blogspot.com/
thinking out loud

This is my opinion but I think you're going about it the wrong way.

Opening a form in the background and hiding it just to show it and filter it
(or go to a certain index) doesn't seem to be the best aproach.

This is why a form has openargs, so you can command it to do something
unique when you open it. In this case, filter it (OR EVEN reset the entire
recordsource.)

It's not very difficult to pull this trick off.
1) you place the filter criteria in the form load of the form to open (In
your case, the form you are currently hiding)

in the form load sub:
if me.openargs <> "" then 'if the form was passed openargs string
me.filter = openargs 'in this case, you already are passing a valid filter
string i.e. "[field] = [value]"
me.filteron = true

and that's it, that's really all you need to do:
1) get rid of opening the forms together
2) keep your double click event as is
3) add the above code as mentioned
--
~Your Friend Chris
http://myvbastuff.blogspot.com/
thinking out loud

Hi Chris. FoA thanks for Your answer.
I'm not trying to "open" subForm2. It's open, but hidden (VisibleProperty
set to "NO"). It's recordset is already filtered for ClientID of the "mother"
MainForm and it has single view (for every client there are one to several
entries), so when it is visible I can page between the entries for the
choosen in the mother-Mainform client.
Now, the subForm1 is visible all the time, also filtered for ClientID of the
"mother" MainForm and it has datasheet view. The q-ty of rows is equal to the
q-ty of entries in the subForm2 (the rows fit to the pages of subForm2).
So, I want to click on any row (one of its fields) in order to turn subForm2
visible and filtered for the same row as I ckicked.
It's simple to add DoCmd OpenForm to the click event and open subForm2 in
new window filtered for the clicked recordset. It's also simple to add
Forms![MainForm]![subForm2].Visible = True to the click event so the subForm2
would turn visible, though not filtered for the clicked recordset. I want to
trick it so the click will make the subForm2 visible and filtered (the
clicked ValueID will be passed to the subForm2 ).
Sounds simple and, apparently, does not require Openargs nor some cumbersome
code. Just need to put up some procedure with stLinkCriteria = "[ValueID]=" &
Me![ValueID] and Forms![MainForm]![subForm2].Visible = True combined.
What do You say?

I realized my answer wasn't exactly what you were looking for.

what I don't understand is opening a sub form.

If you are just trying to open a form based on criteria from another
you pass the openargs like you have now BUT
on the form load event you FILTER the form based off that open arg

in the form load event:
me.Filter = OpenArgs
me.FilterOn = True

--
~Your Friend Chris
http://myvbastuff.blogspot.com/
thinking out loud


:

I have frmMain with frmSub1 (datasheet) and frmSub2 (single, 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 and OrderID-wise filter it by double-clicking
OrderDate value on frmSub1.

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

Please help with combining those two: setting the criteria for frmSub2 as
the OrderID value of the clicked record and visualisation it in the same
frmMain.

 

[Mishanya]

Thanks again for Your patience!
I build the application for some really distructed buddy, so I want to
prevent him from opening multiple forms (he wouldn't find them later). That's
why I stuff all the read-only forms with client-related data as subforms.
I've planned some gross workplace window with toggled subforms.

Still, I'm really new, so I can't quite understand some of Your lines. What
do You mean by "you place the filter criteria in the form load of the form to
open "? Can't it be just combining "Forms![MainForm]![subForm2].Visible =
True" and "stLinkCriteria = "[OrderID]=" & Me![OrderID]" +" "DoCmd.OpenForm
stDocName, , , stLinkCriteria" (only w/o Open) in the same event? They work
fine one by one.
May You put the needed code just indicating wich form (1st ot 2nd) and what
event it is related to?
Appriciate Your help and hope for You forbearance with dummies

This is my opinion but I think you're going about it the wrong way.

Opening a form in the background and hiding it just to show it and filter it
(or go to a certain index) doesn't seem to be the best aproach.

This is why a form has openargs, so you can command it to do something
unique when you open it. In this case, filter it (OR EVEN reset the entire
recordsource.)

It's not very difficult to pull this trick off.
1) you place the filter criteria in the form load of the form to open (In
your case, the form you are currently hiding)

in the form load sub:
if me.openargs <> "" then 'if the form was passed openargs string
me.filter = openargs 'in this case, you already are passing a valid filter
string i.e. "[field] = [value]"
me.filteron = true

and that's it, that's really all you need to do:
1) get rid of opening the forms together
2) keep your double click event as is
3) add the above code as mentioned
--
~Your Friend Chris
http://myvbastuff.blogspot.com/
thinking out loud

Hi Chris. FoA thanks for Your answer.
I'm not trying to "open" subForm2. It's open, but hidden (VisibleProperty
set to "NO"). It's recordset is already filtered for ClientID of the "mother"
MainForm and it has single view (for every client there are one to several
entries), so when it is visible I can page between the entries for the
choosen in the mother-Mainform client.
Now, the subForm1 is visible all the time, also filtered for ClientID of the
"mother" MainForm and it has datasheet view. The q-ty of rows is equal to the
q-ty of entries in the subForm2 (the rows fit to the pages of subForm2).
So, I want to click on any row (one of its fields) in order to turn subForm2
visible and filtered for the same row as I ckicked.
It's simple to add DoCmd OpenForm to the click event and open subForm2 in
new window filtered for the clicked recordset. It's also simple to add
Forms![MainForm]![subForm2].Visible = True to the click event so the subForm2
would turn visible, though not filtered for the clicked recordset. I want to
trick it so the click will make the subForm2 visible and filtered (the
clicked ValueID will be passed to the subForm2 ).
Sounds simple and, apparently, does not require Openargs nor some cumbersome
code. Just need to put up some procedure with stLinkCriteria = "[ValueID]=" &
Me![ValueID] and Forms![MainForm]![subForm2].Visible = True combined.
What do You say?

I realized my answer wasn't exactly what you were looking for.

what I don't understand is opening a sub form.

If you are just trying to open a form based on criteria from another
you pass the openargs like you have now BUT
on the form load event you FILTER the form based off that open arg

in the form load event:
me.Filter = OpenArgs
me.FilterOn = True

--
~Your Friend Chris
http://myvbastuff.blogspot.com/
thinking out loud


:

I have frmMain with frmSub1 (datasheet) and frmSub2 (single, 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 and OrderID-wise filter it by double-clicking
OrderDate value on frmSub1.

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

Please help with combining those two: setting the criteria for frmSub2 as
the OrderID value of the clicked record and visualisation it in the same
frmMain.