How to apply a filter on a Subform based on the main form Field

  • Thread starter Thread starter Irshad Alam
  • Start date Start date
I

Irshad Alam

I have a Form Named FORM1, based on no tables or queries.
Only it has a Option Group Frame in that it include 2
option button. Other than that there is a Text box "Text2"
and a Command Button on the Form1.

Later In the FORM1 I have Inserted FORM2 as a Subform.
This FORM2 is based on a Table named TELEDIR.

Now my need is that what ever is typed on the text
control "Text2" of FORM1 and if the Frame value is 1, then
the subform will filter the data for the Company name
field only. Next if the Frame value is 2, then it will
filter the ContPerson Name based on the Text control of
the FORM1 only.


The Code I put on the command button is as below, which
does not work :

If Me.Frame0=1 Then
Me.Form2.SetFocus
DoCmd.ApplyFilter "", "[CoName] Like ""*"" & [Forms]!
[Form1]![Text2] & ""*"""
else
If Me.Frame0=2 Then
Me.Form2.SetFocus
DoCmd.ApplyFilter "", "[ContactPer] Like ""*"" & [Forms]!
[Form1]![Text2] & ""*"""
End If
End If

'*********

But the above code does not work. Please advise me, what
code should I use to filter the subform records, based on
the option and the form text control.

Please advise.
 
