Q: tryin' to filter a subform opens the subform on it's own

  • Thread starter Thread starter Jen
  • Start date Start date
J

Jen

I'we made a small simple sample database so that I could better explain my
problem.

I have one table, "tbl_main" that contains 2 columns 1:"id", 2:"name".
I also have 3 forms, 1:"frm_test", 2:"frm_pick", 3:"frm_big" .

The first form simply shows the names in at textbox and is bound to my table
"tbl_main" described earlier. On this form I also have a button opening my
second form "frm_pick".

My second form "frm_pick" is an unbound form with a textbox for writing in
data and a button that opens the first form "frm_test" filtered by that data
and finally closing the second form "frm_pick". The code behind this buttons
onclick event is:

Private Sub cmd_open_filtered_Click()
On Error GoTo Err_cmd_open_filtered_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frm_test"

stLinkCriteria = "[name]=" & "'" & Me![txt_name] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.Close acForm, Me.name

Exit_cmd_open_filtered_Click:

Exit Sub

Err_cmd_open_filtered_Click:
MsgBox Err.Description
Resume Exit_cmd_open_filtered_Click

frm.Visible = False

End Sub

Everything works ok so far.

In my third form, the unbound form "frm_big" I have a tab control "TabCtl0".
On the second tab (just because I have it like this on my real database in
where I have the problem) I have "frm_test" embedded as a subform. Now when
I click on this forms button that opens my second filtering form "frm_pick"
still everything is ok, the filtering form is opened. Here's my problem:
After writing in a value in the "frm_pick" and hitting the commandbutton
launching the code shown earlier, instead of showing the subform "frm_test"
filtered to whatever name I entered, "frm_test" opens as an own form,
filtered ok, leaving me with two "frm_test" open, the subform and a new
filtered one. I want the "frm_test" embedded as a subform in the tab control
being the one filtered and not have a seconf "frm_test" opened.

This I can't figure out how to do and would need help from you guys. Jen.
 
Not sure I've followed all that you're doing, but to filter the subform, you
need to set the subform's Filter property to a "WHERE"-type string and then
apply the filter.

Something like this:

Me.Subform.Form.Filter = "[FieldName] = " & SomeValue
Me.Subform.Form.FilterOn = True
 
If I try to explain a little shorter;

From a form I open a second form. Based on what I write in a textbox on that
second form I then by clicking a button on this opened second form filter
the first form.
However when the first form is a subform everything works the same, but
instead of filtering the first (sub)form a whole new form is opened filtered
and the subform isn't filtered.

Jen.

Ken Snell said:
Not sure I've followed all that you're doing, but to filter the subform, you
need to set the subform's Filter property to a "WHERE"-type string and then
apply the filter.

Something like this:

Me.Subform.Form.Filter = "[FieldName] = " & SomeValue
Me.Subform.Form.FilterOn = True


--
Ken Snell
<MS ACCESS MVP>

Jen said:
I'we made a small simple sample database so that I could better explain my
problem.

I have one table, "tbl_main" that contains 2 columns 1:"id", 2:"name".
I also have 3 forms, 1:"frm_test", 2:"frm_pick", 3:"frm_big" .

The first form simply shows the names in at textbox and is bound to my table
"tbl_main" described earlier. On this form I also have a button opening my
second form "frm_pick".

My second form "frm_pick" is an unbound form with a textbox for writing in
data and a button that opens the first form "frm_test" filtered by that data
and finally closing the second form "frm_pick". The code behind this buttons
onclick event is:

Private Sub cmd_open_filtered_Click()
On Error GoTo Err_cmd_open_filtered_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frm_test"

stLinkCriteria = "[name]=" & "'" & Me![txt_name] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.Close acForm, Me.name

Exit_cmd_open_filtered_Click:

Exit Sub

Err_cmd_open_filtered_Click:
MsgBox Err.Description
Resume Exit_cmd_open_filtered_Click

frm.Visible = False

End Sub

Everything works ok so far.

