Multiple criteria (textbox and OptionGroup) on filter subform

[Asghaar]

Hello guys,

Once again I hope you will help me as usually happen.
I have the following situation :
- one form and subform ( set to start and show all records - datasheet style)
- a textbox that filters the subform based on what is written in it ( more exactly matches what is written it it and filters the subform based on the Service field )
- one Option Group that succesfully filters the subform based on a hidden filed in the sunform ( where simple 1,2 and 3 are stored ).

All this works great separatly but i want to accomplish the following thing -
filter the subform based on a service written and after to be able to select an option group that will further more filter the records that appeared based on the textbox filter.

I've tried something in the afterupdate event of the option group but i get - [3075] Syntax error in query expression [Comments]="2" and [Service]=ze"' - this is one example

Below are the codes that i have behind each :

Private Sub Frame100_AfterUpdate()

Me.L2_FtGroup_Contacts_sub.Form.Filter = "[Service]= " & Forms!L2_FtGroup_Contacts.txtFilter.Value & Chr(34) & "And [Comment]= " & Chr(34) & Forms!L2_FtGroup_Contacts.Frame100 & Chr(34)


'''Me.L2_FtGroup_Contacts_sub.Form.Filter = "[Comment]= " & Chr(34) & Forms!L2_FtGroup_Contacts.Frame100 & Chr(34)  
[B]- this alone works great, but shows all the records that have 1,2 or 3 and not only those that have the service mendioned in the textbox[/B]

Me.L2_FtGroup_Contacts_sub.Form.FilterOn = True
 
 If L2_FtGroup_Contacts_sub.Form.Filter = "[Comment]= " & Chr(34) & "3" & Chr(34) Then
  Me.L2_FtGroup_Contacts_sub.Form.FilterOn = False

 End If

End Sub

The code for the textbox:

rivate Sub txtFilter_AfterUpdate()


Dim strWhere As String
With Me.txtFilter
If .Text = vbNullString Then
strWhere = ""
Else
strWhere = "[Service] Like ""*" & .Text & "*"""
End If
End With
With Me.[L2_FtGroup_Contacts_sub].Form
.Filter = strWhere
.FilterOn = True
End With

End Sub

So,any way how i could obtain what i want ?

Thank you for your answer and help.

 

[AUGuy]

I'm guessing the listbox details are populated by a query?

If that's the case, why not point the criteria for your two fields to the controls in the query. Simpler than writing VBA and easy to implement.

 

[Asghaar]

I'm guessing the listbox details are populated by a query?

If that's the case, why not point the criteria for your two fields to the controls in the query. Simpler than writing VBA and easy to implement.

Hello,

I don't have a listbox. Only a text box in which people can write anything and based on that the subform(which shows all the records initially from a table) is filtered.
One option group which has 3 options (opt number 3 being show all).
What I want is to filter the subform using this 2 criteria (text box and option group)
I hope this brings some light on my situation.
Regards

==== any ideas ?

 

[Asghaar]

Hello,

I've managed to put up a solution for what i wanted.
I post the code below for people that might want the same thing .

So, i first filter the subform with a textbox ( has a on change event) - while the box is not empty the option group will get visible for more detail filtering

Private Sub txtFilter_Change()
Dim strWhere As String
With Me.txtFilter
If .Text = vbNullString Then

strWhere = ""
Me!Frame100.Visible = False
Me!Label101.Visible = False
Me!Label104.Visible = False
Me!Label111.Visible = False
Me!Option103.Visible = False
Me!Option110.Visible = False
Forms!T_all!T_all_sub.Form.FilterOn = False
Else

strWhere = "[Comment] Like ""*" & .Text & "*"""
Me!Frame100.Visible = True
Me!Label101.Visible = True
Me!Label104.Visible = True
Me!Label111.Visible = True
Me!Option103.Visible = True
Me!Option110.Visible = True
End If
End With

 With Me.[T_all_sub].Form
.Filter = strWhere
.FilterOn = True
End With

An the code for the option group (after update event ) :

Private Sub Frame100_AfterUpdate()

Dim strWhere As String

[Forms]![T_all]!txtFilter.SetFocus
With Me.txtFilter
If .Text = vbNullString Then
strWhere = " "
Else
Me.T_all_sub.Form.Filter = "[Comment] Like ""*" & .Text & "*"" And [ContactRoleGroup] like Forms!T_all.Frame100"
End If
End With
Me.T_all_sub.Form.FilterOn = True

  
If Forms!T_all.Frame100 = 3 Then
[Forms]![T_all]!txtFilter.SetFocus
With Me.txtFilter
If .Text = vbNullString Then
strWhere = ""
Else
strWhere = "[Comment] Like ""*" & .Text & "*"" "
End If
End With
With Me.[T_all_sub].Form
.Filter = strWhere
.FilterOn = True
End With
End If
 
End Sub

Hope will help someone.

Thank you all and happy holidays.