first, make sure that you are using the name of the subform *control*, not
the name of the subform itself (its' name in the database window). sometimes
the two are different.
in design view, click the subform once to select it, then open the
Properties box. click the Other tab, and look at the Name property. that's
the name of the subform control. i'll refer to it here as Child1.

try the following code on your command button:

Dim strField As String

Select Case Me!Frame0
Case 1
strField = "CoName"
Case 2
strField = "ContactPer"
End Select

With Me!Child1.Form
.Filter = strField & " Like ""*"" & .Parent!Text2 & ""*""
.FilterOn = True
End With

"Me!Child1.Form" invokes the form properties of the subform contained within
the subform control. ".Parent!Text2" refers to the control on the main form,
which is the "parent" of the subform. using Select Case makes it easy to add
coding if you expand the option group in the future.

hth
 
Attn. : Madam Tina

Thanks for your advice.
I implemented it, but it does not work, It shows error No.
2448 with the description "You cannot Assign a value to
this object" When press debug, it highlight the below line
with yellow color :

..Filter = strField & " Like ""*"" & .Parent!Text2 & ""*""

please advise
-----Original Message-----
first, make sure that you are using the name of the subform *control*, not
the name of the subform itself (its' name in the database window). sometimes
the two are different.
in design view, click the subform once to select it, then open the
Properties box. click the Other tab, and look at the Name property. that's
the name of the subform control. i'll refer to it here as Child1.

try the following code on your command button:

Dim strField As String

Select Case Me!Frame0
Case 1
strField = "CoName"
Case 2
strField = "ContactPer"
End Select

With Me!Child1.Form
.Filter = strField & " Like ""*"" & .Parent!Text2 & ""*""
.FilterOn = True
End With

"Me!Child1.Form" invokes the form properties of the subform contained within
the subform control. ".Parent!Text2" refers to the control on the main form,
which is the "parent" of the subform. using Select Case makes it easy to add
coding if you expand the option group in the future.

hth


Irshad Alam said:
I have a Form Named FORM1, based on no tables or queries.
Only it has a Option Group Frame in that it include 2
option button. Other than that there is a Text box "Text2"
and a Command Button on the Form1.

Later In the FORM1 I have Inserted FORM2 as a Subform.
This FORM2 is based on a Table named TELEDIR.

Now my need is that what ever is typed on the text
control "Text2" of FORM1 and if the Frame value is 1, then
the subform will filter the data for the Company name
field only. Next if the Frame value is 2, then it will
filter the ContPerson Name based on the Text control of
the FORM1 only.
The Code I put on the command button is as below, which
does not work :

If Me.Frame0=1 Then
Me.Form2.SetFocus
DoCmd.ApplyFilter "", "[CoName] Like ""*"" & [Forms]!
[Form1]![Text2] & ""*"""
else
If Me.Frame0=2 Then
Me.Form2.SetFocus
DoCmd.ApplyFilter "", "[ContactPer] Like ""*"" & [Forms]!
[Form1]![Text2] & ""*"""
End If
End If
Click to expand...
Click to expand...
 
i tested the code, which i hadn't done originally. it worked for me with the
following change:

Dim strField As String

Select Case Me!Frame0
Case 1
strField = "CoName"
Case 2
strField = "ContactPer"
End Select

With Me!Child1.Form
.Filter = strField & " Like '*" & .Parent!Text2 & "*'"
.FilterOn = True
End With

hth


Irshad Alam said:
Attn. : Madam Tina

Thanks for your advice.
I implemented it, but it does not work, It shows error No.
2448 with the description "You cannot Assign a value to
this object" When press debug, it highlight the below line
with yellow color :

.Filter = strField & " Like ""*"" & .Parent!Text2 & ""*""

please advise
-----Original Message-----
first, make sure that you are using the name of the subform *control*, not
the name of the subform itself (its' name in the database window). sometimes
the two are different.
in design view, click the subform once to select it, then open the
Properties box. click the Other tab, and look at the Name property. that's
the name of the subform control. i'll refer to it here as Child1.

try the following code on your command button:

Dim strField As String

Select Case Me!Frame0
Case 1
strField = "CoName"
Case 2
strField = "ContactPer"
End Select

With Me!Child1.Form
.Filter = strField & " Like ""*"" & .Parent!Text2 & ""*""
.FilterOn = True
End With

"Me!Child1.Form" invokes the form properties of the subform contained within
the subform control. ".Parent!Text2" refers to the control on the main form,
which is the "parent" of the subform. using Select Case makes it easy to add
coding if you expand the option group in the future.

hth


Irshad Alam said:
I have a Form Named FORM1, based on no tables or queries.
Only it has a Option Group Frame in that it include 2
option button. Other than that there is a Text box "Text2"
and a Command Button on the Form1.

Later In the FORM1 I have Inserted FORM2 as a Subform.
This FORM2 is based on a Table named TELEDIR.

Now my need is that what ever is typed on the text
control "Text2" of FORM1 and if the Frame value is 1, then
the subform will filter the data for the Company name
field only. Next if the Frame value is 2, then it will
filter the ContPerson Name based on the Text control of
the FORM1 only.
The Code I put on the command button is as below, which
does not work :

If Me.Frame0=1 Then
Me.Form2.SetFocus
DoCmd.ApplyFilter "", "[CoName] Like ""*"" & [Forms]!
[Form1]![Text2] & ""*"""
else
If Me.Frame0=2 Then
Me.Form2.SetFocus
DoCmd.ApplyFilter "", "[ContactPer] Like ""*"" & [Forms]!
[Form1]![Text2] & ""*"""
End If
End If
Click to expand...
Click to expand...
But the above code does not work. Please advise me, what
Click to expand...
Click to expand...
 
Attn. : Madam Tina

Thanks for your advice. The amended code works on a text
search normally, I applied and found perfect but a
problem/error arises on date and numeric field search.

I added 2 more option in the Frame0 and in Code I added 2
more Case, for date field and Numberic field.

Please note the exact required details of mine

The form which I a applying has several data types of
field, total four field form, 1st and 2nd of Text field
type, third Date type field and Fourth Numeric type field:

First 2 field is of text type. Your code works on it
normal. Fine.

Third field is a date field, While I search for this, It
show error.

Fourth Field is a numberic field. While I search for
this , It show error


please advise what to do now.
-----Original Message-----
i tested the code, which i hadn't done originally. it worked for me with the
following change:

Dim strField As String

Select Case Me!Frame0
Case 1
strField = "CoName"
Case 2
strField = "ContactPer"
End Select

With Me!Child1.Form
.Filter = strField & " Like '*" & .Parent!Text2 & "*'"
.FilterOn = True
End With

hth


Irshad Alam said:
Attn. : Madam Tina

Thanks for your advice.
I implemented it, but it does not work, It shows error No.
2448 with the description "You cannot Assign a value to
this object" When press debug, it highlight the below line
with yellow color :

.Filter = strField & " Like ""*"" & .Parent!Text2 & ""*""

please advise
-----Original Message-----
first, make sure that you are using the name of the subform *control*, not
the name of the subform itself (its' name in the
Click to expand...
database
window). sometimes
the two are different.
in design view, click the subform once to select it,
Click to expand...
then
open the
Properties box. click the Other tab, and look at the
Click to expand...
Name
property. that's
the name of the subform control. i'll refer to it here
Click to expand...
as
Child1.
try the following code on your command button:

Dim strField As String

Select Case Me!Frame0
Case 1
strField = "CoName"
Case 2
strField = "ContactPer"
End Select

With Me!Child1.Form
.Filter = strField & " Like ""*"" & .Parent!
Click to expand...
Text2
& ""*""
.FilterOn = True
End With

"Me!Child1.Form" invokes the form properties of the subform contained within
the subform control. ".Parent!Text2" refers to the control on the main form,
which is the "parent" of the subform. using Select Case makes it easy to add
coding if you expand the option group in the future.

hth


I have a Form Named FORM1, based on no tables or queries.
Only it has a Option Group Frame in that it include 2
option button. Other than that there is a Text box "Text2"
and a Command Button on the Form1.

Later In the FORM1 I have Inserted FORM2 as a Subform.
This FORM2 is based on a Table named TELEDIR.

Now my need is that what ever is typed on the text
control "Text2" of FORM1 and if the Frame value is 1, then
the subform will filter the data for the Company name
field only. Next if the Frame value is 2, then it will
filter the ContPerson Name based on the Text control of
the FORM1 only.
Click to expand...
The Code I put on the command button is as below, which
does not work :

If Me.Frame0=1 Then
Me.Form2.SetFocus
DoCmd.ApplyFilter "", "[CoName] Like ""*"" & [Forms]!
[Form1]![Text2] & ""*"""
else
If Me.Frame0=2 Then
Me.Form2.SetFocus
DoCmd.ApplyFilter "", "[ContactPer] Like ""*"" & [Forms]!
[Form1]![Text2] & ""*"""
End If
End If
Click to expand...
But the above code does not work. Please advise me, what
code should I use to filter the subform records,
Click to expand...
based
on
the option and the form text control.
Please advise.
Click to expand...
Click to expand...


.
Click to expand...
 
Attn. : Madam Tina

Thanks for your advice. The amended code works on a text
search normally, I applied and found perfect but a
problem/error arises on date and numeric field search.

I added 2 more option in the Frame0 and in Code I added 2
more Case, for date field and Numberic field.

Please note the exact required details of mine

The form which I a applying has several data types of
field, total four field form, 1st and 2nd of Text field
type, third Date type field and Fourth Numeric type field:

First 2 field is of text type. Your code works on it
normal. Fine.

Third field is a date field, While I search for this, It
show error.

Fourth Field is a numberic field. While I search for
this , It show error


please advise what to do now.
-----Original Message-----
i tested the code, which i hadn't done originally. it worked for me with the
following change:

Dim strField As String

Select Case Me!Frame0
Case 1
strField = "CoName"
Case 2
strField = "ContactPer"
End Select

With Me!Child1.Form
.Filter = strField & " Like '*" & .Parent!Text2 & "*'"
.FilterOn = True
End With

hth


Irshad Alam said:
Attn. : Madam Tina

Thanks for your advice.
I implemented it, but it does not work, It shows error No.
2448 with the description "You cannot Assign a value to
this object" When press debug, it highlight the below line
with yellow color :

.Filter = strField & " Like ""*"" & .Parent!Text2 & ""*""

please advise
-----Original Message-----
first, make sure that you are using the name of the subform *control*, not
the name of the subform itself (its' name in the
Click to expand...
database
window). sometimes
the two are different.
in design view, click the subform once to select it,
Click to expand...
then
open the
Properties box. click the Other tab, and look at the
Click to expand...
Name
property. that's
the name of the subform control. i'll refer to it here
Click to expand...
as
Child1.
try the following code on your command button:

Dim strField As String

Select Case Me!Frame0
Case 1
strField = "CoName"
Case 2
strField = "ContactPer"
End Select

With Me!Child1.Form
.Filter = strField & " Like ""*"" & .Parent!
Click to expand...
Text2
& ""*""
.FilterOn = True
End With

"Me!Child1.Form" invokes the form properties of the subform contained within
the subform control. ".Parent!Text2" refers to the control on the main form,
which is the "parent" of the subform. using Select Case makes it easy to add
coding if you expand the option group in the future.

hth


I have a Form Named FORM1, based on no tables or queries.
Only it has a Option Group Frame in that it include 2
option button. Other than that there is a Text box "Text2"
and a Command Button on the Form1.

Later In the FORM1 I have Inserted FORM2 as a Subform.
This FORM2 is based on a Table named TELEDIR.

Now my need is that what ever is typed on the text
control "Text2" of FORM1 and if the Frame value is 1, then
the subform will filter the data for the Company name
field only. Next if the Frame value is 2, then it will
filter the ContPerson Name based on the Text control of
the FORM1 only.
Click to expand...
The Code I put on the command button is as below, which
does not work :

If Me.Frame0=1 Then
Me.Form2.SetFocus
DoCmd.ApplyFilter "", "[CoName] Like ""*"" & [Forms]!
[Form1]![Text2] & ""*"""
else
If Me.Frame0=2 Then
Me.Form2.SetFocus
DoCmd.ApplyFilter "", "[ContactPer] Like ""*"" & [Forms]!
[Form1]![Text2] & ""*"""
End If
End If
Click to expand...
But the above code does not work. Please advise me, what
code should I use to filter the subform records,
Click to expand...
based
on
the option and the form text control.
Please advise.
Click to expand...
Click to expand...


.
Click to expand...
 
when you have defferent data types, the code can't be quite as compact and
non-redundant as i wrote it, instead more like

Select Case Me!Frame0
With Me!Child1.Form
Case 1
.Filter = strField & " Like '*" & .Parent!Text2 & "*'"
Case 2
.Filter = "ContactPer Like '*" & .Parent!Text2 & "*'"
Case 3
.Filter = "DateFieldName = #" & .Parent!Text2 & "#"
Case 4
.Filter = "NumericFieldName = " & .Parent!Text2
End Select

.FilterOn = True
End With

substitute the correct date field and numeric field names, of course.

hth


Irshad Alam said:
Attn. : Madam Tina

Thanks for your advice. The amended code works on a text
search normally, I applied and found perfect but a
problem/error arises on date and numeric field search.

I added 2 more option in the Frame0 and in Code I added 2
more Case, for date field and Numberic field.

Please note the exact required details of mine

The form which I a applying has several data types of
field, total four field form, 1st and 2nd of Text field
type, third Date type field and Fourth Numeric type field:

First 2 field is of text type. Your code works on it
normal. Fine.

Third field is a date field, While I search for this, It
show error.

Fourth Field is a numberic field. While I search for
this , It show error


please advise what to do now.
-----Original Message-----
i tested the code, which i hadn't done originally. it worked for me with the
following change:

Dim strField As String

Select Case Me!Frame0
Case 1
strField = "CoName"
Case 2
strField = "ContactPer"
End Select

With Me!Child1.Form
.Filter = strField & " Like '*" & .Parent!Text2 & "*'"
.FilterOn = True
End With

hth


Irshad Alam said:
Attn. : Madam Tina

Thanks for your advice.
I implemented it, but it does not work, It shows error No.
2448 with the description "You cannot Assign a value to
this object" When press debug, it highlight the below line
with yellow color :

.Filter = strField & " Like ""*"" & .Parent!Text2 & ""*""

please advise

-----Original Message-----
first, make sure that you are using the name of the
subform *control*, not
the name of the subform itself (its' name in the database
window). sometimes
the two are different.
in design view, click the subform once to select it, then
open the
Properties box. click the Other tab, and look at the Name
property. that's
the name of the subform control. i'll refer to it here as
Child1.

try the following code on your command button:

Dim strField As String

Select Case Me!Frame0
Case 1
strField = "CoName"
Case 2
strField = "ContactPer"
End Select

With Me!Child1.Form
.Filter = strField & " Like ""*"" & .Parent! Text2
& ""*""
.FilterOn = True
End With

"Me!Child1.Form" invokes the form properties of the
subform contained within
the subform control. ".Parent!Text2" refers to the
control on the main form,
which is the "parent" of the subform. using Select Case
makes it easy to add
coding if you expand the option group in the future.

hth


I have a Form Named FORM1, based on no tables or
queries.
Only it has a Option Group Frame in that it include 2
option button. Other than that there is a Text
box "Text2"
and a Command Button on the Form1.

Later In the FORM1 I have Inserted FORM2 as a Subform.
This FORM2 is based on a Table named TELEDIR.

Now my need is that what ever is typed on the text
control "Text2" of FORM1 and if the Frame value is 1,
then
the subform will filter the data for the Company name
field only. Next if the Frame value is 2, then it will
filter the ContPerson Name based on the Text control of
the FORM1 only.

The Code I put on the command button is as below, which
does not work :

If Me.Frame0=1 Then
Me.Form2.SetFocus
DoCmd.ApplyFilter "", "[CoName] Like ""*"" & [Forms]!
[Form1]![Text2] & ""*"""
else
If Me.Frame0=2 Then
Me.Form2.SetFocus
DoCmd.ApplyFilter "", "[ContactPer] Like ""*"" &
[Forms]!
[Form1]![Text2] & ""*"""
End If
End If

But the above code does not work. Please advise me, what
code should I use to filter the subform records, based
on
the option and the form text control.
Please advise.
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top