In my third form, the unbound form "frm_big" I have a tab control "TabCtl0".
On the second tab (just because I have it like this on my real database in
where I have the problem) I have "frm_test" embedded as a subform. Now when
I click on this forms button that opens my second filtering form "frm_pick"
still everything is ok, the filtering form is opened. Here's my problem:
After writing in a value in the "frm_pick" and hitting the commandbutton
launching the code shown earlier, instead of showing the subform "frm_test"
filtered to whatever name I entered, "frm_test" opens as an own form,
filtered ok, leaving me with two "frm_test" open, the subform and a new
filtered one. I want the "frm_test" embedded as a subform in the tab control
being the one filtered and not have a seconf "frm_test" opened.

This I can't figure out how to do and would need help from you guys. Jen.
Click to expand...
Click to expand...
 
OK - so the form that opens the "form on which you enter the value to be
used for filtering" is a subform of another form.

In this case, assuming that that will be the only form that will call this
"form on which you enter the value to be used for filtering" (second) form,
use this code for the button on this second form:

Forms!FirstFormMainFormName!SubformName.Form.Filter = "[FieldName] = " &
Me.TextBoxName
Forms!FirstFormMainFormName!SubformName.Form.FilterOn = True

where
FirstFormMainFormName is the name of the main form in which the subform
is

SubformName is the name of the subform control (the control that
actually holds the subform) on the FirstFormMainFormName form

FieldName is the name of the field in the subform's recordsource that is
to be the filtered value

TextBoxName is the name of the textbox on the second form into which you
type the value


--
Ken Snell
<MS ACCESS MVP>




Jen said:
If I try to explain a little shorter;

From a form I open a second form. Based on what I write in a textbox on that
second form I then by clicking a button on this opened second form filter
the first form.
However when the first form is a subform everything works the same, but
instead of filtering the first (sub)form a whole new form is opened filtered
and the subform isn't filtered.

Jen.

Ken Snell said:
Not sure I've followed all that you're doing, but to filter the subform, you
need to set the subform's Filter property to a "WHERE"-type string and then
apply the filter.

Something like this:

Me.Subform.Form.Filter = "[FieldName] = " & SomeValue
Me.Subform.Form.FilterOn = True


--
Ken Snell
<MS ACCESS MVP>

Jen said:
I'we made a small simple sample database so that I could better
Click to expand...
Click to expand...
explain
my
Click to expand...
opening
my
Click to expand...
writing
in
data and a button that opens the first form "frm_test" filtered by
Click to expand...
that
data
and finally closing the second form "frm_pick". The code behind this buttons
onclick event is:

Private Sub cmd_open_filtered_Click()
On Error GoTo Err_cmd_open_filtered_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frm_test"

stLinkCriteria = "[name]=" & "'" & Me![txt_name] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.Close acForm, Me.name

Exit_cmd_open_filtered_Click:

Exit Sub

Err_cmd_open_filtered_Click:
MsgBox Err.Description
Resume Exit_cmd_open_filtered_Click

frm.Visible = False

End Sub

Everything works ok so far.

