FilterForm

[JeroenMioch]

Hi guys,

I have a form on wich i filter records using unbound textboxes.

For example : If a user puts a part of a surname in the fldSurname textbox and presses enter then all records matching the part of the surname will appear.
Not really rocket sience, i guess all databases have such a "recordfinder" and there are different approaches to accomplish this.

This part works perfectly for me, However i also want an unbound textbox that searches the whole table.

So my fields are :

ID
Personell number
FirstName
Surname
Department
Company
email
remarks

If i input for example "Joha" in my unbound textfield and enter i want it to return all records that match the criteria in ALL fields.
So in this case it would return the record "Johanson" of the Surname field and Johan in the firstName field.
Basicly, two records would show up.

I struggled with the code to make it work, but without any luck.

This is my code :

Private Sub Form_Open(Cancel As Integer)
    Me.Filter = "1=2"
    Me.FilterOn = True

Private Sub fldSurNameSearch_AfterUpdate()
    FilterForm
End Sub

Private Sub FilterForm()
    Dim strFilter As String
    Dim blnFilter As Boolean
    
    blnFilter = False
    
    strFilter = " 1=1 "


    If Me.fldSurNameSearch <> "" Then
        strFilter = strFilter + " and Surname like '*" & fldSurNameSearch & "*'"
        blnFilter = True
    End If

    If blnFilter Then
        Me.Filter = strFilter
    Else
        Me.Filter = "1=2"
    End If
   
    Me.FilterOn = True

End Sub

I put in only part of the code. Off course i have more code for different fields to search.

Anyone know if i can modify this code to search ALL fields ? I doesnt have to be in combination with other fields btw.

Thanks in advance for your time

[pbaldy]

Try


strFilter = strFilter + " and (Surname like '*" & fldSurNameSearch & "*' OR FirstName like '*" & fldSurNameSearch & "*')"

[JeroenMioch]

Thanks mate, ill definately try it.
Ill let you know if it works

Greetings,

Jeroen

[JeroenMioch]

Works perfectly mate !!

Many thanks

greetings,

Jeroen

[pbaldy]

Excellent! Happy to help.