Search Box Problem in Access (1 Viewer)

[Drunkenneo]

I am working on universal search as the code :

Dim sFind As String
        Dim sField As String
    Dim sFilter As String
    
    If bFindLastKeySpace Then
       Search.Value = Search.Value & " "
      Search.SelStart = Len(sFind) + 2
    Else
        Search.SelStart = Len(sFind) + 1
    End If

    Search.SetFocus
    sFind = Nz(Search.Text, "")
    If Len(sFind) > 0 Then
        Dim iField, iFieldsIncluded As Integer
        iFieldsIncluded = 0
        sFilter = ""
        For iField = 0 To iFields - 1
            Select Case rsMe.Fields(iField).Type
            Case dbBoolean    'Field types not to search in
                'Don't include it
            Case Else
                sField = rsMe.Fields(iField).Name
               If iFieldsIncluded > 0 Then sFilter = sFilter & " Or "
                    sFilter = sFilter & "[" & sField & "] Like '*" & sFind & "*'"
                    iFieldsIncluded = iFieldsIncluded + 1
               End Select
        Next iField
****            Me.Filter = sFilter
        Me.FilterOn = True
    Else
        Me.Filter = ""
        Me.FilterOn = False
    End If
        Search.SetFocus
'Search.SelLength = 0
    bFindLastKeySpace = False

When i am trying to search "Man hood" Its giving error on ** marked line , "Error 2176, The setting of property is too long", Please Help?

 

[pr2-eugin]

How many fields are in your table anyway?

 

[Drunkenneo]

55, fields

 

[pr2-eugin]

Phew !

Why have you not considered using something like "Dynamically search multiple fields" or VBA?

 

[Drunkenneo]

Nice One,

But there is a constraint I am using split form here and view as datasheet view, due to which filtering is needed.

 

[pr2-eugin]

You can set the Form's recordsource instead of the Listbox to the Query, and requery the Form instead of requerying the Listbox (I am talking about John's example ofcourse).