In my third form, the unbound form "frm_big" I have a tab control "TabCtl0".
On the second tab (just because I have it like this on my real
Click to expand...
Click to expand...
database
in
Click to expand...
Click to expand...
 
Thanks Ken for taking the time to answer.

I have tried loads of different combinations of your code but without
success. All I get (everytime) is: "Object doesn't support this property or
method". The subform is as mentioned in a tab control "TabCtl0", the subform
is in the tab "Page2". Below is one version of code I'we tested producing
the error:

Private Sub cmd_open_filtered_Click()
On Error GoTo Err_cmd_open_filtered_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frm_test"

stLinkCriteria = Forms!frm_big!TabCtl0.Form.Filter = "[name] = " &
Me.txt_name
Forms!frm_big!TabCtl0.Form.FilterOn = True
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.Close acForm, Me.name

Exit_cmd_open_filtered_Click:

Exit Sub

Err_cmd_open_filtered_Click:
MsgBox Err.Description
Resume Exit_cmd_open_filtered_Click

End Sub

As you probably figured out I'm not that good in Vba. If you could show what
you mean in the sample database http://www.jh-webservice.com/test.zip and
send it back to (e-mail address removed) then I probably
would learn this easier.

Jen

Ken Snell said:
OK - so the form that opens the "form on which you enter the value to be
used for filtering" is a subform of another form.

In this case, assuming that that will be the only form that will call this
"form on which you enter the value to be used for filtering" (second) form,
use this code for the button on this second form:

Forms!FirstFormMainFormName!SubformName.Form.Filter = "[FieldName] = " &
Me.TextBoxName
Forms!FirstFormMainFormName!SubformName.Form.FilterOn = True

where
FirstFormMainFormName is the name of the main form in which the subform
is

SubformName is the name of the subform control (the control that
actually holds the subform) on the FirstFormMainFormName form

FieldName is the name of the field in the subform's recordsource that is
to be the filtered value

TextBoxName is the name of the textbox on the second form into which you
type the value


--
Ken Snell
<MS ACCESS MVP>




Jen said:
If I try to explain a little shorter;

From a form I open a second form. Based on what I write in a textbox on that
second form I then by clicking a button on this opened second form filter
the first form.
However when the first form is a subform everything works the same, but
instead of filtering the first (sub)form a whole new form is opened filtered
and the subform isn't filtered.

Jen.

Ken Snell said:
Not sure I've followed all that you're doing, but to filter the
Click to expand...
subform,
you
need to set the subform's Filter property to a "WHERE"-type string and then
apply the filter.

Something like this:

Me.Subform.Form.Filter = "[FieldName] = " & SomeValue
Me.Subform.Form.FilterOn = True


--
Ken Snell
<MS ACCESS MVP>

I'we made a small simple sample database so that I could better
Click to expand...
explain
my
problem.

I have one table, "tbl_main" that contains 2 columns 1:"id", 2:"name".
I also have 3 forms, 1:"frm_test", 2:"frm_pick", 3:"frm_big" .

The first form simply shows the names in at textbox and is bound to my
table
"tbl_main" described earlier. On this form I also have a button
Click to expand...
opening
my
second form "frm_pick".

My second form "frm_pick" is an unbound form with a textbox for
Click to expand...
writing
in
data and a button that opens the first form "frm_test" filtered by that
data
and finally closing the second form "frm_pick". The code behind this
buttons
onclick event is:

Private Sub cmd_open_filtered_Click()
On Error GoTo Err_cmd_open_filtered_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frm_test"

stLinkCriteria = "[name]=" & "'" & Me![txt_name] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.Close acForm, Me.name

Exit_cmd_open_filtered_Click:

Exit Sub

Err_cmd_open_filtered_Click:
MsgBox Err.Description
Resume Exit_cmd_open_filtered_Click

frm.Visible = False

End Sub

Everything works ok so far.

In my third form, the unbound form "frm_big" I have a tab control
"TabCtl0".
On the second tab (just because I have it like this on my real
Click to expand...
database
in
where I have the problem) I have "frm_test" embedded as a subform. Now
when
I click on this forms button that opens my second filtering form
"frm_pick"
still everything is ok, the filtering form is opened. Here's my problem:
After writing in a value in the "frm_pick" and hitting the commandbutton
launching the code shown earlier, instead of showing the subform
"frm_test"
filtered to whatever name I entered, "frm_test" opens as an own form,
filtered ok, leaving me with two "frm_test" open, the subform and a new
filtered one. I want the "frm_test" embedded as a subform in the tab
control
being the one filtered and not have a seconf "frm_test" opened.

This I can't figure out how to do and would need help from you guys. Jen.
Click to expand...
Click to expand...
Click to expand...
 
Jen,

Excuse me for jumping in here. The information Ken has given you is
correct. It's got nothing to do with the tab control... just leave
this out of it. Just remember that when you are opening a form with a
subform, the form which is shown in the subform is not open.

Anyway, that wasn't my main point. It seems to me that this is
getting way more complicated than I ever do. Why not try this
alternative approach?... Base your frmTest on a query, and in the
criteria of the [name] field, put...
[Forms]![frmPick]![txtName]
Then it won't matter whether you open frmTest standalone, or whether
you open frmBig with frmTest as a subform, you should get the right
data coming through, and you can throw all that complicated code out
the window.

By the way, a side issue... I know this is just a sample database,
but the word Name has a special meaning in Access, and is classified
as a Reserved Word. As such, it should not be used as the name of a
field or control or database object, as this can cause errors under
some circumstances.

- Steve Schapel, Microsoft Access MVP
 
Thanks Steve for taking the time to answer.

Still don't get it though. If I do it like you said, then if frmPick isn't
loaded then I get #Name? and it never is loaded except of when filtering the
frm_Test. Jen.

Steve Schapel said:
Jen,

Excuse me for jumping in here. The information Ken has given you is
correct. It's got nothing to do with the tab control... just leave
this out of it. Just remember that when you are opening a form with a
subform, the form which is shown in the subform is not open.

Anyway, that wasn't my main point. It seems to me that this is
getting way more complicated than I ever do. Why not try this
alternative approach?... Base your frmTest on a query, and in the
criteria of the [name] field, put...
[Forms]![frmPick]![txtName]
Then it won't matter whether you open frmTest standalone, or whether
you open frmBig with frmTest as a subform, you should get the right
data coming through, and you can throw all that complicated code out
the window.

By the way, a side issue... I know this is just a sample database,
but the word Name has a special meaning in Access, and is classified
as a Reserved Word. As such, it should not be used as the name of a
field or control or database object, as this can cause errors under
some circumstances.

- Steve Schapel, Microsoft Access MVP


Thanks Ken for taking the time to answer.

I have tried loads of different combinations of your code but without
success. All I get (everytime) is: "Object doesn't support this property or
method". The subform is as mentioned in a tab control "TabCtl0", the subform
is in the tab "Page2". Below is one version of code I'we tested producing
the error:

Private Sub cmd_open_filtered_Click()
On Error GoTo Err_cmd_open_filtered_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frm_test"

stLinkCriteria = Forms!frm_big!TabCtl0.Form.Filter = "[name] = " &
Me.txt_name
Forms!frm_big!TabCtl0.Form.FilterOn = True
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.Close acForm, Me.name

Exit_cmd_open_filtered_Click:

Exit Sub

Err_cmd_open_filtered_Click:
MsgBox Err.Description
Resume Exit_cmd_open_filtered_Click

End Sub

As you probably figured out I'm not that good in Vba. If you could show what
you mean in the sample database http://www.jh-webservice.com/test.zip and
send it back to (e-mail address removed) then I probably
would learn this easier.

Jen
Click to expand...
Click to expand...
 
OK, Jen, my apologies. I guess I made an assumption, based on the way
I personally work, that frmTest would always be opened from frmPick.

If I understand the situation correctly, frmTest is a subform on
frmBig, and you want the record shown on this subform to be filtered
according to the entry in an unbound textbox txtName on frmPick, based
on clicking a button on frmPick. Am I right? So in that case, based
on Ken's advice, your code should look like this...

Private Sub cmd_open_filtered_Click()
DoCmd.SelectObject acForm, "frmBig"
With Forms!frmBig!frmTest.Form
.Filter = "[name] = '" & Me.txtName & "'"
.FilterOn = True
End With
End Sub

This assumes that frmBig is already open at the time, which I have
gathered is the case. Otherwise your code will be...

Private Sub cmd_open_filtered_Click()
DoCmd.OpenForm "frmBig"
With Forms!frmBig!frmTest.Form
.Filter = "[name] = '" & Me.txtName & "'"
.FilterOn = True
End With
End Sub

- Steve Schapel, Microsoft Access MVP
 
Back
Top