If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
How to apply a filter on a Subform based on the main form Field
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. |
#2
|
|||
|
|||
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" wrote in message ... 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. |
#3
|
|||
|
|||
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" wrote in message ... 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. |
#4
|
|||
|
|||
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" wrote in message ... 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" wrote in message ... 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. |
#5
|
|||
|
|||
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" wrote in message ... 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" wrote in message ... 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. . |
#6
|
|||
|
|||
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" wrote in message ... 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" wrote in message ... 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. . |
#7
|
|||
|
|||
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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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. . |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
How to add a new record to subform from main form? | Alex | Using Forms | 8 | July 27th, 2004 04:44 PM |
auto entry into second table after update | Tony | New Users | 13 | July 9th, 2004 10:42 PM |
form doesn't update!!!!! | manish | Using Forms | 4 | June 15th, 2004 11:24 AM |
Recordset in subform based on field in parent form | Lyn | General Discussion | 15 | June 14th, 2004 03:10 PM |
Filter subform combobox on option button in main form | Emma | Using Forms | 2 | June 11th, 2004 10:29 PM |