multi value search in textbox of a form

[lupis]

I am pretty new to vba. This is what I am trying to achieve.
I have a form that onload displays all records in a form.
I also have a searchbox with a button that should be able to take multiple search criterias (from multiple colums in a table or query). And then a reset button to display records one more.

The search strings are the following fieldnames in a contacts table, they are all of dataytpe text.
LastName
FirstName
EmailAddress

The search ctiteria would be based on LIKE, and I guess OR between to seperate them.
Should I have strfilter variable where the different fieldvalues are concatenated in the same string?

Thanks
Sohail

 

[G37Sam]

I don't think I get you fully,

You have a textbox, user would enter "Joe" for instance, and a with click of that button, you want to filter the form to users who have Joe in their first name, last name or email.. right?

If so, just have the button edit your form's recordsource, then requery your form.

Hope that helps,
Samer

 

[lupis]

I am sorry if I wasnt clear enough. What I meant it, regardless if I submitt a string of firstname, lastname or email. The string I provide with a like clause could be in one of those fieldnames of a table, and resulting rs could display more then one single record corresponding to that search string.

thanks
Sohail

 

[Galaxiom]

You could apply a filter to the form using this code in the search form search button click procedure.

With Forms!formname
.Filter = "[LastName] Like '*' & Me![searchbox] & '*' OR etc etc"
.FilterOn = True
End With

Turn it off by setting FilterOn to False in the reset button procedure.

 

[lupis]

So if I have form named Contacts List and a textbox named SearchBox and the three fieldnames of the table being LastName, FirstName and EmailAddress, would the command click code for the button be in VBA be the following?

With Forms!Contacts List
.Filter = "[LastName] Like '*' & Me![searchbox] & '*' OR [FirstName ] Like '*' & Me![searchbox] & '*' OR [EmailAddress] Like '*' & Me![searchbox] & '*'"
.FilterOn = True
End With

Thanks
Sohail

 

[boblarson]

If you have spaces in the form name you will want either

Forms![Contacts List]

or

Forms("Contacts List")

 

[lupis]

So, this would work?

With Forms![Contacts List]
.Filter = "[LastName] Like '*' & Me![searchbox] & '*' OR [FirstName ] Like '*' & Me![searchbox] & '*' OR [EmailAddress] Like '*' & Me![searchbox] & '*'"
.FilterOn = True
End With

and for the reset button to dispaly all records again:

With Forms![Contacts List]
.FilterOn = False
End With

thanks
Sohail

 

[JANR]

As an alternative you could create a calculated field in your forms recordsource where you concat the search fields together and filter on this field.

SearchString: [LastName] & "|" & [FirstName] & "|" & [EmailAdress]

With Forms![Contacts List]
   .Filter = "[SearchString] Like '*' & Me![Searchbox] & '*'"
   .FilterOn = True
End With

JR

 

[lupis]

Hi JR,

I made a copy of the form and edited the query behind the form with the calculated field as you suggested.
I tried that out, but when I submit a search string in the textbox with the name of SearchBox in that form, Access wants me to supply a value in a parameter dialog for Me!Searchbox.
Here is the code in that function for the buttom click action:

Private Sub cmdGo_Click()
With Forms![Copy of Contact List]
   .Filter = "[SearchString] Like '*' & Me![SearchBox] & '*'"
   .FilterOn = True
End With
End Sub

If a click OK without supplying any value, nothing happens, any other values submitted in the textbox does nothing, I am guessing the previous string is still there, is there a function for clearing the textbox priot to submitting a string, something with "RemoveFilterSort".

If I do supply the parameter dialog with a valid FirstName as an example, a empty record is displayed.

At least the command for show all records is working

-Sohail

 

[JANR]

Private Sub cmdGo_Click()
With Forms![Copy of Contact List]
   .Filter = "[SearchString] Like '*' & [COLOR=red]'" & [/COLOR]Me![SearchBox] & [COLOR=red]"' & [/COLOR]'*'"
   .FilterOn = True
End With

Add the redparts and you should be good to go

JR

 

[lupis]

Thanks a lot, I really wouldnt have imagined the soloution you suggested with Calculated field name for the search filter.

Thanks again

Sohail

 

[JANR]

No worries

JR

 

[lupis]

By the way, can I empty the searchstring in the text searchBox when I click for "show all records". This is my current code for showing all records when I set the filter to false:

Private Sub cmdShowAll_Click()
With Forms![Copy of Contact List]
.FilterOn = False
End With
End Sub

First I want to clear the textbox and then also to clear the previous filter.

-Sohail

 

[JANR]

Sure

Private Sub cmdShowAll_Click()
With Forms![Copy of Contact List]
.FilterOn = False
End With
[COLOR=red]Me!SearchBox = ""[/COLOR]
End Sub

JR

 

[lupis]

Thanks a lot

 

[dkbfranklin]

Dear JANR - I am working on something similar as lupis. My Show All button works except that when I try to click back in the searchbox I can't until I have clicked in a record in the list. How can I avoid this extra step? I copied the code you provided but am still having the problem. Ugh!

 

[JANR]

I'm not sure what you mean. Could you upload a screenshot or a samplebase?

JR

 

[dkbfranklin]

Thanks for getting back to me. In my database, the search box works fine, but when I click my show all records button, the list repopulates, but I can't click in the searchbox to complete another search until I click in one of the records on the list. I want the cursor to be in the searchbox automatically after I click show all records.

I am new to the forum, how can I send you a sample database?

Here is the macro behind the Show All Records button. It is embedded in On Click.

RunCode-clearBox()
[Forms]![Contact List]![SearchBox] Is Null-RunCommand-RemoveFilterSort
SetProperty-cmdShowAll, Enabled, 0
StopAllMacros
RemoveTempVar-strFilter
RemoveTempVar-strSearch

 

[JANR]

Ahh using macro, try and use GotoControl to set focus to your searchbox as the last action of the macro

JR

 

[gemma-the-husky]

you may need to test this a bit. if you are letting people optionally type something or nothing into each of multiple search boxes, then you need to take into account the multiple possiblities.

eg if it's "or" on each text box - then leaving any blank will return all records

if it's "and", then leaving any blank will just ignore that filter.

depending which you want, you will need to set the query parameters slightly differently