vba search/filter function help

[adh123]

I am new to access and have found extensive help on this forum from previous posts so thank you all for unknowingly assisting!

I cannot find the answer to this on the forum so far, please point me in the right direction if I have missed something...

I have a form (frmMasterPeople) which contains buttons (e.g. add new person) and a subform datasheet (frmPeopleList). When a user doubleclicks a line in the datasheet it takes them to that record in a new form window.

I want to create a search box in the main form which filters the datasheet (frmPeopleList). I have managed to create this which searches based on the contacts full name.

Code I have used is:

Private Sub cmdSearch_Click()

Forms!frmmasterpeople!txtSearch.SetFocus

    If Len(Trim(txtSearch.Text)) = 0 Then
        frmPeopleList.Form.FilterOn = False
        Exit Sub
    End If

Dim Text As String

Text = "[p_role] LIKE '*" & Trim$(txtSearch.Text) & "*'"

frmPeopleList.Form.Filter = Text
frmPeopleList.Form.FilterOn = True

However I want this to be able to show results based on all of the fields in the datasheet.

The datasheet is based on a table (tblPeople)
The fields in view in the datasheet are labelled:

p_full_name
p_role
p_tel

Therefore if I was to search for the term "camp" I might recieve people who have the surname Campbell as well as people with the role campaign manager.

Any ideas if this is possible? As the database already contains some sensitive data I would prefer not to upload it if possible.

 

[pbaldy]

Along the lines of

Text = "[p_role] LIKE '*" & Trim$(txtSearch.Text) & "*' OR [p_full_name] LIKE '*" & Trim$(txtSearch.Text) & "*'"

Two other thoughts. You don't need to set focus to the textbox, but don't use the .Text property of it. Since "Text" is a reserved word (in this case it's a property of the textbox), it shouldn't be used as a variable name. Sometimes you'll get away with it, other times not.

 

[adh123]

Doh! It was simpler than I was expecting! Thanks very much!

Will also change the 'text' to something else, this was based on code I had found from a web search so had not crossed my mind to update it.

 

[pbaldy]

Happy to help, and welcome to the site by the way!