Generic filter function on specific field (1 Viewer)

[Geert Adriaens]

Hi,

I use a simple filter function attached to specific fields in a CRM database with Companies and Persons (working for Companies).
So, for instance, I have a field "First Name" in Persons, and I have a filter icon next to that field to filter on a (part of) a First Name.
Same with the Last Name field in Persons. On the Companies form, I can filter on the field Company Name or on Street Name,
City Name, etc. Now, I was wondering if I could create a generic function that takes just 2 parameters (<Form> and <Field>), and
executes the same search code without me having to duplicate the code each time. My problem is more how to pass/use the parameters
correctly to that function.
Current code (First Name filter example) is simple:

Private Sub FindFirstNameBtn_Click()
Dim Str As String
Str = InputBox("Enter (part of) First Name", "Find First Name", FirstName)
If Str = "" Then Exit Sub
Me.Filter = "FirstName LIKE ""*" & Str & "*"""
Me.FilterOn = True
If Me.RecordSet.RecordCount = 0 Then
MsgBox "No records found", , "Find First Name: " & Str
DoCmd.Close
DoCmd.OpenForm "Persons"
End If
End Sub

When I want to search the Last Name, I duplicate this code, just replacing things where needed. Same for City, Street,...
So, I would like a function called FindFieldBtn, with the Form/Table name and the Field Name as parameters, and call it
like this for every filter I want: FindFieldBtn (Persons, First Name). Then the question is, what to put in quotes, how to
address the Field's name, the Field's value, etc. in the generic code...

Thanks,
Geert.

 

[Gasman]

Use a combo to get your field names.
A textbox for what you are seeking.

Then pass both of those to the sub in the form.
Try

Sub FindDate(strSearch AS String, strField AS String)
Dim strFilter AS String
strFilter = CHR(34) & strField & " LIKE  ""*" & StrSearch & "*""" & CHR(34)
Debug.Print strFilter

Me.Filter = strFilter
....
...

 

[arnelgp]

here is a simple filter form.

 

[Geert Adriaens]

Ok, thanks for the suggestions, and I see indeed a couple of things I did not know well enough in VBA to create my function. With your inputs, I can get to work!

 

[HillTJ]

@Geert Adriaens, also check out Alan Browns website. He has a very good demo about filtering records. I've adapted his code on multiple occasions.