Address Book - Subform

[Harry Taylor]

Hi All,
I have an Phonebook subform - (PBSubform)
with the following fields;
[Contact Name], [Company], [Telephone] & [Mobile]

Using an unbound textbox (Text2) I can search for a name using the following code;
Private Sub Text2_Change()
Dim strSQL As String
strSQL = "[Contact Name] LIKE " & Chr(34) & Me.Text2.Text & "*" & Chr(34)
Me.PBSubform.Form.Filter = strSQL
Me.PBSubform.Form.FilterOn = True
End Sub

How can I adapt the code so it filters all fields - [Contact Name], [Company], [Telephone] & [Mobile]
(Not just [Contact Name])

Thanks in advance

 

[CJ_London]

Something like

strSQL = "[Contact Name] LIKE " & Chr(34) & Me.Text2.Text & "*" & Chr(34) & “ OR [company] like “ & …..

 

[MajP]

Here is a generic class that can be used with any fields.

Post in thread 'Search as you type in a form without Subform'

Jul 16, 2024

Here is a cut down version

To use and search any amount of TEXT fields

'Need to declare a variable at top of module
Private FAYT_Form As FindAsYouTypeForm

Private Sub Form_Load()
  Set FAYT_Form = New FindAsYouTypeForm
  FAYT_Form.Initialize Me, Me.txtFilter, ffrm_Anywhereinstring, True, "ProductName"
End Sub

In the initialize event
1. Me is the reference to the form to filter. If it is a subform you need to pass a reference to the subform
2. Me.TextFilter is the textbox that you type into
3. ffrm_Anywhereinstring is an enum that lets you pick how to search
4. True handles...

• MajP

 

[Josef P.]

I assume you only want to use the one text box.

Template for criteria string:
[Contact Name] LIKE {FilterValue} OR Company LIKE {FilterValue} OR Telephone LIKE {FilterValue} OR Mobile LIKE {FilterValue}

As code:

Private Sub Text2_Change()  'Text2 ... what a great name ... you know immediately what the content is ;)
  ' Why change and not AfterUpdate? ... I hate it when I have to wait after every character ;)
    FilterSubForm Me.Text2.Text
End Sub

private sub FilterSubForm(byval FilterValue as Variant)

    Const FilterStringTemplate as String = "[Contact Name] LIKE {FilterValue} OR Company LIKE {FilterValue} OR Telephone LIKE {FilterValue} OR Mobile LIKE {FilterValue}"

    Dim FilterString as String

    if len(FilterValue) > 0 then
         FilterString = replace(FilterStringTemplate, "{FilterValue}", "'" & replace(FilterValue, "'", "''") & "*'"
    end if

    With Me.PBSubform.Form
         .Filter = FilterString
         .FilterOn = len(FilterString)>0
    end With

End Sub

 

[plog]

strSQL = "[Contact Name] & [Company] & [Telephone] & [Mobile] LIKE " & Chr(34) & Me.Text2.Text & "*" & Chr(34)

 

[Harry Taylor]

I assume you only want to use the one text box.

Template for criteria string:
[Contact Name] LIKE {FilterValue} OR Company LIKE {FilterValue} OR Telephone LIKE {FilterValue} OR Mobile LIKE {FilterValue}

As code:

Private Sub Text2_Change()  'Text2 ... what a great name ... you know immediately what the content is ;)
  ' Why change and not AfterUpdate? ... I hate it when I have to wait after every character ;)
    FilterSubForm Me.Text2.Text
End Sub

private sub FilterSubForm(byval FilterValue as Variant)

    Const FilterStringTemplate as String = "[Contact Name] LIKE {FilterValue} OR Company LIKE {FilterValue} OR Telephone LIKE {FilterValue} OR Mobile LIKE {FilterValue}"

    Dim FilterString as String

    if len(FilterValue) > 0 then
         FilterString = replace(FilterStringTemplate, "{FilterValue}", "'" & replace(FilterValue, "'", "''") & "*'"
    end if

    With Me.PBSubform.Form
         .Filter = FilterString
         .FilterOn = len(FilterString)>0
    end With

End Sub

Thanks Jesof,

I'm getting an issue with;
FilterString = replace(FilterStringTemplate, "{FilterValue}", "'" & replace(FilterValue, "'", "''") & "*'"

Compile Error
Syntax Error

(I'll be honest your coding is way above my level )

 

[Josef P.]

I'm getting an issue with ...

A closing bracket is missing in my code:
FilterString = replace(FilterStringTemplate, "{FilterValue}", "'" & replace(FilterValue, "'", "''") & "*'")

More readable:

private sub FilterSubForm(byval FilterValue as Variant)

    Const FilterStringTemplate as String = "[Contact Name] LIKE {FilterValue} OR Company LIKE {FilterValue} OR Telephone LIKE {FilterValue} OR Mobile LIKE {FilterValue}"

    Dim FilterString as String
    Dim FilterValueExpression as string

    if len(FilterValue) > 0 then
         FilterValueExpression = "'" & replace(FilterValue, "'", "''") & "*'"
         FilterString = replace(FilterStringTemplate, "{FilterValue}", FilterValueExpression)
    end if

    With Me.PBSubform.Form
         .Filter = FilterString
         .FilterOn = len(FilterString)>0
    end With

End Sub

 

[Harry Taylor]

A closing bracket is missing in my code:
FilterString = replace(FilterStringTemplate, "{FilterValue}", "'" & replace(FilterValue, "'", "''") & "*'")

More readable:

private sub FilterSubForm(byval FilterValue as Variant)

    Const FilterStringTemplate as String = "[Contact Name] LIKE {FilterValue} OR Company LIKE {FilterValue} OR Telephone LIKE {FilterValue} OR Mobile LIKE {FilterValue}"

    Dim FilterString as String
    Dim FilterValueExpression as string

    if len(FilterValue) > 0 then
         FilterValueExpression = "'" & replace(FilterValue, "'", "''") & "*'"
         FilterString = replace(FilterStringTemplate, "{FilterValue}", FilterValueExpression)
    end if

    With Me.PBSubform.Form
         .Filter = FilterString
         .FilterOn = len(FilterString)>0
    end With

End Sub

Josef.P
You are an absolute star and a credit to humanity

Thank you so much, take the rest of the day off

Very much appreciated.