Filter subform from text box

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Can anyone help with the code required to filter a subform (ie. what is the
proper syntax) as you type in a text box on the main form which houses the
sub form?

Is it possible to have the filter fire on the data in the subform after each
keystroke so that the subform data gets filtered as the user types in the
text box?

Also, am suspecting it is possible to add a 'like' to the filter string and
then append a '*' to the end of the filter string for a wildcard filter - can
someone help with this syntax too?

Sorry for some many questions but I hope they are clear enough for someone
to assist. Many thanks in advance
/JW
 
Use the Change event procedure of the text box to filter the subform after
every keystroke.

This example assumes:
- the user types into an unbound text box named txtFilterCity;
- the filter is applied against a field named City in the subform;
- City is a Text type field (not a Number type field);
- the subform control is named Sub1;
- you want no matches in the subform when the filter box is blank.

Dim strWhere As String
With Me.txtFilterCity
If .Text = vbNullString Then
strWhere = "(False)"
Else
strWhere = "[City] Like """ & .Text & "*"""
End If
End With
With me.[Sub1].Form
.Filter = strWhere
.FilterOn = True
End With

Be sure to use the Text property of the text box, since its Value is not up
to date in the Change event.
 
Brilliant Allen. Works perfectly - you have saved me an immense amount of
work trying to figure out how to make this work!

I made two simple changes to your code:
a) I add a line in the first part of the If to set the filter on the subform
to false (i.e. when the text box is empty, then take the filter off the
subform

b) Moved the With/End With code to the Else part of the If - necessary due
to the change in a) above

Again, cheers for the assistance.
James


Allen Browne said:
Use the Change event procedure of the text box to filter the subform after
every keystroke.

This example assumes:
- the user types into an unbound text box named txtFilterCity;
- the filter is applied against a field named City in the subform;
- City is a Text type field (not a Number type field);
- the subform control is named Sub1;
- you want no matches in the subform when the filter box is blank.

Dim strWhere As String
With Me.txtFilterCity
If .Text = vbNullString Then
strWhere = "(False)"
Else
strWhere = "[City] Like """ & .Text & "*"""
End If
End With
With me.[Sub1].Form
.Filter = strWhere
.FilterOn = True
End With

Be sure to use the Text property of the text box, since its Value is not up
to date in the Change event.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

J Welsby said:
Can anyone help with the code required to filter a subform (ie. what is
the
proper syntax) as you type in a text box on the main form which houses the
sub form?

Is it possible to have the filter fire on the data in the subform after
each
keystroke so that the subform data gets filtered as the user types in the
text box?

Also, am suspecting it is possible to add a 'like' to the filter string
and
then append a '*' to the end of the filter string for a wildcard filter -
can
someone help with this syntax too?

Sorry for some many questions but I hope they are clear enough for someone
to assist. Many thanks in advance
/JW
Click to expand...
Click to expand...
 
Back
Top